Microsoft Dynamics 365 July 2017 Update - Not In Queries for Views and Advanced Find

David Mochrie, 18 October 2017

The single most requested feature in Microsoft Dynamics 365 on Microsoft Connect, and now on the Microsoft Ideas site, is to have ‘Not In’ queries for views and advanced find.

For example:

• Accounts with no Contacts
• Contacts with no Cases
• Accounts with no Opportunities

Previously, it has been possible to do this by manipulating the Fetch XML in a view, but no way to do it directly in the application.

In the July 2017 release of Microsoft Dynamics 365, Microsoft have taken the first step to addressing this.
It’s now possible to configure basic 'Not In' queries. That is, you can find all records of an entity where they have no child records at all through one or more relationships, either active or inactive.

For example, I could show all active Accounts with no Contacts. I’ve loaded ‘ABC Ltd’ into Dynamics 365 and not added any Contacts to it.

In Advanced Find, I base the query on ‘Active Accounts’ and then select the relationship ‘Contacts (Company Name)’.

 
When I select the ‘Contacts (Company Name)’ relationship, an option set shows next to it.

When I select ‘Does Not Contain Data’, I lose the ability to add any clauses to the Contact entity part of the query, and get a warning to that effect:

 
When I accept that warning, I see this:

When I select the results:

I could also add another ‘Not In’ relationship to the query, such as Cases:

So, this query now finds all the active Accounts with no Contacts AND no Cases.

The limitation is clear though. With no option to add conditions to the related entity, I couldn’t create queries such as ‘Active Accounts with no Active Contacts’, as there’s no way to restrict the Contacts to just active ones.
So, in summary, this is a first step towards ‘Not in’ queries that so many have asked for but doesn’t yet give full ‘Not In’ functionality.