4 Easy Steps to Creating Custom SmartLists from SQL Views in Microsoft Dynamics GP 2015

We often receive requests from customers asking us to help them create custom SmartLists. For many, this is a paid consulting engagement. However, for those administrators with Dynamics GP SQL table knowledge, this can be done very easily using a SELECT statement.

Here’s how to do it:

Step 1

In SQL Server Management Studio, create a select query against a Company database that results in the desired data. In this example, we have created an Item Price by SKU query for Sales against the Fabrikam (TWO) Company database.

Step 2

Create a SQL view from the select statement and grant SELECT permissions to the DYNGRP.

Step 3

Open SmartList & select New to open SmartList Designer. Name the SmartList & list it under the appropriate series. Scroll down to views to locate the newly created view and click OK.

Step 4

Grant access to this view by going to Tools >> Setup >> System >> Security Tasks. For this example, we selected the DefaultUser TaskID, selected SmartList from the Product drop down, SmartList Object from the Type drop down and SmartList Objects from the Series drop down. Mark the SmartList and hit Save.

The new SmartList should now be visible to anyone with the Defaultuser taskID.

For help or assistance with SmartList or Microsoft Dynamics GP, please contact FMT Consultants by filling out the form below.

Written by:
April Buck, Senior Dynamics GP Consultant
FMT Consultants