Dynamics GP offers a very good tool to create your own Smartlists… Smartlist Designer.
One of the cool things users can do is incorporate custom SQL views into Smartlist through SmartList designer… this can be a HUGE time saver and really gives you the ability to get data you would normally not be able to get in a normal Smartlists.
Below are the steps needed to add a SQL View to your Smartlists.

Step 1:  Create SQL view against company Database

First things first… you will need a SQL View – you can create your own, or check some online resources – I have some listed under the SQL View option on this page, but you can also check out some awesome ones that I frequent from Victoria Yudin and Ian Grieve.  There is also a ‘living document’ from Microsoft, found here.

Step 2:  Assign View to DYNGRP

Most views you capture online will have this statement included as part of the view:

GO
GRANT SELECT ON CHANGE_TO_YOUR_VIEW_NAME TO DYNGRP

But, just to be sure, I wanted to put it here as a troubleshooting step for some issues.  It won’t hurt to run the statement again so when in doubt, run it.  Just replace your view name with the generic syntax “CHANGE_TO_YOUR_VIEW_NAME” and run against the database where the view was originally created.

Step 3:  Create a New Smartlist

Now that we have our SQL View ready, it’s time to bring it into a Smartlist.
Open Smartlists and click “New”

Step 4: Define the New Smartlist

Smartlist Designer will open up after clicking new.  If you don’t see the image below, you may have the E-one product Smartlist Builder.  With the Eone product, the steps are slightly different and I will cover those in another blog post.
If you get a security error, you need to check with your administrator for assistance gaining access to this functionality.
In this step, we are going to give the Smartlist a name and also select the appropriate series (this will be the Smartlist ‘folder’) you want the Smartlist to show under.
In my example, I am creating an AP Aging using Victoria’s current AP aging detail view…. so, let’s put that under the Purchasing series.

Step 5:  Find and select the SQL View

This step isn’t hard but it may take a few minutes to populate your SQL Views… so if it does take some time, just be patient 🙂
In the Database View pane on the left, scroll down until you see “Views”.
Expand “Views” and choose “Company” (if you placed the SQL View on your System database, then choose System).
The SQL Views listed will be for the company that you are currently logged into.
Select the checkbox next the view you want to use and Dynamics GP will automatically add all the fields to the “Selected Fields” window.  You can remove these as needed.

Step 6:  Save the New Smartlist created

Click OK to save the Smartlist and make it available in the Smartlist window.

Step 7:  Add Smartlist to user security

New Smartlists do not get make available to users automatically.   Be sure to add the new Smartlist to the appropriate tasks needed to grant users access to the new Smartlist.

That’s it!
If you need more information regarding security/roles/tasks, please contact me and I will be happy to help!
Thanks for reading!
Shawn

Shawn Dorward
Microsoft MVP, Business Solutions
LifeHacks365.com | LinkedIn | Twitter | GPUG | MSDYNGP
 
Original Post: https://lifehacks365.com/2018/03/06/gp-lifehacks-137-add-a-sql-view-to-smartlist-using-designer/