The scenario here is to retrieve all the connections with the Plaintiff role and update them to the Plain Plaintiff role. Power Automate flow returns only the first 5000 records in the ‘List rows’ action by default.
Since there are almost 100,000 records in my scenario.
For this, we are going to use the Pagination technique. Here are two ways to use Pagination:
1. By enabling the Pagination feature from settings, set the threshold as the number of rows you want to retrieve.
2. When using fetch XML query to retrieve rows then Pagination feature will not work. So, we must disable this. I have implemented this using the steps shown below:
Step 1: Create a cloud flow and set the trigger point. The trigger can be based on a schedule, a manual action, or an event from your data source. In my case, I’m using a manual trigger.
Step 2: Initialize the variables Count, PagingCookie, and PagingCookieOnly as strings and RawJSON as Object.
Step 3: Next, I retrieved the Plaintiff and Plain Plaintiff roles using fetchXML query and set the respective variables with their role ID retrieved.
Similarly, retrieved Plain Plaintiff role.
Step 4: Now add Do Until and set the condition to retrieve the rows until the Paging Cookie variable is empty.
Step 5: Now select action ‘List rows’ inside the Do Until block to retrieve connections using fetch XML query.
Paste the fetch XML query and add an additional Page attribute then increment this to retrieve all the pages.
Step 6: Increment the variable Count.
Expression: length(outputs('List_rows')?['body/value'])
Step 7: Set the PagingCookie variable as shown. Below is the expression given:
Expression:
if(empty(outputs('List_rows')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie']),'',decodeUriComponent(decodeUriComponent(outputs('List_rows')?['body']?['@Microsoft.Dynamics.CRM.fetchxmlpagingcookie'])))
Expression for PagingCookieOnly variable:
if(empty(variables('PagingCookie')),'',replace(substring(variables('PagingCookie'),add(indexOf(variables('PagingCookie'),'pagingcookie="'),14)),
'" istracking="False" />',''))
Step 8: Add an "Apply to Each" control to loop through each record we retrieved in the previous step. This is where we will iterate through the bulk records. Now we will update the roles in all the retrieved connections.
Step 9: I have added compose to check the number of records retrieved.
Step 10: Now save the flow and run it manually.
Here, we got all 100000 records. This is how we can retrieve and update bulk records using Power Automate.