Common Data Service (current environment) List Record Fetch XML Aggregate

[Mick282]

Hi all,

So I'm having a bit of trouble with the new CDS (current environment) connector and especially the List Record step. I'm trying to use the Fetch Xml Query area of the List record step to filter the list but what I really want is to perform a count aggregate using fetch.

Below is a screenshot of what I'm trying to do.

The expected result should be 4 but I'm getting a error. Is it not possible to use a fetch XML aggregate in the list record step?

I can still do the count by using an expression after I've retrieved a list of records:

length(outputs('Count_Opportunities')?['body/value'])

The result if I just retrieve it with a normal fetch xml as opposed to a aggregate

But I don't need to retrieve all the opportunities and their fields to just do a count. I'm trying to make the flow a bit more efficient. Any help would be greatly appreciated.

[abm]

Hi @Mick282 

What error are you getting?

Thanks

[Mick282]

Hi @abm 

Below is a screenshot and the error message:

The error message is:

{"_error":{"Code":null,"Description":"Key property 'opportunityid' of type 'Microsoft.Dynamics.CRM.opportunity' is null. Key properties cannot have null.

It's weird because I'm certain opportunityid exists. I checked my FetchXML in FetchXML builder and this is what I'm getting. It does look like:

It's still returning the number of 4 although the Id is blank

[Helpful2]

Hi All,

I'm seeing an error using FetchXML aggregation as well.  I started with a more complicated structure and was getting Internal Server 500's so pared it down to this (authored in FetchXML Builder):

<fetch aggregate="true" >
  <entity name="contact" >
    <attribute name="annualincome" alias="AnnualIncome_sum" aggregate="sum" />
  </entity>
</fetch>

And still getting the error:

[Jcook]

Hello,

Can you try to use single quotes instead of the double quotes.

—Josh
If you like my post please hit the "Thumbs Up" -- If my post solved your issue please "Mark as a Solution" to help others

[abm]

Hi All,

I did tried and got the same error 500.

@Jcook  I tried with single quotes but made no difference at all.

I can run other fetchxml query except the aggregate count one.

You can run the aggregate count via D365 SDK code. Believe this might be a bug or flow limitation.

Thanks

[abm]

Hi @Mick282 , @Helpful2 , @Jcook 

More update.

So I did tried via Invoke an HTTP request action.

My test result:

Limitations

Queries that return aggregate values are limited to 50,000 records. This limit helps maintain system performance and reliability. If the filter criteria in your query includes more than 50,000 records you will get the following error:

Error code: -2147164125
Hexadecimal error code: 8004E023
Platform error message: AggregateQueryRecordLimit exceeded. Cannot perform this operation.
Client error message: The maximum record limit is exceeded. Reduce the number of records.

To avoid this error add appropriate filters to your query to ensure that it will not need to evaluate more than 50,000 records. Then run you query multiple times and combine the results.

Reference: 

https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/use-fetchxml-aggregation

Thanks

[Helpful2]

Do we interpret @abm's results as the previously failing FetchXml working as expected using the "Invoke an HTTP request" action?

Meaning there's likely a defect in the CDS (current environment) List Records action when using FetchXml with sum aggregation.

I also validated that my little FetchXml works correctly in Postman.  It's my understanding that the List Records action submits the FetchXml to CDS OData APIs so if it works in Postman, it should work in List records - but it doesn't.

[Helpful · Accepted solution]

Workaround found:  https://powerusers.microsoft.com/t5/General-Power-Automate/Using-FetchXML-with-List-records-in-CDS-c...