In Dynamics 365, we can filter lookup fields without writing code. In this post, we will look at an out of the box field example and a custom example.
The common out of the box example is filtering a contact based on an account. If we create a new Opportunity, we can see if we look at the Contacts field, we see all contacts in the system:
And the same with Accounts:
If we were to select an Account or a Contact, it won’t filter the corresponding field. E.g. if we selected 3M as the account, it would be useful if the system only showed us contacts for 3M, but this is not the case. Likewise, selecting a contact that belongs to A Datum Corporation does not filter the account to A Datum Corporation.
In order to achieve this, we can use the Related Records Filtering property on the Contact field. Open the form in design view, and select the Contact field, then click Change Properties:
You will see Related Records Filtering:
Click the checkbox, and select to only show records where Account (Opportunities) Contains Accounts (Primary Contact).
Note you may see multiple occurrences of Account (Opportunities), as there are multiple relationships defined between Accounts and Contacts. You will need to select the right one that contains Accounts (Primary Contact):
It should then look like below:
Now, create a new opportunity, and select an Account:
Now use the drop down to select a contact. You will see the contacts have been filtered to only show the primary contact for this account, as selected on the account record:
Note the related records filtering is based on the relationships. In the above example, we can see the following options:
- Accounts (Contacts)
- Accounts (Created by Portal Contact)
- Accounts (Primary Contact)
- Company Name (Accounts) (Contacts)
- Managing Partner (Managed Contacts)
These are based on the Account-Contact relationships.
If we look at the 1:N relationships for Accounts, we can see the following for Contacts:
If we look at the N:1 relationships for Accounts, we can see the following for Contacts:
Now for the custom example. Let’s say we have 2 custom entities, Country and City. We would like to filter the city based on the country selected.
Our Countries list looks like:
We will add a lookup field to the City entity called Country:
This will create a N:1 relationship:
Our cities now look like this, with the associated country filled in:
Now on the Account form, add the 2 new fields, save and publish. If we were to select a country and then a city, we would not yet get the filter:
The final step is to add the Related Record Filter on the City field on the Accounts form:
Now, when we select a country on the Account form, it will be filtered to only show cities relating to that country:
Other Usefull Links
Dynamics 365: Lookup Field Filtering /
FILTERING LOOKUP FIELDS IN DYNAMICS 365
