Table of contents
How to retrieve more than 5000 records with Power Automate
The list rows action from the Dataverse connector in Microsoft Power Automate has a limit 5,000 rows by default. We will see below the different ways of retrieving more :
- Use Pagination
- Use SkipToken
Everytime I have to retrieve more than 5000 records from Dataverse in Power Automate, I stuck for few minutes.
Here what cross my mind :
- 🤨 Should I launch manually few times my flow to cover the whole bunch of items ? (Ugly I admit it, but I really think about it 😋)
- Do I use the Pagination ?
- Do I use the Skip Token ?
Now let's imagine I decide to be lazy and not click 20 times to cover the whole 100000 records present in Dataverse
Here is my decision path, depending on the number of records to retrieve :
- Between 1 to 5000 -> simplest one
- Between 5000 to 100000 -> Pagination
- More than 100000 -> Skip Token
List Rows : 5000 rows limit
A quick test on the Contact entity without any filters shows it
Well in fact not really, because if I select all the columns, here is what happens :
It fails \o/
Why the buffer size error in List Rows ?
Because you exceeded the 100 MB limit
You can find how to solve this issue with Avoid buffer size error for List Rows using Power Automate Flows
Ok let's select only 1 column and go with the Pagination way of doing
How to use Pagination to retrieve more than 5000 records in Power Automate
- Open the ... of the List Rows action
- Choose Settings
- Toggle on Pagination
- Input 10000 as Threshold
You can see now that I retrieved more than 5000 records but I hit the 10000 threshold limit I defined previously. It just mean I have more than 10000 records
Let's do it again and change the Threshold to 100000 records
Fantastic, but what if I have to retrieve more than 100000 records?
WAIIIITTT, before going further, there is a limitation you should know
⛔ Problem : Pagination does not work with Fetch XML
Pagination does not work when you are using the Fetch XML query to filter
🟢 Solution : Convert your Fetch XML to OData Filter
How to use SkipToken to retrieve more than 100000 records in Power Automate
Wait, can I really have more than 100000 records in ONE entity ?
Yes you can, take a look at your activitypointer table for instance
We hit the threshold, meaning there is more than 100000 rows matching our request.
If the table has more than 100000 records, you have to use a different technique to return all records.
The steps are :
- Turn Off Pagination in List Row’s action
- Parse the odata.nextLink parameter
- Extract the Skip Token
- Loop until the odata.nextLink is empty
What is Skip Token in odata.nextLink parameter
When Pagination is off, the response from the action contains :
- a @odata.nextLink parameter
- and a batch of 5000 records :
@odata.nextLink : https://xxx.crm4.dynamics.com/api/data/v9.1/activitypointers?$select=subject&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=blablabla
By parsing @odata.nextLink and get the skip token you can use this in the next query with List rows to get the next 5000 records.
How to retrieve more than 100000 records using Skip Token
Here is the structure of the flow
- Deactivate pagination (otherwise odata.nextLink will be empty)
List Rows with skipToken Initial Query
We get the odata.nextLink with an initial query.
Initialize SkipToken variable
You initialize the SkipToken variable by extracting its value from the odata.nextLink retrieved just before
Do until SkipToken is empty
The Count parameter corresponds to the max number of iteration of your loop.
- Default value is 60, meaning you can retrieve 60*5000 records = 300000 records
- By putting 5000, you can retrieve 25000000 records (yes, 25 millions records)
The Timeout parameter is the time duration of how much your loop should run
- Format is ISO 8601
- PT1H is one-hour duration
- PT1M is one-minute duration
- The T represents the Time-designator
- P1M is one-month duration
List Rows with SkipToken
We reuse the List Rows action but this time with the SkipToken variable
Before proceeding to the action on my records, I update the variable SkipToken with the value just retrieved, meaning my SkipToken variable is decrementing now (avoiding to retrieving the same set of 5000 records again and again and again)
Actions on this iteration
Here we can put our business logic 👇
And you’re done !
If you have any questions about Power Automate, please feel free to reach out