How to use FetchXml in PowerAutomate/Microsoft Flow to query more than 5K CDS data?

In this post, I’ll go through the paging idea and how to use FetchXML with Power Automate to obtain records from Data verse or Dynamics 365 CE/CRM.

Step 1: Create a Flow and load it with the necessary variables.

Make a new Instant flow and include three variable actions.

• Total Record Variable: This variable is used to hold the number of records returned and to add the total of each iteration.
• Paging Cookie: To save the raw fetchxml paging cookie value returned by the List row step response.
• Paging Cookie Only: used to save the extracted page-cookie value from the raw paging cookie.

The screenshot is below.

Step 2: Create an Object variable to hold the Raw JSON object.

Step 3: Define the loop do till and set variables values within the loop verify whether the paging cookie is blank. Continue the loop until the paging cookie becomes null. To read data within the loop, utilise the list row. The remaining stages are shown below.

The check in loop is used to determine whether or not the paging cookie variable is empty. If the loop is not blank, it will continue.

List Row sept of dataverse connector – Fetchxml with dynamic page count attribute and page cookie was used.

Page and paging-cookie expressions are provided below.

add(iterationIndexes(‘Do_until’),1)if(equals(iterationIndexes(‘Do_until’),0),”,concat(‘paging-cookie=”’, substring(first(skip(split(string(xml(setProperty(variables(‘RawJSON’),’z’,variables(‘PagingCookieOnly’)))),'<‘),1)),2),””))

The length of the returned row was used to increment the Count variable.

To read the raw paging cookie from the response of list row get accounts, use the Paging Cookie expression listed below.

The paging cookie is only updated as shown in the expression below.

After the test, the count will return 6100 accounts, which is greater than 5000.

Thanks for reading.