Bring Out the “Advanced” in Advanced Find
Using Advanced Find in Microsoft Dynamics 365 for simple searches is like using satellite imagery to find your car. With that kind of power, there is so much more you can accomplish. Imagine being able to map out the landscape, create a navigation system, and get real-time traffic updates. This is the power of Advanced Find that lays at your fingertips. The only obstacle is knowing how to use it.
With that said, let’s put the “Advanced” in Advanced Find and show you what it’s made of, the common query operators, and some tips on advanced querying and sorting. Move on from just finding Waldo. Start searching for Wenda, Wizard Whitebeard, and the rest of the gang and see what you can accomplish together.
Anatomy of Advanced Find
Before we turn you into the detective you’ve always wanted to be, we first have to go through the key aspects of an Advanced Find. There are four items to keep in mind:
- Determining the Entity to Work From
This is the repeating section of data. If you’re looking across multiple entities, you will need to determine which entity is most important as that will be the starting point for your advanced find.
- Understanding Relationships
You will need to recognize how entities are related to one another, whether it’s a one-to-one, one-to-many, or many-to-many relationship.
- Selecting Columns
You should think about which columns you will want to pull in, whether they are from the base entity you’re working with or if you want to pull columns in from related entities as well.
Be aware that there are limitations in what you can find. Some information may need other tools to help you find what you need.
Query Criteria and Operators
While there are plenty of options when it comes to query criteria and operators, they all come down to the following: AND, OR, and DATES. Using these three variants can typically get you the results you need, however, there are also numeric variants you can always fall back on.
Advanced Querying and Sorting
Working With Null / Empty Values
If you’re looking for records either with or without blank fields, there are two ways of filtering through the data. Most commonly, people tend use Equal or Does Not Equal upon selecting a field and simply fill in the value they are seeking.
A more effective method though would be to use the Contains Data or Does Not Contain Data filters, as they apply directly to your search criteria. One thing to keep in mind is that while this can be fairly straight-forward, using the Equal or Does Not Equal filter in a binary situation, such as a Yes / No field, can cause you to miss information. If the data was in your system before the field was applied, the field may show a null or blank value rather than a Yes or No.
Querying on Dates Using Fiscal Year / Quarter
Starting with the Created On field, you can select one of the Fiscal Year, Fiscal Period, or Fiscal Period and Year filters. It’s important to note that the fiscal years and periods that show afterwards are the same ones that are created or set up in your Microsoft Dynamics 365 environment. So whether you use a calendar year or start partway through the year, the results will match with the data in your solution.
While on the topic of querying based on date ranges, here is a list of other intriguing options and use-cases we encourage you to explore:
- Last 7 Days – Good for viewing recent support cases.
- Last X Hours – Can give you a quick snapshot of the progress of your current day.
- Older Than X Days – Helps in showing older support cases that may need to be addressed.
Using Multiple Conditions Together
There are countless ways you can go about building a complex search depending on what your goal is. Regardless of what you’re looking though, you’re likely need to use the AND and OR groupings. We created a sample below to give you an idea of the various ways you can go about combining the two. As you can see, you can build several levels of filters.
In order group two or more items together, select each row using the chevron on the left, and click either Group AND or Group OR near the top of the screen. You can always ungroup them or change the groupings afterwards.
Sorting & Filtering in the Results Window
While you can only sort the columns in the results window within Advanced Find, there is a way to filter your findings even further. If you create a new view with the Advanced Find search that you built, click on the funnel icon near the top right of your screen. That will activate the filter capabilities within the view.
You’re not done yet. With the filter capability activated, click on the chevron within the column you wish to filter and open the Custom Filters window. Now you’re ready to filter to your heart’s content!
Saving and Sharing Views
You have now built, used, and saved your views. But as time goes on, what do we do with these views? Here are several ways of how to make use of them:
Personal Vs System Views
If you find a system view that gets you part of the way there, a good practice is to create a copy. By clicking to Save As, you can now create a new view that you can manipulate without risking losing what you already had.
Sharing views can be a great way to help a colleague save some time. In order to share a view, select the view you wish to share and click on the Saved Views tab at the top. Click on the Share icon, where you can now share the view to any number of users or teams. You can also determine their permissions. including read, write, delete, append, assign, and share.
Deleting Personal Views
In the same Saved Views tab, you can delete or deactivate views. We recommend always deactivating views rather than deleting them as you never know when an old view may be useful. If you delete a view, it’s gone permanently. If you deactivate it, it will simply disappear from your active views list, only showing you those that you wish to see.
Downloading FetchXML for Use in Reports
Perhaps you have an expansive query, but it only gets you so far. In that case, you may want to bring it into SQL Server Reporting Services (SSRS), which is a custom tool you can use to build more complex reporting. In order to use a view as the basis of your reporting, you can click on the Download FetchXML button in the Saved Views tab.
Feeling like you’re “Advanced” in Advanced Find? Well, one less thing for you to look for are some key takeaways from what we have covered.
- Advanced Find is a very powerful and serviceable tool to use. Some training required.
- Views can be leveraged for Dashboards and Charts
- Views can also be used as the basis for reports
- Sometimes Advanced Find isn’t enough, in which case SSRS or PowerBI can be leveraged when you need to go above and beyond the built-in tools.
If you’d like to review this in a step-by-step fashion, you can watch Daryl Orwin‘s webinar on the topic here. You can also get in touch if you want some more guidance. It’s why we’re here!