Two things to remember:
The maximum number of items you can get in one go using the ‘Get items’ action is 5000.
The default entry in the ‘Top Count’ field for ‘Get items’ gives you the impression it automatically is set to the top limit of 5000. This is incorrect as it will only get 100 items. You have to set the Top Count to 5000 manually.
So how do we get more than 5000?
Firstly, you need to declare some variables using the ‘Initalize variable’ action:
arrItems – an array to store data from your large SharePoint List:
intID – an Integer variable to store the ID of the last collected record from your large SharePoint List:
boolEmpty – A Boolean that controls the do until loop based on the response from your large SharePoint List. Use the ‘false’ expression for the Value:
Next you need to create a ‘Do until’ loop and set it to stop when boolEmpty is equal to true:
We can then use the ‘Get items’ action and set the Top Count to 5000. This will then get batches of 5000 items at a time until all items have been retrieved.
You should also add a ‘Filter Query’ to get item IDs above 0:
To speed up this flow and only retrieve the metadata that you need you can use the ‘Select’ action as a filter:
You now need to join the output of the ‘Select’ action to the array items. This can be done with the ‘Compose’ action and the following expression:
union(variables('arrItems'), body('Select'))
Now use the ‘Set variable’ action to set arrItems to the outputs of the compose action:
Then use another ‘Set variable’ action to set intID to get the last item collected in the ‘Get items’ action with this expression:
last(body('Get_items')?['value']).ID
Finally, set the value of boolEmpty using another ‘Set variable’ action with the following expression:
empty(body('Get_items')?['value'])
You need to set the ‘Configure run after’ to continue the flow if that Set Variable action succeeds or fails. This is done by selecting the ellipses on the ‘Set variable 3’ action, select ‘Configure run after’, then make sure ‘has failed’ is ticked.
This is because on the last attempt to retrieve items we are expecting there not to be any:
arrItems will now contain every item in your list, and you can then add further actions using that data.
Comments