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

By | April 26, 2017

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

 

 

 

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

    1. Paolo Tomasi

      too complicated, sorry. We need something simpler. In Filemaker it is possible, why not in Dynamics?!?

      Reply
      1. Alex Shlega Post author

        This post was specifically about FetchXml capabilities – not about the UI. If you are familiar with Fetch, this should not be too complicated, but, if you are looking for a UI option, then I would agree with the assessment:)

        Reply
  1. Evan Watson

    You can upload the custom fetch xml to a view via XRM Toolbox. The key here is the view has to exist already and the columns are exactly the same. It works for us in D365 online.

    Reply
    1. Alex Shlega Post author

      Hi Evan,

      thanks for the note. That’s a very useful feature, I agree. Although, in this kind of situations I’d likely prefer to build a report to make use of the customized fetch since I would not have to mess with how Dynamics stores those fetch queries then.

      Reply
  2. Debasish Dandapat

    Thanks It was really helpful and it worked.

    But one issue regarding this. I am able to find the view in MS CRM through navigating from the sitemap.
    But when trying to find the view in the Advance find, unable to find the view in advance find.

    Reply
  3. Andrew Marshall

    Hi there, this is great.

    I’ve then tried to tweak my xml to show where for the parent record a particular value exists. e.g. All the parents owned by XXX where there is no child.

    I presume that’s easy enough.

    Reply
  4. Andrew Marshall

    Sorry – all sorted – i had done it correctly but was using a different field!

    This is brilliant !! thanks

    Reply

Leave a Reply to Andrew Marshall Cancel reply

Your email address will not be published. Required fields are marked *