cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helpful
Resolver III
Resolver III

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?

1 ACCEPTED SOLUTION

Accepted Solutions

I have identified a workaround:  the alias of the key property MUST be the same name as the attribute.

 

For example:

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

 

In my view this is still a defect, but for anyone that is stuck on this I hope it saves you many, many hours.

View solution in original post

12 REPLIES 12
yashag2255
Dual Super User II
Dual Super User II

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!

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

 

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>

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!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

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.

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!


Follow me on Twitter at @QG_LeeJHarris

Or on LinkedIn at in/leejharris

I have identified a workaround:  the alias of the key property MUST be the same name as the attribute.

 

For example:

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

 

In my view this is still a defect, but for anyone that is stuck on this I hope it saves you many, many hours.

View solution in original post

Has FetchXml gone away

I hope not because we have stuff that depends on it!

If you’re not seeing it on your List Records action make sure you’re using the CDS (current environment) connector.

Thanks for the tip. I found it. This is amazing and powerful. So much easier tha

n odata inside flow

Life saviour!! I had spent a few hours on this already but you def saved me spending a few more hours and a long night 🙂

as a reference for others finding this thread: I have seen the same behavior in "Aggregation transformation" with List Records for CDS.

 

This does not work

 

groupby(
 (statecode),
 aggregate(amount with sum as amount_sum)
)

 

the sum is not included in the result set

 

this does work

 

groupby(
 (statecode),
 aggregate(amount with sum as amount)
)

 

 it includes "amount" with the total sum in it...

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,940)