Microsoft Dynamics: Finding Parent Records with No Child Records using XrmToolBox and FetchXml

Preface: If you’re working with Dynamics CRM Online you may have run into the problem below, and the following will be helpful. If you’re working with an on-premise installation I would skip this tip and query the sql server directly, as that would be more efficient. That being said, this solution would work with either on-premise or online versions.

Recently, I had a client ask me if I could pull up a list of all their Accounts which had no child Forecast records(our custom entity). If you have ever tried to use Advanced Find to do something like this you’ll quickly notice Advanced Find only wants to show your Parent Records WITH child records, not vice versa. Outer join in this case is not supported.

However, I was able to fulfill their request, and this is how I did it.

We are going to use the following FetchXml for our outer join. You can replace the “cp_forecast” with the logical entity name of any relevant child record. For ease in copying and pasting:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="account">
<attribute name='name'/>
<link-entity name="cp_forecast" from="cp_parentaccountid" to="accountid" link-type='outer' />
<filter type='and'>
<condition entityname='cp_forecast'
attribute='cp_parentaccountid'
operator='null'/>
</filter>
</entity>
</fetch>

Paste your fetchXml in the request section and click Execute.

Assuming all went smoothly you should see something like this appear in the response box:

But we’re not done. I still had to format the data so my client would be able to read this list with ease. So, naturally I used excel.

Copy and paste the result into an excel sheet. You could use Ctrl + F find and replace all the values you don’t want, but you would have to do that each time you ran this report. Instead, I used the SUBSTITUTE excel formula to delete the repeating values I didn’t want. This will save me a few clicks the next time I do this.