Today’s #TipTuesday article is yet another SmartList tip. 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 you can override the maximum number of records (even if you aren’t “searching” for anything in this particular list).

Default search settings

By default, the search is set to “AND” (Match All), meaning if you have 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 you’re choosing 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

There are multiple filter types that 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 required.
• Is Equal To = looking for an exact match.
• Begins With = looking for a match in the field beginning with whatever you type in the Value field.
• Is Between = allows you 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 you enter 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 you are using upper or lowercase values for most customers. However, if your SQL collation is not case insensitive (your GP Admins would know), you may find you need to be specific with case 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 your results, it would look for your exact criteria.

Choosing fields to search on

If you happened to read my previous tip on columns in SmartList, you 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 your SmartList itself. You are not restricted to just filtering on that, and here’s how to search on fields not on your list of columns.

First, click on Search if you are 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 you see is “Selected Columns”. Click on that bar/drop down list to choose All Columns and then any column that is available in your SmartList object is available to search on.

For example: let’s pretend I want to get a list of G/L 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 can be one 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 you do not have to override every time you 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 you newly added accounts *this month* no matter when you open this particular SmartList. Here is a picture of my search with this setting:

Field Comparison searches

Another type of search you can do is a Field Comparison type of search. One example might be (continuing with G/L 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 setting 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
• Value = the other date field
• (Field Comparison must be enabled on this definition)

Field comparisons can be very useful in many cases, it’s a matter of being creative with what you want to find!

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 if you find it confusing. 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 your logic *should* return results! Sometimes simply talking it through 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 you find the limitation of only being able to search on 4 fields at a time difficult, read through this to see if you can use wildcards in any of your searches to get more out of them. I know there are times where my “OR” searches are searching for different things in the same field and it could possibly be replaced by using some of his 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!