Find Records Without a Child Record in Dynamics 365 v8 and v9

Satyvir Jasra, 20 June 2018

Dynamics 365 v9 comes with improved Advanced Find where we can retrieve the records with no child record. However, due to Advanced Find limitations in Dynamics 365 v8 we do not have the ability to run such a query.

Dynamics 365 Version 9

The Advanced Find query below shows how we can select Account records without any child Contact records in Dynamics 365 v9.

Running this query will return results if there are Account records with no association with a Contact records through Company Name field. Such as the one below.

This is a nice feature and allows you to get a better grasp on your business.

Dynamics 365 Version 8

Dynamics 365 v8 doesn’t have the ability to do this in Advanced Find, so we need to look at other methods. The following example shows how to achieve a similar result, using third-party tools such as FetchXML Builder in the XrmToolBox. Note that this approach is done outside of Dynamics 365, so your users are unable to run the query within the application itself.

We can use following FetchXML query to retrieve Account records with no Contact child record. I wanted to see only Account Name column in the results.

Then I ran my query in FetchXML Builder by clicking on OK followed by Execute.

The following screenshot shows the result of this query.

We can double click on the records and it will open the record in the browser too, which is handy if you want to view the record in Dynamics 365.