cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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.

 

image.png

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 aggregateThe 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver III
Resolver III

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

8 REPLIES 8
Highlighted
Super User III
Super User III

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

Hi @Mick282 

 

What error are you getting?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Helper III
Helper III

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

Hi @abm 

 

Below is a screenshot and the error message:

 

image.png

 

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 blankIt's still returning the number of 4 although the Id is blank

Highlighted
Microsoft
Microsoft

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

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:

fetchxml.png

 

Highlighted
Super User III
Super User III

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

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




Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Super User III
Super User III

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

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Super User III
Super User III

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

Hi @Mick282 , @Helpful2 , @Jcook 

 

More update.

 

So I did tried via Invoke an HTTP request action.

 

image.png

 

My test result:

 

 

image.png

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Highlighted
Microsoft
Microsoft

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

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.

Highlighted
Resolver III
Resolver III

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

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (7,415)