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 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'])

About the Author

Microsoft Business Applications MVP and Senior Technical Consultant who has been working with Dynamics for over 10 years starting with CRM 4.0

Reference

Win. L. Z., 2022, DYNAMICALLY LIST ROWS FROM ANY DATAVERSE TABLE WITH FETCHXML IN POWER AUTOMATE, Available at: https://linnzawwin.blogspot.com/2022/11/dynamically-list-rows-from-any.html. [Accessed on 11 January 2023]