How to use a SQL view in SmartList Builder

Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList.  I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3:

STEP 1  -  create SQL view

To start we need a  SQL view.  I have published a number of views for getting GP data, so I will use one of my favorites - Payables Apply Information.  You can see the list of other SQL views I have published on my GP Reports page.

To create the view in SQL: 

1. Open SQL Server Management Studio and connect to your GP SQL Server
2. Click on the New Query button at the top left
3. Copy the SQL code into the new window that opens (on my blog, I put the code to copy between squiggles: ~~~~~)
4. In the drop-down, select your GP company database:
5. Click Execute
6. You will get a message saying “Command(s) completed successfully.”
7. If you want to use your SmartList in multiple companies, repeat steps 4 and 5 for each company database

 STEP 2 - set up SmartList security for SQL view

Before we can use a SQL view in SmartList Builder we need to grant SmartList Builder security to that view:

1. Log into GP as a user that has all the proper security access
2. Open the SQL Table Security window by going to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security
3. Choose Views at the top
4. Click on your company database on the left
5. Find the view on the right and select it:
6. If you created the SQL view in multiple company databases, repeat steps 4 and 5 for each company database

STEP 3 - create SmartList

Now we are ready to create the SmartList:

1. Open SmartList Builder by going to Microsoft Dynamics GP > Tools > SmartList Builder > SmartList Builder
2. Type in a SmartList ID and Name (the Item Name will default to the SmartList Name, I recommend leaving these the same to avoid confusion)
3. Choose what Product and Series to put your SmartList in - this is a great new option in GP 10.0 and allows you to group SmartLists as you want.  This is also something that can be changed later, so some people like to create all new SmartLists is a separate place until they are satisfied with them, then move them to their ultimate location.  Up to you on this one.
4. Click on the + sign to the right of Tables and choose SQL Server Table:
5. Check the Use Company Database checkbox if you are creating a SmartList that should run against whatever company database the user is logged into (or if you are planning that for the future)
6. Click on the company database on the left
7. Select Views and click on your view on the right
8. Click on the + sign to the right of Key Fields and add Key Fields (these are used determine the current record selected when opening Go To’s) - at least one Key Field is required:
9. Click Save to return to the SmartList Builder window
10. Every column in the view will be brought in as a field for the SmartList, at this point you can decide what fields to show, change formatting, etc., for now I will simply add all the fields available to the SmartList:
11. Once you’re done configuring all the additional options for your SmartList, click Save
12. To finish creating the new SmartList, go to SmartList, and say Yes to the following message:

Next steps & additional resources

Below are some additional resources for creating SmartLists and dealing with SmartList security in GP:

• This blog - Granting access to a new SmartList in GP 10.0
• Mariano Gomez - Changing SQL Server views for SmartList Builder smartlists
• Mariano Gomez - How to create a Go To link to the Apply Sales Document window in SmartList Builder
• Mariano Gomez - Smartlist Builder Security for SQL Server Views
• David Musgrave - Creating SQL Views of Extender Data
• This blog - Reporting on Extender data in Dynamics GP

If you know of any other resources to add to this list, please let me know.