cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helpful
Level 8

Using FetchXML with List records in CDS (current environment)

Hello,

The new CDS (Current environment) connector's "List records" action appears to support FetchXML, however, it throws errors (500) when using aggregation.

Can anyone provide some kind of documentation reference?

Or test a basic scenario where you're pulling linked entities with an aggregation?

6 REPLIES 6
Dual Super User
Dual Super User

Re: Using FetchXML with List records in CDS (current environment)

Hi @Helpful 

 

Please check the thread below https://powerusers.microsoft.com/t5/Building-Flows/Recurrence-Flow-Send-An-Email-action-for-outstand...

 

Here the XRM toolbox has been used to generate the fetchxml query to create a filter to get records. 

 

Please share a screenshot of the flow that you have built so far so that we can better assist you. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Helpful
Level 8

Re: Using FetchXML with List records in CDS (current environment)

Yep, using FetchXML Builder to construct the XML.  In the tool, it retrieves the records exactly as designed.  In the Flow, however, it throws an error.  My query includes aggregation.

  • When I test without aggregation, it executes in 1s and brings linked entities and everything back as it should
  • When I test WITH aggregation I get an error that includes:  
    "body":{"_error":{"Code":null,"Description":"Key property 'zco_projectbudgetid' of type 'Microsoft.Dynamics.CRM.zco_projectbudget' is null. Key properties cannot have null values."The working screenshot:

Any ideas would be welcome.

A screenshot of the working query - no aggregation:working_no_aggregation.png

 

A screenshot of the failing query with aggregation (note that in this screenshot I removed the attributes from the root just to see if I got a different result)

distinct="true" returntotalrecordcount="true"

:failing_aggregation.png

 

Helpful
Level 8

Re: Using FetchXML with List records in CDS (current environment)

Here's the XML if you'd like to test something similar:

<fetch distinct="true" aggregate="true" returntotalrecordcount="true" >
  <entity name="zco_projectbudget" >
    <attribute name="zco_projectbudgetid" alias="ProjectBudgetId" groupby="true" />
    <filter type="or" >
      <condition attribute="statuscode" operator="eq" value="1" />
      <condition attribute="statuscode" operator="eq" value="796500002" />
      <condition attribute="statuscode" operator="eq" value="796500003" />
    </filter>
    <link-entity name="zco_project" from="zco_projectid" to="zco_projectid" >
      <link-entity name="zco_worker" from="zco_workerid" to="zco_projectmanagerid" >
        <attribute name="zco_primaryemail" alias="ProjectManagerEmail" groupby="true" />
      </link-entity>
    </link-entity>
    <link-entity name="zco_projectbudgetline" from="zco_projectbudgetid" to="zco_projectbudgetid" link-type="outer" >
      <attribute name="zco_projectbudgetlineid" alias="ProjectBudgetLineId" groupby="true" />
      <attribute name="zco_budgetline_amount" alias="BudgetLineAmount" groupby="true" />
      <filter>
        <condition attribute="statuscode" operator="eq" value="1" />
      </filter>
      <link-entity name="zco_budgetlineperiod" from="zco_projectbudgetlineid" to="zco_projectbudgetlineid" link-type="outer" >
        <attribute name="zco_amount" alias="BudgetPeriodsAmount_Sum" aggregate="sum" />
        <filter>
          <condition attribute="statuscode" operator="eq" value="1" />
        </filter>
      </link-entity>
    </link-entity>
  </entity>
</fetch>
Super User
Super User

Re: Using FetchXML with List records in CDS (current environment)

Hi @Helpful

Did you have any luck solving this? I am seeing the same issue when using aggregation in fetchxml.

 


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

Proud to be a Flownaut!


Helpful
Level 8

Re: Using FetchXML with List records in CDS (current environment)

Hey @LeeHarris,

I ended up putting a roll-up field on the records I was concerned about and then querying for records with a certain value in the roll-up.

Got the result I wanted, but seems like an unecessary workaround.

Super User
Super User

Re: Using FetchXML with List records in CDS (current environment)

Hi @Helpful 

Rollups are unfortunately not an option for me due to the limit of 10 per entity, already maxed this out.

Thanks for letting me know anyway.

 

 


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

Proud to be a Flownaut!


Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

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!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,015)