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:
<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 name | Attribute alias | Aggregate function | In FXB | In Flow |
| "ver_answersid" | "ver_answersid" | countcolumn | Error: Unexpected | Error: Unexpected |
| "ver_answersid" | <> attribute name | countcolumn | Works | Error: Key property null |
| any other field in entity | "ver_answersid" | countcolumn | Error: Unexpected | Error: Unexpected |
| "ver_answersid" | "ver_answersid" | count | Error: Unexpected | Error: Unexpected |
| "ver_answersid" | <> attribute name | count | Works | Error: Key property null |
| any other field in entity | "ver_answersid" | count | Error: Unexpected | Error: Unexpected |
| "ver_answersid" | "ver_answersid" | avg | Error: Not supported | Error: Not supported |
| "ver_answersid" | <> attribute name | avg | Error: Not supported | Error: Not supported |
| any other field in entity | "ver_answersid" | avg | Error: Not supported | Error: Not supported |
| "ver_answersid" | "ver_answersid" | max | Error: Not supported | Error: Not supported |
| "ver_answersid" | <> attribute name | max | Error: Not supported | Error: Not supported |
| any other field in entity | "ver_answersid" | max | Error: Not supported | Error: Not supported |
| "ver_answersid" | "ver_answersid" | min | Error: Not supported | Error: Not supported |
| "ver_answersid" | <> attribute name | min | Error: Not supported | Error: Not supported |
| any other field in entity | "ver_answersid" | min | Error: Not supported | Error: Not supported |
| "ver_answersid" | "ver_answersid" | sum | Error: Not supported | Error: Not supported |
| "ver_answersid" | <> attribute name | sum | Error: Not supported | Error: Not supported |
| any other field in entity | "ver_answersid" | sum | Error: Not supported | Error: Not supported |
| "ver_answersid" | "ver_answersid" | nothing | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate |
| "ver_answersid" | <> attribute name | nothing | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate |
| any other field in entity | "ver_answersid" | nothing | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate | Error: Fetch aggregate requires all attributes to be either a groupby or an aggregate |
| nothing | nothing | nothing | Works | Error: 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?
