How to get more than 5000 records from dataverse in power automate

Introduction

When working with Dataverse in Power Automate, you’re limited to retrieving a maximum of 5,000 records per request. To overcome this, you should implement pagination. This guide walks you through the methods to retrieve more than 5,000 records efficiently in Power Automate.

Using query expression

If you are not using FetchXml, you can use this method to implement pagination. Follow the steps below to enable it in your Power Automate flow.

Select the List rows action.

Enable the pagination under the Settings tab in the Networking section.

Additionally, you can provide the maximum number of rows requested in the threshold value. The maximum configurable threshold is 100,000.

To learn more about the threshold value, check out Microsoft’s documentation.

Using FetchXml

If you are using FetchXml to filter records, the above method will not work. You have to manually add steps to handle the pagination in dataverse using the paging cookie.

When retrieving records from Dataverse and it contains more records after the first page, the response includes a component known as the paging cookie. This paging cookie serves as a key to seamlessly navigate through large datasets, enabling you to fetch records beyond the initial limit in subsequent requests.

First, initialize a variable named PageNumber which will store the current page number.

Initialize a variable named PagingCookie to store the value of the paging cookie retrieved from the response.

Create a variable named TotalRecords of type Integer and set the initial value to 0.

Next, create a do-until loop that will run until the PagingCookie variable is empty.

Inside the loop, add a dataverse list rows action with your FetchXML.

In FetchXML, we are using two dynamic values for page number and paging-cookie.

Use the below sample code to create your FetchXML. We have to encode the retrieved paging-cookie value, I will explain this in more detail later in the article.

<fetch version='1.0' mapping='logical' no-lock='true' count='5000' distinct='true'       
page='@{variables('PageNumber')}'  
paging-cookie='@{if(
    not(empty(variables('PagingCookie'))),
    encodeXmlValue(variables('PagingCookie')),
    ''
)}' >
  <entity name='contact'>
    <attribute name='statecode'/>
    <attribute name='fullname'/>
    <attribute name='createdon'/>
    <attribute name='contactid'/>
    <order attribute='createdon' descending='false'/>
    <filter type='and'>
      <condition attribute='statecode' operator='eq' value='0'/>
    </filter>
  </entity>
</fetch>

Next, add a Compose action to retrieve the Paging cookie from the response.

You can retrieve the paging cookie using the following code –

outputs('List_rows')?['body/@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']

Next, add an Increment variable action to increment the page number by 1.

Now, add another Increment variable action to increment the TotalRecords variable by the total number of records received in this iteration.

Use the below sample code to get the total number of records –

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

The paging cookie that is returned in the response is structured in the following format –

<cookie pagenumber="2" 
pagingcookie="%253Ccookie%2520page%253D%25221%2522%253E%253Ccreatedon%2520last%253D%25
222024-11-01T13%253A22%253A22%252B05%253A30%2522%2520first%253D%25222024-08-
24T05%253A20%253A57%252B05%253A30%2522%2520%252F%253E%253Ccontactid%2520last%253D%2522%
257BE981EED2-E798-EF11-8A69-6045BD55FB61%257D%2522%2520first%253D%2522%257BB2FCCC7D-AA61-
EF11-A670-000D3C4512E9%257D%2522%2520%252F%253E%253C%252Fcookie%253E" 
istracking="False" />

From the above response, we need only the pagingcookie attribute value, which we will send in the subsequent requests.

To retrieve the paging cookie value from the response, follow the steps below –

• Get the value of the pagingcookie attribute
• URL decode the value twice

You can use the below sample code in your Power Automate flow to retrieve the value. Given that the value of the paging cookie from the response is in the variable named PagingCookieOriginal.

decodeUriComponent(
    decodeUriComponent(
        trim(
            first(
                split(
                    last(
                        split(
                          variables('PagingCookieOriginal'), 'pagingcookie="'
                        )
                    ), 
                    '" '
                )
            )
        )
    )
)

First, we are retrieving the pagingcookie attribute using the combination of split, first and last functions.

Using the decodeUriComponent function to URL decode the retrieved value.

The formatted paging cookie will look something like below –

<cookie page="1">
   <createdon last="2024-11-02T12:28:22+05:30" 
     first="2024-08-24T05:20:57+05:30" />    
   <contactid last="{B46F5F88-212B-4B30-9557-F4D08EC457E3}" 
     first="{60C900AD-E600-4CBD-BD73-EB5401D878FB}" />
</cookie>

As mentioned earlier in the post, we need to use the XML-encoded value. You can use either the replace function to replace all < with <and > with > or you can use an undocumented function named encodeXmlValue in Power Automate to encode the XML. Credit to this blog that revealed this hidden gem.

encodeXmlValue(variables('PagingCookie'))

Using these methods, you can easily fetch more than 5000 records efficiently in Power Automate.