[Power Automate – Dataverse] How to execute a fetchXml query that includes options not supported by the List rows action?

Dataverse list rows connector allows to retrieve rows from a Dataverse table. This connector can filter rows using OData expressions or fetchXml queries. Unfortunately, the connector does not support all fetchXml requests. This blog will discuss an approach to execute this kind of unsupported queries.

Let’s look at what the documentation says:

The distinct operator and aggregation queries are not currently supported in FetchXML queries from the List rows connector.

Let’s try a fetchXml query with the option distinct=”true” !!

<fetch distinct="true" >
  <entity name="contact" >
    <attribute name="address1_country" />
  </entity>
</fetch>

Here is the output of this query with the list rows connector:

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

Indeed, the List row connector needs to know the primary key of the table for its correct use

Suggested solution:

My first approach was to remove the distinct option and calculate the unique values on Power Automate. It’s true that this will work fine, but it will require downloading all the data, and it may take time if the data is large. So, the approach is to delegate this operation to Dataverse.

To delegate the calculation to Dataverse, the server’s extension capabilities can be used. Indeed, this approach will execute the fetchXml through a Custom Api, and call it from Cloud Flow with the Dataverse connector action ‘Perfom unbound action’.

Below is the definition of the Custom Api:

For inputs, only one is needed, which is the fetchXml:

Finally, for output, the Custom Api will return an EntityCollection:

Now for the implementation:

For example, this custom api can be called from a Power Apps. Below is the flow used:

In a Power App, the Custom Api response can be displayed in a gallery:

Let’s take a look at how it works on the Power Apps. First, when changing the input fetchXml:

UpdateContext({result: ExecuteFetchXmlFromPowerApps.Run('fetchXml Input'.Text).result});
ClearCollect(
    countriesCollection,
    MatchAll(
        result,
        "\{""@odata.type"":""(?<odataType>[^""]*)"",""address1_country"":""(?<address1_country>[^""]*)"",""address1_composite"":""(?<address1_composite>[^""]*)""\}"
    )
);

The bottom part is equal to the response of the Cloud Flow ‘Execute FetchXml From PowerApps’ . Indeed, it is an array of objects:

Finally, the content of the gallery is the collection ‘countriesCollection’

Hope it helps …