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.

SQL Server Management Studio

 

Step 2

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

SQL SELECT Selection

 

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.

SmartList Designer

 

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.

Security Task Setup Dynamics GP

 

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

 

Custom SmartLists in Dynamics GP

 

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