[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” !!
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
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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
3 thoughts on “[Power Automate – Dataverse] How to execute a fetchXml query that includes options not supported by the List rows action?”
Good stuff. Sad to say it’s needed. What PowerFX really needs right now is PowerQuery. Your elegant solution is convoluted due to these seemingly related teams not solving common problems together – such as the persistent need for Dataverse to include some level of analytics, because that’s how our business flows work. It requires infrastructure and secondary environment management devops engineering that’s just simply not the promise of the platform. This problem you are solving really limits the utility of Power Apps for my team.
LikeLiked by 1 person
Thanks for your feedback, I hope we can have soon the possibility to extend powerFx with our implementations. I think this is already in the roadmap of microsoft. Thanks again for reading and sharing some words on my blog.