Ad Hoc Inquiries and Advanced Find in Dynamics 365

A common question which comes up is how to find specific data in Dynamics 365. While several tools are available for this, such as: Charts, Dashboard, Views, and the SSRS Report Wizard, my go to tool for this is the Advanced Find feature.

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:

Image.jpg

This will open Advanced Find in a new window. Another nice feature with the new window functionality, versus opening in-place, is you can have multiple Advanced Find queries open at any given time.

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 column 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.

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.

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 dependent 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:

Image.jpg

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:

Image.jpg

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:

Image.jpg

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:

Image.jpg

To review, and/or edit the columns in the Advanced Find query, click on the Edit Columns button:

Image.jpg

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 field 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…):

Image.jpg

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 in necessary to revise the columns and filtering logic before finalizing:

Image.jpg

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!

Image.jpg