Microsoft Dynamics GP SmartList Tips
I presented a webinar for our customers last month on SmartList tips and realized it would make a great blog!
If you’re new to Microsoft Dynamics GP or just unfamiliar with SmartList, it is an ad hoc query tool built into Dynamics GP. At its base level, it lets you pull quick lists of master records, period summaries, transactions, etc.
There are standard “queries” in SmartList that give you a starting point and then options that let you fine tune your queries. I’m not going to go into the basic usage of SmartList since there are already many posts and videos to cover that. I’m just going to list out some of the features that I think are very helpful and often missed.
Field Comparisons: This option lets you compare data from two fields just as its name implies. In the example below, I’m comparing Actual Ship Date to Requested Ship Date to see which orders didn’t get shipped when expected. The best thing about this feature is that it is sensitive to they type of field you pick in the Column Name. If you pick a date field, only other date fields will be shown in the Value field. Same for currency – select an amount field as the Column and only amount fields will be available as the matching Value.
Order By: Simple sorting in SmartList is accomplished by clicking the column header in the data view. But if you need to sort by multiple fields, use the Order By option in the Search window to sort by as many fields as you like. If you do click on a column heading in the data view, you’ll lose your multiple sorts – but if you know you’re going to export to Excel then sort by two or three fields, setting this up in your Search will save a few steps.
Search Type: By default, this field is set to Match All, which makes all 4 of the search criteria you select be “and” criteria. You’re telling SmartList to search for Search Definition 1 “and” Search Definition 2 “and”… However, if you change this option to Match 1 or More, you’ve just changed your search to an “or” search. Now you’re telling SmartList to search for Search Definition 1 “or” Search Definition 2 “or”…
I use this often when checking to see if a vendor is already setup in GP. This allows me to search the Vendor ID, Vendor Name, Phone Number or any other piece of information that might help me confirm whether the vendor exists or not.
Display Name: Again, this option helps with the mundane tasks that you often do after exporting to Excel. In the example below, the people who consume the data in my Excel export may have no idea what U Of M is, so every time I export I have to change that column heading. But if I change the Display Name and save it in the favorite, it will always export with the column header named the way I want it.
In the interest of keeping this blog fairly short, I’ll skip over to Export Solutions (found under the SmartList button/icon on the ribbon).
In a nutshell, Export Solutions allow you to export data to a pre-saved template and trigger a macro upon completion of the export. The benefit again is massive time savings. Let’s say I need a pivot table and/or pivot chart of my top 10 customers. I can create an Excel template with a macro saved in it and all the repetitive work done for me by having SmartList trigger the macro to slice and dice my data.
I’m not going to walk you through all the steps to create an Export Solution in this blog, but if you search for Microsoft Dynamics GP Export Solution you’ll find several videos walking you through the setup. I also have the steps documented for you and you can download it here.
Hope this gives you some new ideas on how to get more out of SmartList.