Power Automate: Get All Records (Do-Until)

Hello Friends,

Welcome back with another post on Power Automate. In this post, we will discuss about getting all records from SharePoint list irrespective of items count. As we know, that the "Get Items" action results only 100 records and so is the "Send an HTTP request to SharePoint". In "Get Items" action, we can stretch this limit by using Top Count which itself has max limit of 5000 items irrespective of Power Automate license. If any list have more than 10000 items then how do we get all items.

The answer is using Do-Until. It is a great feature using which we can loop on SharePoint list and get all Items. All these items can be stored in an array and then can be processed. So, what are we waiting for? Let's start-

1. First of all we will create a list and add thousands of records ( 😉 ). I had created a list and added around 25000 records (using a manual flow).
2. 
3. Now, open the Power Automate maker portal and create an "Instant cloud flow" of type "Manually trigger a flow". I am using this flow for demo purpose. You may use any flow.
4. 
   
   
5. Add an action "Initialize variable", declare a variable "intItemID" of type Integer and set it to ZERO (0).
6. 
7. Click on Save to save the flow.
8. Add an action "Initialize variable", declare a variable "arrAllItems" of type Array and set it to [].
9. 
10. Click on Save to save the flow.
11. Add an action "Initialize variable", declare a variable "blnIsEmpty" of type Boolean and set it to false.
12. 
13. Now, the process starts. Add an action "Do until".
14. 
15. Add the validity condition as "blnIsEmpty" is equal to "true".
16. 
17. Now you will see a link "Change limits". Click on it. It will show 2 type of limits-
1. Count: Maximum number of recursive calls, this Do-until action will perform. Max limit is 5000
2. Timeout: It is a value which we can use to stop loop if the Loop condition is not met to avoid indefinitely running. More details can be found here.
18. 
19. Change Count to 3000.
20. 
21. Now click on "Add an action" inside this Do-until action and choose "Get Items" action.
22. Choose Site Address, List Name, Filter Query (as ID gt intItemID), Order By (ID).
23. 
24. Now add a "Select" action. It will take input from Get Items output and in Map, we will map fields according to our requirement.
25. 
26. Map ID & Title fields.
27. 
28. Next, we will make a union (join) of this output with arrAllItems using Compose action. Here we will use "union" function.
29. 
30. Now, we will assign the output of this compose action to the variable arrAllItems so that in next loop call, the updated array will be available to Compose action for union. For this, use "Set variable" action.
31. 
32. Now, we will reset the intItemId with the last record, we received from "Get Items" action. Again use "Set variable" action. To get the last item id, we will use the "last' function.
33. 
34. The last action we need to add is to set blnIsEmpty variable. For this we will check if the output of "Get Items" action is blank. If blank, then make blnIsEmpty as true. The "empty" function server this condition perfectly. Let add another action "Set variable".
35. 
36. Now, the last thing, we need to add is "Configure run after". In case, if the output of "Get Items" action is blank, the "Set intItemID" action will get failed and throw error. It will result in failure of execution of our flow. So, to avoid that scenario and terminating the flow of Do-until properly, we will update the "Configure run after" property of "Set blnIsEmpty" as "is successful" and "has failed".
37. 
38. 
39. After selecting both check boxes, click on Done.
40. That's all. Now Save the flow and execute it.
41. 
42. As we were having 25700 items and each iteration (Get Items) returns 100 records, it took 258 iterations to get the data. The 258th iteration is what where it found blnIsEmpty as true and terminated the Do-until process.
43. To verify the output, I had fetched the length of the array "arrAllItems". It comes out to be 25700 which is exactly same as in the list.
44. 
45. This way, we can get all items in Power Automate.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !