This entry is part of our Feature Review Series. These short, to the point blogs strive to provide a quick snapshot of information to a user looking for a quick overview of a feature, how it’s managed or configured, some insight into how a business / organization would use it, and provide links to resources or tutorials for a deeper dive.
We’ve been working through an overview of the Advanced Find feature. In this third installment in our mini-series on how to use Advanced Find, we’ll dig into interacting with related tables when building queries.
The Power of Advanced Find
There are many use cases for the Advanced Find feature.
At the very least, this tool gives creative control to users who wish to configure views the way they want to see the data. Everyone has their own working style, so allowing users this ability empowers them to make the system “their own” which, hopefully, leads to more engaged users.
While Advanced Find is great for data from a single table, it can also be used to filter data in related tables. Depending on the structure of the view, users can even pull in related data, though there are some caveats to this we’ll outline below.
Related Data
There are two ways to think about related data:
- Using data from a related table to filter the data.
- Pulling data into the view as columns from related table.
The foundation of this concept lies within the types of relationships within Dynamics 365, so it’s important to understand.
Table Relationships
We’re not going to deep-dive into relationships here, but we will provide a link to the official documentation. For the purpose of this article, it’s important to understand there are three types of relationships:
- 1:Many (1:N) – Example: One Account can have multiple Opportunities.
- Many:1 (N:1) – Example: Cases will always align with a single Contact.
- Many:Many (N:N) – Each record can have multiple related records.
Note that the first two are reciprocal. If you create a 1:N relationship between two tables, the related table will automatically get a N:1 relationship created.
For more information on table relationships, see this Microsoft Docs article.
We’re going to focus on 1:N and N:1 relationships here. To illustrate this, we’ll use two examples.
First, Accounts and Opportunities.
Each Account can have multiple opportunities. We can pull up a list of Accounts in Advanced Find and filter which accounts show up by stipulating conditions on the related Opportunities they have. We can include any information about the Account itself, as well as a selection of other records tied to the Account table.
However, because there are potentially multiple Opportunity records connected to the Account, we cannot pull Opportunity information into the view. Each Account will only appear one time on the list, and there is no way to include data from the one or more (read: multiple) opportunities that could be connected.
Let’s look at this in action.
In Advanced Find, set Look For: to “Accounts.”
When we click Select and begin building our query logic, we see all of the available fields from the Account record, as shown below.
Note the scroll bar on the right of the window. If you scroll down far enough, you’ll find a section header titled Related. This section will surface all of the related tables tied to the Accounts table.
Keep scrolling until you find the one titled Opportunities (Account). The item in parenthesis identifies the field on the record that links the two. In this case, it’s the Account record.
When selected, we’ll now have an additional line within our query logic for the related entity, and when we look at that list of fields we’ll find it’s all fields related to the table itself. In this case, Opportunities.
This means we’re now building query logic against the Opportunities related table. Regardless of whether the Account has one or multiple, logic found to be true on any of the related records means the Account will show up in the list.
In our example, let’s add two lines of logic.
First, that the Estimated Revenue of the Opportunity is greater than $4,000,000, and another that stipulates the Opportunity is in Open status. That would look like this:
The resulting records will filter down.
In our trial system there are only a handful of Accounts with opportunities over $4,000,000, so our results should be pretty small.
In the previous article, we talked about how you could filter based on City. We could quickly add that logic in and all of the sudden we’d see all of the Accounts that have an open $4,000,000 opportunity (or, perhaps, multiple!), in the city we want.
That’s really powerful information!!
Related Data in Columns
The next logical thought someone might have here is that we want to include data about the Opportunity itself. For example, we might want to see what the Estimated Close date is. A great request!
However, in this format, that’s not possible per the earlier comments about not being able to see data from the related records because there could be multiple.
To do so, we’d have to set the Look For: to Opportunities first. That will give us a list of all of the Opportunities that meet this query logic, and then we’re able to include data from the Opportunity record AND the Account record since this is a 1:N.
(Accounts:Opportunities – there is only one Account tied to each Opportunity, so we can pull data from that single related record as the system can see that record clearly.)
Let’s use an example to illustrate.
We’ll use the same logic as before, but remember that we’re now using the Opportunity record as our starting point, so, when we look at the fields in the query area, the Opportunity fields will be presented. We’ll put the two query statements like we had them before.
Scroll back up to the previous image to compare the differences between the two examples.
When we click Results, we’re going to see which records show up.
A couple of things should catch your attention here.
First, there are five records. When we ran this query from the Account level, there were only four.
Why is that?
It’s because here we’re looking at the Opportunities of which there could be more than one that meets the query logic that is also attached to an Account. In this case you can clearly see the bottom two are linked to the “Adventure Works” Account.
Earlier, we were looking at which Accounts had Opportunities with an Estimated Revenue greater than $4,000,000 in Open status. It’s important to understand the difference here.
We’re looking at two different tables of information, hence the different results.
The second thing you might notice is that the resulting columns are different. Again, that’s because we’re building this through the Opportunity record, so the default fields presented belong to that table.
However, as noted, we can add information from the Account into this view. Below you can see we’ve added in the Address 1: City of the associated Account.
How did we do this?
In the “How to Build Your Query” article we explained the process of editing columns. This follows the same logic with one small twist.
When we get to the area where we add the columns, we can change the Record Type dropdown to choose any related record to the table we’re working in. In this case we’re working with Opportunities.
As you can see below, Account is one of the options.
When we do this, the options for the columns we can select change to reflect that table!
Stay Curious, Keep Learning
Like we’ve said before, for many people the best way to learn is to get hands on with the tool. We recommend you giving that a shot and perhaps even use this example to guide you through.
As previously mentioned, note that the data we’re using in our example is based in a trial environment, so you might have to tweak the values a bit to match data within your own environment.
In our next article, we’re going to talk about saving these views so you can find them within the tables themselves. We’ll also touch on the options for sharing these views with your colleagues so you can be the office hero who created the great views everyone loves!
