Dynamics 365: Can’t find all parent records that have no child records using Fetch (Myth BUSTED!)

It is an incredibly popular question: how do we find all parent records which do not have some kind of related child records?

• How do we find all accounts without opportunities
• How do we find all teams without users
• How do we find all Parent entity records that have no Child records associated with them

And there is an incredibly popular answer: IT IS NOT POSSIBLE.

Well, it actually is. Although, not in the advanced find. But you don’t have to believe me – I’ll simply show you how it’s done.

In my Dynamics 365 environment, I have created two custom entities: Parent and Child.

The first two (“Another Parent” and “Main”) have child records linked to them. For example:

 

The last one (“No Children”) has no child records:

 

So there are a couple of questions we may want to ask in this situation:

• What are all the parents that have some children?
• What are all the parents that have no children at all?

The first question is easy to answer using Advanced Find:

 

Just as expected, there are two parent records.

How about the second question? Can we replace that “Contains Data” filter with “Does not contains Data” and get meaningful results?

 

Apparently, we can change the filter, but here is what we are going to get as a result:

Wow, it’s empty! I was hoping for a bit better result – remember I do have that “No Children” parent record?

So.. IT IS IMPOSSIBLE, after all, right? No, we are not done yet.

Let’s download that FetchXml from Advanced Find and modify it a little.

Here is the original:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”tcs_parent”>
<attribute name=”tcs_parentid” />
<attribute name=”tcs_name” />
<attribute name=”createdon” />
<order attribute=”tcs_name” descending=”false” />
<link-entity name=”tcs_child” from=”tcs_parentid” to=”tcs_parentid” alias=”ad”>
       <filter type=”and”> 
           <condition attribute=”tcs_childid” operator=”null” />
      </filter>
   </link-entity>
</entity>
</fetch>

And here is the modified version:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”>
<entity name=”tcs_parent”>
<attribute name=”tcs_parentid” />
<attribute name=”tcs_name” />
<attribute name=”createdon” />
<order attribute=”tcs_name” descending=”false” />

   <filter type=”and”> 
           <condition entityname=”tcs_child” attribute=”tcs_childid” operator=”null” />
      </filter>
    <link-entity name=”tcs_child” from=”tcs_parentid” to=”tcs_parentid” alias=”ad” link-type=”outer”>
    </link-entity>
</entity>
</fetch>

I have highlighted the modified part for you. First, it has to be an “outer” join. Second, that “filter” condition has to move up, and, yet, I have also added entityname attribute to the condition.

That’s fully compatible with the FetchXml schema, btw. If you are interested, have a look at the schema:

https://msdn.microsoft.com/en-us/library/gg309405.aspx?f=255&MSPPError=-2147217396

The next step is a bit tricky – I need to test that FetchXml. I can’t easily put it back into CRM to replace an existing view, for example. However, Dynamics 365 comes with WEB API which actually allows us to run fetch query(did you know?)

Once you have logged into Dynamics, you can construct this kind of url:

https://<YOUR DYNAMICS URL>/api/data/v8.2/<PLURAL FORM OF THE ENTITY NAME>?fetchXml=<YOUR FETCH QUERY>

So, let’s try it. For the original FetchXml, here is what I got:

Now here is what this whole post was for – I am going to try modified FetchXml. And, here we go:

In other words, it’s absolutely possible to use FetchXml for this kind of “not exists” conditions. We may not be able to create views in Dynamics, but we can easily use it to develop plugins, reports, custom applications.. or simply to run those fetch queries through Web API if we do want the results quickly.

PS. By the way, the credit for this method goes to Aiden Kaskela. If you are curious to see where it all started, have a look here: https://community.dynamics.com/crm/f/117/t/195910