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?
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).
You can then filter on this rollup field as a normal field in CRM.