How to use "NOT IN" filter in Dynamics 365 for Customer Engagement V9

By Abi Shende, Senior Functional Consultant, Arbela Technologies

Business users often have a need to filter records that do not have data in a related entity. Here are a few common examples:

• Accounts without Contacts
• Accounts without Opportunities
• Accounts without Cases
• Contacts without Phone Calls

If you were using SQL to create these queries, you will have to use the “NOT IN” filter condition in the SQL statement. Prior to v9, this feature was not available out of the box in Advanced Find and was one of the most requested features for Dynamics CRM. Prior to v9, you had to use custom FetchXML to create the above searches. However, v9 has added the “Does Not Contain Data” filter condition at the Entity level that supports “NOT IN” queries within Advanced Find.

Here are the screenshots of the filter criteria for the examples above.

Accounts without Contacts

Accounts without Opportunities

Accounts without Cases

Contacts without Phone Call Activities

However, this feature (in its current implementation) has limitations. As you can see from the screenshots above, you cannot add filter conditions for the child entity once you select the “Does Not Contain Data” filter condition at the entity level.

Due to this limitation, you still cannot create the following searches using this feature.

• Accounts without Opportunities in the last 6 months
• Contacts without Phone Calls in the last 1 year
• Accounts without Cases in the last month

You would need to use custom FetchXML to create these searches and views. I will walk you through the steps to create these complex “NOT IN” queries in the future post.