{Solved} Key properties cannot have null values error when performing aggregate queries using List records step in Power automate

Sometimes the simplest of the requirements surprise you and here is one of them. With Power automate becoming stronger with every release, it is unfathomable that these issues still exist in Power platform.

So what’s the requirement here? It can be as simple as you can imagine of. I needed to perform some aggregate operation in Power automate. For our example – let’s take a very simple requirement. Say we have the below query to execute.

<fetch aggregate="true" >
  <entity name="contact">
    <attribute name="creditlimit" alias="totalcreditlimit" aggregate="sum" />
  </entity>
</fetch>

So we have the contact table and we want to find out the sum of the creditlimit field for all contacts in the system. I take the above fetchxml and use it in the List records action of CDS (current environment) connector.

Thinking it would be cakewalk for Power automate right? Not quite though. Below is the error you get when you execute your flow.

Key property 'contactid' of type 'Microsoft.Dynamics.CRM.contact' is null. Key properties cannot have null values.

Quite surprised I even tried this code in Fetchxml builder just to make sure the fetchxml is correct. It turned out to be correct and gave me the expected results.

So what are the options here? We have couple of options. Either we can use Http request action to execute the fetchxml request or even better we can use the CDS connector (not the current environment one).

We cannot run FetchXml here but certainly our OData is quite equipped as well.

And below is the result.

{"@odata.id":"https://adobedev01.crm.dynamics.com/api/data/v9.0/contacts(00000000-0000-0000-0000-000000000000)","@odata.etag":"","ItemInternalId":"00000000-0000-0000-0000-000000000000","creditlimit":600.0}

The output I have highlighted in bold. Also before I end, what I observed is the result is returning only if you put the alias same as the field name. Like in my example, the field name is creditlimit and alias is creditlimit as well.