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