Power Automate: Retrieve over 5000 records from dataverse or dynamics 365 CE/CRM and Create CSV file

In this post, I will explain retrieving more than 5000 records or large sets from Dataverse and creating a CSV file Using FetchXml, Paging Cookie.

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#. We will use the Array variable to collect the outputs after each iteration to create a CSV file.

Overall Flow/Power Automate

Create/Initialize Variables

  1. Final Output Array – To store the records on each iteration
  2. Total Record Count – To know the total record count (For Analysis purposes only)
    • Type: Integer
  3. Page Number – To send the following (Next) page number on the request
    • Type: Integer
  4. 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
  5. Paging Cookie – Modified version of the original paging cookie, which will be sent as a request
    • Type: String
  6. More Records – Dataverse returns this flag as part of the response. This is used to determine whether to break the loop.
    • Type: Boolean
  7. 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. This loop continues by default until the More Records flags are false.

List Accounts

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

Join current output with existing output/records.

union(variables(‘Final Output’),outputs(‘List_Accounts’)?[‘body/value’])

Set the array variable with the above-combined output. 

@outputs(‘Compose_Existing_with_Current’)

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 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’])

Create CSV File

  1. Add an action called “Create CSV table” and set the From field to the “Final Output” variable like below.
  2. Add another action, “Create file,” from Sharepoint and set the Site Address, Folder Path, File Name, and File content.

  • File Name : concat(‘Accounts_’,utcNow(),’.csv’)
  • File Content: The body of Create CSV table

Output CSV File

The CSV file has been created successfully.

Conclusion

Using Power Automate, We can retrieve over 5000 records using Paging Cookie and create a CSV file. This flow runs three times in this example, and the total count is 10142 records.

Power Automate Zip file

Find the power automate flow as a Zip file from the GIT repository

https://github.com/AntonyStalin/GetOver5000RecordsAndCreateCSVFile

9 comments

  1. Hi

    I would like to do pagination on dataverse table. I have next and previous button. I have pagination value in drop down. based on the button click data should load in gallery. No of records load in gallery is equal to pagination selected value. how to acheive this. In API we use d.next for records more than 5k. But in my case i set a value in pagination dropdown and that count numbers should render based on button click. Could you please help me

  2. Hi
    I’m quite new with Power Automate and I have problems when try to copy 2 sentences regarding the Paging Cookie:
    When I try to generate the sentence “The fetch statement has a paging cookie”. and when I try to set the The paging Cookie variable.
    By pasting the sentences, it gives me errors, I think that this is due to the quotes and double quotes
    I tried then to upload the ZIP Filie from the Github, but it gives me errors and can’t upload it. Could you please help me by writing the sentences in another format to get it directly into the Expressions ?

    1. Hi Jordi,

      1. Verify the JSON for Xml Paring Variable. Keep the value as it is like { “a”:””}

      2. I added another file to GitHub. Please download the latest one and reference it. Let me know if you can continue. I can set up an MS team meeting if needed. Please let me know.

  3. im getting invalid XML… its converting the signs into < or >
    I tried doing a replace but then it says the split number needs to be less then 2.. when I change it to a 1 I get invalid XML again

    with this: ‘>

Leave a Reply

Your email address will not be published. Required fields are marked *