Dynamically List Rows From Any Dataverse Table with FetchXML in Power Automate

With the List Rows action of Microsoft Dataverse, we can use FetchXML to query the data. For such queries, all we need is to populate the Table name and Fetch Xml Query parameters. But what if we only have the FetchXML? What if we want to query dynamically from different tables based on the FetchXML? One of the use cases would be storing the FetchXML in the config table where the user admin maintains the value and the cloud flow would query the data based on the FetchXML and process the data regardless of which table it is.

The solution is to populate the Table name parameter of the List Rows action dynamically based on the entity name from the FetchXML. The only problem is that the entity name from the FetchXML is the logical name and the Table name parameter accepts the EntitySetName (the plural version of the logical name). Luckily, we can easily retrieve the EntitySetName of the table by another List Rows action query from the Entity table.

In this post, you will also learn how to retrieve the EntitySetName (the plural name) of the table dynamically from the logical name of the table by retrieving the Entities metadata table.

In the sample child flow below, the input parameter is the FetchXML and it returns the number of rows from any table based on the FetchXML as an output parameter to the Power App or parent flow.

The first step is to query the entitysetname column from Entities table where the name is equal to the entity name from the FetchXML. To extract is with the string manipulation, use the first(split(last(split functions as mentioned in the Power Automate Gymnastics Reference Guide and get the value between <entity name="   VALUE    ">.

first(split(last(split(triggerBody()['text'], '<entity name="')), '">'))

The next step is to populate the Table name parameter with the value from the List rows above. Since there will only be one row, use first() function and take the entitysetname value. And populate the Fetch Xml Query with the FetchXML from the input parameter.

first(outputs('List_Entities_by_Logical_Name')?['body/value'])?['entitysetname']

Finally, return the row count (length of the array) from the output of the 2nd List rows action.

length(outputs('List_rows_by_FetchXML')?['body/value'])

Calling the child flow from the parent will result in something similar to the one below. The input is the FetchXML (List accounts with the name starting with A) and the result is row_count = 6.