cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mick282
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
8 REPLIES 8
abm
Super User
Super User

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 blogPower Automate Video Tutorials

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

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

 

Jcook
Super User
Super User

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





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 blogPower Automate Video Tutorials
abm
Super User
Super User

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 blogPower Automate Video Tutorials

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 resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,625)