dynamics-crmmicrosoft-dynamicsfetchxml

FetchXml: get only records for which all related records satisfy the condition

Dynamics 365 v8.2. FetchXml.

Each isv_entity1 record can be linked with one or more isv_entity2 records (one to many). I need to get only isv_entity1 records for which all linked isv_entity2 records correspond some condition. If at least one linked isv_entity2 record does not meet the condition, then the isv_entity1 record should not fall into the selection.

How can I do it via FetchXml?

If I will use this query:

<fetch distinct='true' >
  <entity name='isv_entity1' >
    <attribute name='isv_entity1id' />    
    <link-entity name='isv_entity2' from='isv_entity2id' to='isv_entity2id' >
      <filter type='and' >
          <condition attribute='isv_someProp' operator='eq' value='someValue' />
      </filter>
    </link-entity>
  </entity>
</fetch>

then I get the isv_entity1 records which have at least one linked isv_entity2 corresponds the condition. But it is not the same what I need.

Solution

  • What you need is: find all parent records where all child records have a given value (but not just some child records)

    The above is sql equivalent of your requirement, unfortunately sub-queries are not supported in FetchXML.

    So either you have to use two queries or to query all the records and iterate to verify/filter the results that match your criteria