dynamics-crmfetchxmldataverse

Filtering FetchXML request by number of records in related entity


In Microsoft Dynamics/Dataverse, I'm trying to write out a FetchXML request on an entity which is related to another entity in an 1:M relationsship. I want it to retrieve parent records which are related to 3 or more child records and ignore the rest. Can this be done?

What I have is:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
<entity name="quote">
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="linked_entity" from="name" to="someid" link-type="outer" />
<filter type="and">
<condition attribute="trefor_kundetype" operator="eq" value="402180000" />
</filter>
</link-entity>
</entity>
</fetch>

I've been looking in to the aggregate function, but this will just return total record count. Can this be done, and if so how would I proceed?


Solution

  • It looks like you are trying to do a query like this (but with something like a having statement):

    <fetch aggregate='true'>
      <entity name='account'>
        <attribute name='name' alias='account_name' groupby='true' />
        <link-entity name='contact' from='parentcustomerid' to='accountid'>
          <attribute name='contactid' alias='no_of_persons' aggregate='count' />
        </link-entity>
      </entity>
    </fetch>
    

    Unfortunately fetch xml does not support having statements - and even with the simple query above you will run into issues like: "AggregateQueryRecordLimit exceeded. Cannot perform this operation."

    I think your best approach would be to create a rollup field on the parent entity (that counts the child items).

    https://learn.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-rollup-fields?view=op-9-1

    You can then filter on this rollup field as a normal field in CRM.