Today's #TipTuesday article is yet another SmartList tip. For the past few weeks, I have been focused on small aspects of SmartList since I love this tool so much! Today I'll be talking about searching & filtering in SmartLists.
Search basics
There are some basics to search and some advanced stuff about search. To start with, here are some of the basics:
- Search can be on up to 4 different fields in Dynamics GP
- The search type can be an "AND" search (i.e. Match All) or an "OR" search (i.e. Match 1 or More)
- The search window is also where a user can override the maximum number of records (even if they aren't "searching" for anything in this particular list).
Default search settings
By default, the search is set to "AND" (Match All), meaning if a user has 4 search definitions, they all must be true to return results. The default search definition is looking up a value in a column. If the column chosen to search on is a drop-down list type of field, the Value will be a drop-down list type of choice, not free-form text. If the field is a date, the value box will be a date selector.
Filter Types
Multiple filter types can be used. In order, they are:
- Contains = fuzzy search, enter part of a possible value in this field and it will look for that in the contents. No wildcard symbol is required.
- Is Equal To = looking for an exact match.
- Begins With = looking for a match in the field beginning with whatever the user types in the Value field.
- Is Between = allows a user to use a range of values.
- Is Not Equal To = returns anything except what is in the Value field.
- Is Greater Than and Is Less Than = matching values over or under what the user enters in the Value field.
Case sensitivity note
The most common SQL collation used with Dynamics GP is case insensitive, so searching should work regardless of whether the user is using upper or lowercase values. However, if the SQL collation is not case insensitive, the users may find that they need to be specific with the exact case of the value and filters like "less than" and "greater than" may return unexpected results (i.e., uppercase before lowercase etc. in some cases).
In the search window, when searching on values in Text/String fields, there is an option to enable "Match Case" so that if case sensitivity matters in the results, it would look for the exact criteria.
Choosing fields to search on
If a user happened to read my previous tip on columns in SmartList, they may remember that there is a way to see more than just the "available" columns. Search works similarly, except by default the Column Name lookup is filtered to "Selected Columns" which means the columns on the SmartList itself. Users are not restricted to just filtering on that, and here's how to search on fields that are not on the visible list of columns.
First, click on Search if not already in that window. Next, click on the magnifying glass ("lookup") for the Column Name (in Search Definition 1 for instance).
The first thing the user should see is "Selected Columns". Click on that bar/drop-down list to choose All Columns and then any column that is available in the SmartList object is available to search on.
For example: let's pretend I want to get a list of GL Accounts that were modified in the past month. I may not need to see when it was modified, but I want to be able to filter on that. I would want to go to All Columns to find Modified Date and search based on that. That's "part 1": the column I choose can be a column that isn't in my actual results.
Part 2 of this is if I want anything to be based on date, I recommend trying to use a generic date if possible, something that a user does not have to override every time they use this list. In the above example, I don't want to have to put in what month I'm in, so I am choosing to use an "Is Between" filter with "Beginning of Month" and "End of Month" as the options. I recommend the same thing on Report Options in other areas of Dynamics GP. This way it will show all newly added accounts *this month* no matter when the user opens this particular SmartList. Here is a picture of my search with this setting:
Field Comparison searches
Another type of search one can do is a Field Comparison type of search. One example might be (continuing with GL Accounts example): I only want to see accounts that have been changed, not newly created. By default, Create Date and Modify Date are the same on a new account. Maybe I only want to see accounts where someone changed some settings later. Here's how I could do that:
- Column Name = one of my two date fields I want to compare
- Filter = is not equal to
- The field Comparison checkbox is enabled
- Value = the other date field
Match All vs Match 1 or More
The Search Type field in the bottom right-hand corner is often left unused, but there are always instances where it could come in handy. The unfortunate part is the entire window is one setting or the other. Think of this setting as "AND" vs. "OR" searches. It might even help to talk it through out loud, reading one search and the word AND or OR between them to help make it make sense and check that the logic *should* return results! Sometimes simply speaking out loud or to a colleague will identify a misstep!
Advanced Searching
This is an area I am not going to go in-depth with because fellow Microsoft MVP Shawn Dorward created such an awesome post about it already! If users find the limitation of only being able to search on 4 fields at a time difficult, read through this to see if they can use wildcards in any of their searches to get more out of them. I know there are times when my "OR" searches are searching for different things in the same field and it could be replaced by using some of Shawn's wildcard options!
https://lifehacks365.com/2018/05/16/smartlist-search-options-advanced-search-using-wildcards
Once again, that is it for this post. I've covered most of the regular functionality there is to cover now on SmartLists and am down to only a couple of small topics left with this little series!
