Power Automate: Retrieve more than 5000 records from dataverse or dynamics 365 CE/CRM

In this post, I will explain to retrieve more than 5000 records or large sets from Dataverse Using FetchXml, Paging Cookie, and More Records Flag.

Background

When using Dataverse or Dynamics 365 CE/CRM, the user can retrieve only 5000 records in a single fetch or query. This is Dataverse Limitation.

Approach

To overcome this limitation, we can use “Paging Cookie” and have a flag called “More Records” to retrieve all records in a loop until the last page using the flag. Here is the Microsoft article to retrieve all records using c#.

Overall Flow/Power Automate

Create/Initialize Variables

    1. Total Record Count – To know the total record count (For Analysis purposes only)
      • Type: Integer
    2. Page Number – To send the next page number on the request
      • Type: Integer
    3. FetchXml Paging Cookie – Dataverse returns the paging cookie as part of the response. This is Raw data and will be used to send a subsequent request
      • Type: String
    4. Paging Cookie – Modified version of the original paging cookie, which will be sent as a request
      • Type: String
    5. More Records – Dataverse returns this flag as part of the response. This is used to determine to break the loop
      • Type: Boolean
    6. JSON For XML Parsing – This is just a template that used to transform the paging cookie to XML
      • Type: Object

 

Main Operation

We are using Do until control. By default, this loop starts and continues until the More Records flags are set to false.

List Accounts

Using Fetchxml query with page number and Paging Cookie. For Page 1, the Paging cookie will be empty.

Fetch statement has a paging cookie. Below is the Power Fx statement

if(equals(variables(‘Page Number’),1),”,concat(‘paging-cookie=”’, substring(first(skip(split(string(xml( setProperty(variables(‘JSON For Xml Parsing’),’a’,variables(‘Paging Cookie’)))),'<‘),1)),2),””))

Set Variables

Incrementing the total count for each iteration. Finally, this variable has a total record count. This is used for Analysis purposes only. And other variable Page Number just incrementing for each iteration. This page number is used to send the request.

length(outputs(‘List_Accounts’)?[‘body/value’])

Dataverse has an attribute called PagingCookie as part of the response. Below is a variable to extract the Raw data from the response.

if(empty(outputs(‘List_Accounts’)?[‘body’]?[‘@Microsoft.Dynamics.CRM.fetchxmlpagingcookie’]),”,decodeUriComponent( decodeUriComponent(outputs(‘List_Accounts’)?[‘body’]?[‘@Microsoft.Dynamics.CRM.fetchxmlpagingcookie’])))

Paging Cookie variable is to extract only the information needs that used to send with the request.

if(empty(variables(‘FetchXml Paging Cookie’)),”,replace(substring( variables(‘FetchXml Paging Cookie’),add(indexOf(variables(‘FetchXml Paging Cookie’),’pagingcookie=”‘),14)),'” istracking=”False” />’,”))

More Record – Flag to determine whether the Do until loop break or continue

if(empty(string(outputs(‘List_Accounts’)?[‘body’]?[‘@Microsoft.Dynamics.CRM.morerecords’])), false, outputs(‘List_Accounts’)?[‘body’]?[‘@Microsoft.Dynamics.CRM.morerecords’])

Conclusion

Using Power Automate, We can retrieve more than 5000 records using Paging Cookie and More Records Flag. In this example, this flow runs 3 times, and the total count is 10139 records.

 

Leave a Reply

Your email address will not be published.