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:
I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.
IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.
THANK YOU, AND LET'S KEEP LEARNING TOGETHER.
CARL
I have numerous filters set up based on one field, but now I am trying to filter based on two fields. Is this possible?
@Kristi It is possible throught JavaScript. You need to use .addPreSearch() function on PreSearch event of desired filtered field(control). Function that is handling this event needs to have .addCustomFilter(fetchXml) on the exact same field(control). Combine it with .removePreSearch() (if you have multiple cases) or use some if’s in handling function and ‘voila’.
Thank you @Marcin! @Kristi hope it resolved the issue.
Hello Carl, do you have any idea on how to change the default related records filtering (Client vs Contacts) in cases?
The above steps mentioned doesnt works at all. i tried filtering the contacts as related as mentioned on opportunities form but nothing is happening
@Saurav , I tried from my end its working fine, i guess you might be not fallowing every thing as it is.
Thank you for sharing such a wonderful stuff with us @ MS D365 Master Carl desouza.
When you add the two fields to the Account form, you’re actually creating two new lookup fields. Also, when you add them to the form, the lookup fields must be in the Body of the form, not in the Header or Footer, otherwise the related records will not filter properly and you will see ALL records. This took me the better part of a day to figure out, so I’m hoping this little bit of info will safe someone else a lot of time and headaches.
hi i cant see (Additional Properties) in customer type field.
how can i have View Selector for customer type field in my form?
Hello Carl,
I left a reply on your LinkedIn, but am curious, when populating the lists for each field entity, are you only loading single column data? Like, just counties for County, and just states for States? How does the system know what to match?
Introduction: Skin tags are a common occurrence, characterized by small,
benign growths on the skin’s surface. While they are harmless,
they can be aesthetically displeasing and cause discomfort.