cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lexicron
Resolver II
Resolver II

FetchXML aggregate query resulting in Flow error "Key property is null"

I am working in Flow, using the List records action under the CDS (current environment) connector. I used Jonas Rapp's FetchXML Builder (FXB) to build a GroupBy+Aggregation Fetch XML query, using SUM for certain fields and COUNTCOLUMN for other fields.

 

1. In FXB, the query works fine without the unique identifier field (ver_answersid) of my CDS entity.

2. In Flow, however, it's giving this error: "Key property 'ver_answersid' of type 'Microsoft.Dynamics.CRM.ver_answersid' is null. Key properties cannot have null values." So, I added an attribute line for it in the query, as shown in the screenshot and code block below:

Clipboarder.2020.07.03-003.png

 

 

 

<fetch version="1.0" output-format="xml-platform" mapping="logical" aggregate="true" >
  <entity name="ver_answers" >
    <attribute name="ver_answersid" alias="ver_answersid" aggregate="count" />
    <attribute name="ver_classtype" alias="ClassType" groupby="true" />
    <attribute name="ver_rawscore1" alias="Score1Sum" aggregate="sum" />
    <attribute name="ver_rawscore2" alias="Score2Sum" aggregate="sum" />
    <attribute name="ver_rawscore1" alias="Score1Count" aggregate="countcolumn" />
    <attribute name="ver_rawscore2" alias="Score2Count" aggregate="countcolumn" />
  </entity>
</fetch>

 

 

 

It doesn't work.

 

Below are the alternatives that I've tried and the results I got from each combination in FXB and in Flow:

Attribute nameAttribute aliasAggregate functionIn FXBIn Flow
"ver_answersid""ver_answersid"countcolumnError: UnexpectedError: Unexpected
"ver_answersid"<> attribute namecountcolumnWorksError: Key property null
any other field in entity"ver_answersid"countcolumnError: UnexpectedError: Unexpected
"ver_answersid""ver_answersid"countError: UnexpectedError: Unexpected
"ver_answersid"<> attribute namecountWorksError: Key property null
any other field in entity"ver_answersid"countError: UnexpectedError: Unexpected
"ver_answersid""ver_answersid"avgError: Not supportedError: Not supported
"ver_answersid"<> attribute nameavgError: Not supportedError: Not supported
any other field in entity"ver_answersid"avgError: Not supportedError: Not supported
"ver_answersid""ver_answersid"maxError: Not supportedError: Not supported
"ver_answersid"<> attribute namemaxError: Not supportedError: Not supported
any other field in entity"ver_answersid"maxError: Not supportedError: Not supported
"ver_answersid""ver_answersid"minError: Not supportedError: Not supported
"ver_answersid"<> attribute nameminError: Not supportedError: Not supported
any other field in entity"ver_answersid"minError: Not supportedError: Not supported
"ver_answersid""ver_answersid"sumError: Not supportedError: Not supported
"ver_answersid"<> attribute namesumError: Not supportedError: Not supported
any other field in entity"ver_answersid"sumError: Not supportedError: Not supported
"ver_answersid""ver_answersid"nothingError: Fetch aggregate requires all attributes to be either a groupby or an aggregateError: Fetch aggregate requires all attributes to be either a groupby or an aggregate
"ver_answersid"<> attribute namenothingError: Fetch aggregate requires all attributes to be either a groupby or an aggregateError: Fetch aggregate requires all attributes to be either a groupby or an aggregate
any other field in entity "ver_answersid"nothingError: Fetch aggregate requires all attributes to be either a groupby or an aggregateError: Fetch aggregate requires all attributes to be either a groupby or an aggregate
nothingnothingnothingWorksError: Key property null

 

I'm not doing distinct="true" and I'm not doing groupby="true" for this field, because those are not what I want. The values in this field are unique for each record and will void my GroupBy(ClassType) purpose entirely.

 

As you can see, nothing works in Flow. I've found other solved posts like this one from @Helpful and a blog post from Ryan Maclean where they managed to find a solution, however neither solution works for my situation. I strongly believe this is a bug in Flow. My original query from FXB without the key property/unique identifier should just work, it's perfectly valid. Flow's original error about "Key property is null" is ridiculous and points to a bug. The unique identifier field is a UNIQUE IDENTIFIER field, it's automatically ALWAYS filled with a value for each record, HOW can it be null??

 

Would anyone else have other insight or solution to this issue please?

1 ACCEPTED SOLUTION

Accepted Solutions

Conclusion: Aggregation currently not available in Flow's FetchXML.

 

Also, aggregation by count is also not available in Flow's other CDS connector https://powerusers.microsoft.com/t5/Building-Flows/How-to-use-Aggregation-transformation-in-Flow-Lis...

View solution in original post

4 REPLIES 4
Lexicron
Resolver II
Resolver II

Have raised a MS support ticket about this issue.

Conclusion: Aggregation currently not available in Flow's FetchXML.

 

Also, aggregation by count is also not available in Flow's other CDS connector https://powerusers.microsoft.com/t5/Building-Flows/How-to-use-Aggregation-transformation-in-Flow-Lis...

View solution in original post

rappen
Advocate I
Advocate I

Hi @Lexicron 

Did you ever get to any nicer conclusion than that it doesn't work?

Hi @rappen 

 

Sadly no. I've also tried it again as recently as early December, and it remains unimplemented by Microsoft. 

 

From my interaction with the engineer back then, they weren't going to escalate it to the product team to add 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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,998)