Personalized Community is here!
Quickly customize your community to find the content you seek.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
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.
There are some basics to search and some advanced stuff about search. To start with, here are some of the basics:
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.
There are multiple filter types that can be used. In order, they are:
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.
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:
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:
Field comparisons can be very useful in many cases, it’s a matter of being creative with what you want to find!
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!
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!
SmartList Search Options – Advanced Search: Using Wildcards and ANY FIELD
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!
Business Applications communities