Back to Blog Index

Creating system views with complex “NOT IN” filter in Dynamics 365 for Customer Engagement

By Abi Shende, Sr. CRM Functional Consultant, Arbela Technologies

In the previous post, we reviewed the new feature in Dynamics 365 v9 that enables users to search for records in one entity without any data in a related child entity using Advanced Find. For example, Accounts without any Contacts.

However, as mentioned in that post, this feature (in its current implementation) has limitations. 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 created in the last 6 months
  • Contacts without Phone Calls in the last 1 year
  • Accounts without Cases in the last month

In this post, I will walk you through the steps to create a System View that returns Accounts without Opportunities created in the last 6 months.

Create a system view with the following filter conditions. This view returns Account that DO have Opportunities created in the last 6 months.

Save the View and Publish changes.

Next, we will use the View Designer tool in the XrmToolBox to modify the FetchXML for this view to accomplish our goal.

Start XrmToolBox.

Connect to your CRM instance.

Select the View Designer tool.

Open the System View you created above.

Click Edit Query.

In the Query Builder, select link-entity opportunity (aa) and change Link type to “outer”.

Next, add the following filter condition for the Account.

Click Execute (F5) to test your query.

Once you have verified the query results, click Return FetchXML to use this FetchXML.

This will take you back to View Designer.

Now, click Save and Publish to publish the View.

Go back to Dynamics 365 and refresh the browser.

Verify that the view now displays the expected results.


In this post, we walked through the steps to create System Views with complex “NOT IN” filter condition.