How to update 100000(hundred thousand) records with power automate flow by using chunk expression, Split the list and pass onto child flows.

The Dataverse connecter returns up to 5000 rows by default, We can increase this up to 100000.

To get more than 5,000 rows, turn on the Pagination and set the threshold up to 100,000 in Settings

If you have more than 100,000 rows to process? you can get more than 100,000 rows from Dataverse, use the skip token to send another request until the skip token returns empty

When we have thousand of records to update instead of doing list down the records and apply on each, I have approached this slightly different here. 

We will build one parent flow and 4 child flows. So that we can divide the whole list into 4 identical list and pass on to child flows. This approach significantly reduces the time to update the total records. 

Lets jump in and build a flows!

Based on your requirement you can choose the type of flow you want, for example purpose I am using Schedule flow. 

Since I am using the current date to filter down the records, I am initialize and format the date.

use the below expression to format the date

formatDateTime(utcNow(),'yyyy-MM-dd')

Initialize the Array length

Initialize the Total Records

Initialize the Chunk Array

List all the products due to update the price, use the fetchXML query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="product">

    <attribute name="name" />

    <attribute name="productid" />

    <attribute name="productnumber" />

    <attribute name="description" />

    <attribute name="statecode" />

    <attribute name="productstructure" />

    <attribute name="cr0b9_newprice" />

    <attribute name="cr0b9_newvalidfrom" />

        <attribute name="cr0b9_newvalidto" />

         <attribute name="ProductId" />

    <order attribute="productnumber" descending="false" />

    <filter type="and">

            <condition attribute="statecode" operator="eq" value="0" />

      <condition attribute="cr0b9_newvalidfrom" operator="on" value="2024-04-08" />

          </filter>

  </entity>

</fetch>

 

Condition- Check the array length

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

If its greater than "0"

Set Array length & Total Records

Set Array Length: div(length(outputs('list_All_Products')?['body/value']),4)

Note: I am using 4 child flows that's way I am using more, we can change this number based on your requirement.

Set Total Records: add(variables('vNewArrayLengthToBe'), sub(1,mod(variables('vNewArrayLengthToBe'), 1)))

Set Chunk Array

chunk(outputs('list_All_Products')?['body/value'], variables('vTotalRecordsTobe'))}

Now check the Chunuk Array and call the child flow in apply on each.

Condition - 1

variables('vChunkArray')?[0]

If its not equal to "null"

variables('vChunkArray')?[0]

Note: We are passing on these 4 values and updating in child flow.

Add Parallel branch condition 2

variables('vChunkArray')?[1]

Add Parallel branch condition 3

variables('vChunkArray')?[2]

Add Parallel branch condition 4

variables('vChunkArray')?[3]

Now all done test the flow.

Thank you for visiting this Blog 🙏