In this post, I will explain how to retrieve over 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 a 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
-
- Total Record Count – To know the total record count (For Analysis purposes only)
- Type: Integer
- Page Number – To send the next page number on the request
- Type: Integer
- 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
- Paging Cookie – Modified version of the original paging cookie, which will be sent as a request
- Type: String
- More Records – Dataverse returns this flag as part of the response. This is used to determine to break the loop.
- Type: Boolean
- JSON For XML Parsing – This is just a template that used to transform the paging cookie to XML
- Type: Object
- Total Record Count – To know the total record count (For Analysis purposes only)
Main Operation
We are using Do until control. This loop starts and continues by default until the More Records flags are false.
List Accounts
We are using Fetchxml query with page number and Paging Cookie. For Page 1, the Paging cookie will be empty.
The 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 variables, the Page Number just increments 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’])))
The paging Cookie variable extracts only the information needed to send 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 breaks 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 over 5000 records using Paging Cookie and More Records Flag. This flow runs three times in this example, and the total count is 10139 records.
How do you take the results from this and create a csv that includes all the items instead of multiple csv’s?
Hi Adam,
I created a separate blog for your question. This has all steps and the Power to automate flow to create a single CSV file. Please refer blog: https://learntoilluminate.com/2023/01/power-automate-retrieve-over-5000-records-from-dataverse-or-dynamics-365-ce-crm-and-create-csv-file/
Hi Stalin,
I have to do pagination using Dataverse table it has 15K records. But based on the button click in powerapps i will load 25(Value set in Dropdown) records to gallery through respond to powerapps. I will do sorting and filtering those respond should come to powerapps through automate. How to acheive this. I tried skiptoken but after 5k only its applying but i need to restrict only 25 records after button click next set of records should receive from automate. Could you please help me
Dataverse has a 5k limit return by default.
You can override this by setting the Pagination on for your Dataverse List and setting a sufficiently high threshold (like 100000).
Stephen I am aware of the Pagination solution and it worked great for one Dataverse database in my test team but the exact same code/flow running against a second Dataverse database in my live team seems to ignore the setting. I even did more testing and found with no changes to settings (i.e. leaving it at default pagination) the test team returned 5,000 records where the live team only returns 250 records. Any idea why that would be?
Hi Nagarajan
I created a separate blog for your request, which is paging using Power Automate with Dataverse.
Blog: https://learntoilluminate.com/2023/01/powerapps-pagination-when-using-dataverse/
Let me know if any questions.
How to do it if that is from a SharePoint Document Library?
We can use “HTTP” action with the following details:
Method: GET
Code:‘)/items?$top=5000
_api/web/lists/getbytitle(‘
Dear Stephen
What if the database has more than 100k records, say 105001. Will this flow run 22 times (21 times x 5k records + 1 time x 1 record)?
Sorry, I mistook the name. My question is to Stalin.