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 🙏

Comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. Can you provide the Expression used to update the Child flow

    ReplyDelete

Post a Comment

Popular posts from this blog

How to keep flows remain on if the flows don't run for 90 days

D365 CRM-Change Entity translations(Sub area) in Modern driven app.