In our most recent webinar, we took a look at SmartList Tips And Tricks. In this webinar, we took a look at how you can get the most out of the Microsoft Dynamics GP SmartList reporting tool. If you want to catch up on this, or any other, webinar, you can do so here.
- Introduction
- SmartList UI
- Columns
- Searching
- Favorites
- Reminders
- Advanced Searching
- Exporting/Export Solutions
- Conclusion
Introduction ^
If you’ve spoken to me for even a short time about Microsoft Dynamics GP, I have probably mentioned SmartList and extolled it’s benefits. I think it’s a great reporting tool, easily extended with SmartList Builder, which even in it’s basic form is where users can usually easily find any information they require.
in this webinar, I touched on the basics of navigation and amending the included columns and search criteria before moving onto advanced searching techniques and export solutions, both of which I find many clients don’t know about.
SmartList UI ^
The SmartList UI is quite simple, but there are a few features that it seems many people don’t know are there. Earlier versions of SmartList had a predefined split between the navigation and detail panes, but for a few versions now you’ve been able to right click and choose to make the navigation pane wider or narrower:
You can also stop the (relatively) new behaviour of the navigation pane disappearing when you select a SmartList favourite by toggling it off from the SmartList button on the action pane and selecting Favourites Pane » Enable Auto-Hide (which if enabled will disable it):
Columns ^
SmartList favourites come with a set of columns already selected for output; by clicking the Columns button on the action pane, you can manipulate the columns in serval ways.
By marking the box next to columns, you can shuffle them up or down using the arrows on the right, or you can click Remove to remove those columns:
You can also click Add to add new columns to the output; on some SmartLists, clicking the bar where it says Available Columns you can change this to All Columns and have access to more columns for including on your report:
Searching ^
The basic searching in SmartLists is very good, allowing you to search for up to four criteria at a time with a choice of filters such as contains, is between or is greater than:
At the bottom of the window you can also increase the number of returned records from the default of 1,000.
Favorites ^
If you make any changes to a SmartList and then load a different one, all of the changes you made are discarded; Favourites are a way that you can save your amendments to columns and search criteria for future use.
To save a new favourite, click the Favorite button on the toolbar. You need to set a Name and choose the Visible To:
- System means anyone can see the favourite in any company.
- Company means anyone can see the favourite in the company in which it was created.
- User Class means only users in the same user class as the user saving the favourite can see it in any company.
- User ID means only the user saving the favorite can see it in any company.
One key point to remember is that any user who can see a favourite is able to amend or remove the favourite and the Remove button has no “are you sure?” prompt.
To add a new favorite click Add and then click Add favorite, cicking Modify will save changes to an existing favourite and Remove will delete, without asking for confirmation, the favourite.
Reminders ^
When adding a favourite you can also choose to add a reminder; these appear on the home page in the To Do section:
When creating the reminder you get to choose when it displays, such as when the number of records is greater than 0:
You can also choose to view the reminder as a visual cue which means instead of just showing as a line of text, it shows an image of a stack of papers; there are three images it uses; a small, a medium and a tall stack depending on how many records are returned.
One word of caution regarding reminders; when the home page is refreshed each of the SmartList reminders is run. If you have a lot of reminders, or reminders which are selecting a lot of data, then the home page refresh can be very slow. I’d recommend using reminders judiciously and not going overboard.
Advanced Searching ^
Earlier in the webinar I discussed the basic search facility of SmartList; there are some advanced searching techniques which don’t seem to be widely known, although Mark Polino and I covered them in the Microsoft Dynamics GP 2016 Cookbook and Shaun Dorward has a good post on the subject.
When doing a search there is a field available called Any Field which will search for the specified string in any field. So searching against vendors for ALLEN will find any vendor with Allen in their Vendor ID, Vendor Name, Address 1 and so on:
This makes searching very powerful and can help you find the required information quickly; I’ve used it before for part of an address where I wasn’t sure if the data I was looking for was in Address 1, 2 or 3.
There is also the capability to search for a “charlist” or character list; this allows you to, for example, search for vendors starting with specific letters. If you’re wanting to search for all vendors with a name between A and G this is easy using the between filter, but if you wanted to find only those vendors whose names started with A, B, C, F, G and H, you can;t do this with a between or other standard filter, but you can with a charlist.
The charlist search is quiet easy; simply enclose the required letters in square brackets:
If you wanted to find all vendors other than the ones starting with those letters, you just need to add a circumflex (^) before after the opening square bracket, but before the first letter.
The search type which I find the most useful is none of the above, but the wildcard search, of which there are two types:
- % which is a wildcard for any number of characters.
- _ which is a wildcard search for a single character.
If I was wanting to find all accounts with the first segment of 000 and a last segment of 00, I could do this using two search filters and searching for each segment. However, I could also do this by using a wildcard search for 000-%-00.
If I then wanted to change my search for the same first and third segments, but have a 2 as the second character of the second segment, I could search use either 000-_2%-00 or 000-_2__-00. The second example is being more explicit about the number of characters after the 2.
If I also wanted the fourth character to be a 1, then I would search for 000-_2_1-00.
I can combine wildcard searches with charlist searches. If I was wanting to find all accounts with a first segment of 100 or 200, a second segment where the second characters was a 2 or a 4, the fourth character of segment 2 was a 1, the first character of segment 3 was a 0 and the second character of segment three was not a 1 or 2, I would search for [12]00-_[24]_1-0[^12].
Exporting/Export Solutions ^
Exporting a SmartList to Excel is as easy as running the SmartList and clicking the excel button; this will export all of the returned data exactly as it is in the SmartList. This does mean that there is no formatting, such as bold headings. This is possible by way of an export solution.
Export solutions allow you to define Excel macros which should be applied to a SmartList as it is exported to Excel; it allows you to do things such as apply formatting to headings or columns and record the application of formula or other Excel features; basically if it can be recorded by a macro you could apply this during export using an export solution.
The first step is to export a SmartList to Excel and record a macro while you apply all of the formatting changes or other items you want. In the example below, I have made the column headings bold, made the columns fit width and added a column G with a formula of =E2-F2:
You then need to save the file as a macro enabled Excel workbook in a shared location (please make sure this location is one which is backed up regularly). In the webinar I said, that you could duplicate the formula down all rows as part of the macro, but it was pointed out to me afterwards by an attendee that this would only duplicate down for the number of rows in the template you created and would not be extended by the macro; instead, I would suggest recording the macro for the formula and duplicate it down the rows after exporting data in future. This is a bit more work, but still incudes the formula in the macro.
Once you have the macro-enabled workbook saved, you can create the export solution in SmartList. Do this by clicking the SmartList button the toolbar and selecting Export Solutions.
You need to give the export solution a name and select the Document which is the macro-enabled Exel workbook; enter the name of the macro you saved in the workbook in the Completion Macro field and then choose which of the SmartList favourites should have access to the export solution:
Once you have saved the export solution, the Excel export button on the selected favourites will have two (or more options if you have more than one export solution assigned to a favourite) options:
Conclusion ^
SmartList is a very flexible and powerful reporting tool. I always say to people that 90-95% of the time when someone asks me “where can I find…”, the answer is SmartList. The advanced searching techniques especially can make it far easier to find required information and the export solutions can save time in formatting a report after exporting.
Tools like SmartList Builder can make SmartLists even more useful as it allows you to build whole new SmartList objects pulling together information in ways not possible in standard SmartLists.
Our next webinar is on Dynamics GP – Year End Best Practice on the 14th December:
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.