A common question which comes up is how to find specific data in Dynamics 365. Several tools are available for this, such as Charts, Dashboard, Views, and the SSRS Report Wizard, but my go to tool for this is the Advanced Find feature.
Advanced Find is a powerful ad hoc query tool, and once you understand the basics, there are many features which you may want to explore. This article will discuss the steps to follow for Advanced Find queries, applying filtering logic, and then will discuss often overlooked features.
Ad Hoc Inquiries
While we won’t be exploring all the functionality available in Advanced Find, this information should put you in a position where you can develop a query, establish filters, adjust the columns of data, and then review the data.
To access Advanced Find in Dynamics 365, click on the funnel looking icon in the upper right-hand corner:
This will open Advanced Find in a new window. A nice feature with the new window functionality, versus opening in-place, is you can have multiple Advanced Find queries open at any given time.
Steps for Advanced Find Queries
Step 1: Look For
The first thing to do in setting up your query is to set the Look For to what it is you want to report on. This is an important decision, as you will be able to add columns of data from this entity, as well as entities this record is a child to in a one-to-many (1:N) relationship. You will generally want to select the most granular detail for this. An example would be Account and Cases. If I want to see data from Cases but pull some specific information about the Account associated with a Case, I would set the Look For to Case, not Account.
Step 2: View
Once the Look For is set, you will need to determine the View to be used for the base filtering. When selecting this, you will have options of: [new] (which has not previously defined filtering logic), any existing System Views, as well as any Personal Views previously created (more on this later). Make an appropriate selection.
Step 3: Filtering Logic
With the Used Saved View selected, the filtering logic will be the next thing to define. If you click the Select button, you will see you can select any field from the entity selected for the Look For (above) as well as related records. Depending on what is selected for the initial field, your next step will be defining the filter clause, such as (but certainly not limited to): Equals, Contains, Begins With, etc.…
Note: These options will change depending upon the initial field selected.
Below is a basic example where I want to see Cases (Look For = Cases), without an initial filter applied (Use Saved View = [new]), where the Created On is On or After the beginning of the year:
Additional filters can be applied by clicking the Select button and continuing the build-out. An item to note on this is if you add additional filters, Advanced Find will treat them as “AND” scenarios, such as this criteria must be met AND this criteria must be met. So, in the example below, the query would return all Cases where the Created On is On or After the beginning of the year AND the Owner equals Current User:
If your query needs to leverage “OR” functionality, Advanced Find can do this as well. Using the query from above, say we want to return a result set where the Created On is On or After the beginning of the year OR the Owner equals Current User. To do this, click on the small down arrow adjacent to each filter, and select the Select Row option.
With each row selected, click on the Group OR button. This will update the filter with the OR scenario. Also note, there is an option for Group AND, so if you need to have nested AND/OR functions, you can do this:
Step 4: Edit Columns
To review, and/or edit the columns in the Advanced Find query, click on the Edit Columns button:
In the Edit Columns window, you can click on the Add Columns to add additional columns to the Advanced Find query. Keep in mind, you can add fields directly from the Look For entity initially selected (in this scenario, Cases), as well as fields from any parent entity (such as Account, Contact, User, etc…).
Step 5: Results
Once the columns have been selected, in the Advanced Find window, you can click on the red exclamation point (Results) to run the query. You may find it necessary to revise the columns and filtering logic before finalizing:
Step 6: Save As
Once you have your Advanced Find query complete, click on the Save As button to assign a name to your Personal View. This Personal View will be available to select from your View selector on the Look For entity (Cases), as well as in Advanced Find in the Use Saved View. This saving feature is important, don’t overlook this! You’ve spent the time to develop your query, and this will keep you from having to recreate this each time you need this data!
Those are the basic steps to complete an Advanced Find query. Next, I will discuss often overlooked features in Advanced Find that you can explore.
Overlooked Features in Advanced Find
Advanced Find is a powerful ad hoc query tool, but there are many features which users may be overlooking when using the tool. While what I have included below is not an exhaustive list of all features available, it will provide enough information to explore some of the features offered.
New Record in Advanced Find
Clicking on the New button will open a blank form for the entity you are running the Advanced Find for, in this scenario Account. This will allow you to create a new record directly from Advanced Find.
This feature will allow you to delete one, or many, records. To use this feature, select the record(s) you would like to delete. With the record(s) selected, the Delete button will become available. After the Delete button is selected, the system will prompt you with a final warning regarding the deletion. Proceed as needed.
There may be times when you need to update multiple records. While the system provides an option around importing a file for this update, sometimes it is just easier to do this manually. To use this feature, select the record(s) you would like to edit. With the record(s) selected, the Edit button will become available. After the Edit button is selected, the system will open a window for the record where the edits can be made.
Depending on how your Duplicate Detection rules are established, there are inevitably times when duplicate records are created. Instead of deleting a specific record, Dynamics 365 allows users to merge these records. When merging records, make sure you are aware of which record is your master record, and which record will become deactivated.
If/when a business process requires you to share records with other users or a Team, this can be done from within Advanced Find. This is extremely helpful when you need to share multiple records at once. To use this feature, select the record(s) you would like to share. With the record(s) selected, the Share button will become available. After the Share button is selected, the system will open the standard window for record sharing.
In the event records need to be reassigned, this function can be performed from within Advanced Find. To use this feature, select the record(s) you would like to re-assign. With the record(s) selected, the Assign button will become available. After the Assign button is selected, the system will open the standard window for record assignment.
Excel Export in Advanced Find
Other than the immediate ad hoc inquiry purposes of Advanced Find, the Excel Export is my favorite feature in Advanced Find. This feature allows you to take the dataset returned by an Advanced Find query and export it to Excel. Once the data is in Excel, you can use the tools available in Excel for further analysis.
The most common scenario I use Excel Export is for calculations not captured in Dynamics 365, as well as any ad hoc charts which I may not want to capture in a user or system level Chart or Dashboard. If you plan on using this functionality, consider using the Dynamic options, as you can refresh the Excel file with updated data from Dynamics 365.
While this article did not discuss all capabilities of Advanced Find, it outlined the basic steps to complete a query and some basic features available that you can explore.
Dynamics 365 CE (CRM) How-To eGuide
41 pages of step-by-step instructions for 6 different key tasks in Dynamics 365 Customer Engagement (CRM). Includes interactions with PowerApps and Flow!Get eGuide