{Step by step} Retrieve more than 5000 records using Power Automate Pagination.

In this blog, we will learn how to retrieve more than 5000 records using Power Automate.

Requirement:

Dataverse is a powerful platform for managing data, and Power Automate is a great tool for automating business processes that involve Dataverse. However, there are some limitations to how much data can be retrieved from Dataverse at one time, by default, Dataverse limits the number of records that can be retrieved in a single query to 5000.

Implementation:

Go to https://make.powerautomate.com.
Sign into Power Apps.
Click on “Create” from the left navigation pane.
Select “Instant cloud flow.”

Give a title to your flow.
Select the “Manually trigger a flow” trigger.
Click on “Create.”

Now add some actions.

Click on “New step,” and search for “Initialize variable.”
Select “Initialize variable” and rename it.
Write “Record name” in the name.
Select “Integer” in type.
And put “0” in the value.

We are adding this “Initialize variable” to get the total record count.

Add one more variable.
Click on “New step,” and search for “Initialize variable.”
Select “Initialize variable” and rename it.
Write “Page number” in the name.
Select “Integer” in type.
And put “1” in the value.

This variable will help to send the next page number on the request.

Now create a do-until loop to run until the page Number is 0.

Add dynamic content of “Page number” in value.

Click on “Add an action.”
Search and select “Scope.”
Again, click on “Add an action.”

Scope will define your fetch criteria to retrieve the records from Dataverse.

Search for “List rows.”
Select the “List rows” action of Microsoft Dataverse.

Select an entity, here we have selected “Contacts.”
Add fetch XML query.
In the fetch Xml query, add dynamic content of the “Page Number” variable.

Add a condition.
Write this expression in condition.
- “length(outputs(‘List_rows’)?[‘body/value’])”

Here, we are adding a condition to check if the list rows length is still greater than 0.

If yes, increment the length of the Page Number.

Click on “Add an action.”
Search for “Increment variable.”
Select “Page Number” in the name option.
Add “1” in value.
Add a “Scope” action under “Increment variable.”
Now add one more “Increment variable” in “scope.”
Select “Record Count” in the name option.
Write this expression in value.
- “length(outputs(‘List_rows’)?[‘body/value’])”

If no, Set Page Number as 0.

Add “Set variable.”
Select “Page Number” in the name option.
Set “0” in value.

Add a “Compose” action.
Now add a dynamic content of “Record Count.”
Save the flow and run it.

Output:

Using Power Automate, we can retrieve more than 5000 records with Pagination, this flow runs two times, on the first run it retrieves 5000 records, and 2100 records on second run.