Create a SmartList to Analyze AP Invoices Paid by GL Accounts in Dynamics GP

A common question from our Microsoft Dynamics GP customers: in a GL account, how do I see how much I paid a certain vendor? An example is where do you go to see all the legal fees paid to several different law firms quickly and easily? Follow this list of steps so you too can build a report in SmartList using eOne SmartList Builder or SmartList Designer.

To start, create a SQL query

• First, realize that you need a SQL view. This is a virtual table that gathers data from several tables in your Dynamics GP database. You need to pull AP invoices to get the GL account distributions (header, line, and distribution tables), vendor information (vendor master), and payment information for amounts and dates (apply to information). You could pull all this data from these various tables or build your own SQL view.
• Review Dynamics GP MVP Victoria Yudin’s website for popular pre-built SQL views.  There is a list of SQL views on this page and I selected Payables.
• Select the GL Distributions for AP Transactions. You should copy this script into Notepad or Word or OneNote to save it to plug into a SQL query in a few minutes.
• To run a SQL query, you need to have appropriate access to the SQL Server Management Studio, so make sure you talk to someone in IT. They might prefer to do this for you.
• Click on the company database in which you want to create the view (TWO in this case)
• Click on New Query and paste the script into this area on the right.
  
• Click on Execute. You should get the message: Command completed successfully. If you get errors, you might have selected the wrong database.

Now, create your own SmartList

• Now you are ready to use SmartList Designer or eOne SmartList Builder to find the view and bring it into a SmartList. I’m going to use SmartList Designer because everyone has that tool. The process is similar in eOne SmartList Builder. Here are instructions from Victoria’s website on how to build a report using a view. She’s a great resource (and amazing to share all this information with you and me!)
• Open SmartList and click on New.
• Give the list a name, the product is Dynamics GP, and Purchasing is a good series to put this report.
  
• Look under Database View and scroll to the end under Views and look for the name of the view you just created: view_AP_Distributions.
  

• Click on it and all the fields in the view will populate the Selected Fields on the right. You can uncheck to remove some, but I left them all in. This is the default view and you might need them down the road.
• Execute the Query to view the returned data in the Preview window at the bottom.
• You don’t need to build any relationships because the SQL view did that for you. You can filter some information if you like. For instance, this view has posted and unposted data. If you want to see only posted transactions, you can filter by posted. You could also filter by year. But keep in mind that you can do this in SmartList too. I recommend leaving all the data there for the default.

Now you can customize the SmartList for you once it is built!

• You can create a Go To for drilling to another SmartList or Navigation List with payables invoices. To do this, click on Create Go To and reference the SmartList or Navigation List. If you want to drill directly to a payables invoice, you will need eOne SmartList Builder. Here is a list of the differences between eOne SmartList Builder and SmartList Designer. I recommend buying SmartList Builder and you will have a lot more functionality.
  

• Click Save and OK to get back to SmartList.
• You will find the new SmartList under Purchasing. Start to have fun by filtering the data to get that analysis of legal fees by searching by the legal fee account number or by account name. You can move the fields around if you like.
  

We wish you the best of luck with the report and if you have any other suggestions about Navigation Lists or SmartList, please let us know. Make sure to look at other resources provided by your Microsoft Dynamics MVPs and contact your partner to earn a special price on eOne SmartList Builder.

For more information please feel free to contact us today!