How can I use Pagination setting in my flow with SharePoint - Get items action ?

[yoshihirok]

I want to use Pagination setting in my flow with SharePoint - Get items action.

How can I use this setting after turn on Pagination.

My test environment.

1. SharePoint list which have 5,400 items.

My test scenario.

1. Trigger: Schedule - 1 day.

2. Action: SharePoint - Get items.

  Order by = ID

  Top count = 5

  Setting - Pagination = On,

  Setting - Limit = 3

3. Apply to each: value

  3-1, Action: Microsoft Team - post a message

    Message = Expression - item().ID

Regards,

Yoshihiro Kawabata

[v-yamao-msft]

Hi Yoshihiro,

According to this blog about “More action settings and four new connectors”, we know that Pagination enables you to handle more records than are returned in a single call from a service.
https://flow.microsoft.com/en-us/blog/four-connector-action-settings/

According to your scenario, I created a simple flow to test the Settings for the action “get items”.

I configured the Top count and Setting-Limit with different value, then I found that if the limit value is smaller than the Top count, then the number of returned items would be Top count.

While if the limit value is larger than the top count, it will hit the limit that I defined in the flow.

Have you run the flow on your side? Do you get the same result with me?

Best regards,
Mabel Mao

[yoshihirok]

Hi @v-yamao-msft

thank you for your reply, and re-pro.

I want to figure out 'Top count', 'Setting - Limit', and Each Triggers/Actions.

My scenarios:

1. What does the flow need to Pagenation ?

2. What is best flow to copy 5,400 items in SharePoint List to other List ?

3. What is best flow to send messages from 5,000 items in SharePoint List to Microsoft Teams ?

For these scenarios, 

I test by Pagenation = On, 'Top Count' = 5, 'Setting - Limit' = 3.

My Image flow from 'Setting - Limit' and 'Pagenation' word.

   1st, Action 'SharePoint List - Get Rows' seems to output 3 items, from 1 to 3

   That because , 'Top Count' = 5, and 'Setting - Limit' = 3.

   and

   2nd, Action 'SharePoint List - Get Rows' seems to output 2 items, from 4 to 5.

   That because, same.

Or another image from 'Setting - Limit' and 'Pagenation' word.

   'Pagenation' works Triggers, not Actions.

   Each Trigger output smaller than 'Setting - Limit' items for each run.

   For example, For Trigger output total 5 items, and 'Setting - Limit' = 3.

   The flow run 2 times.

   1st run, Trigger output 3 items from 1 to 3.

   2nd run, Trigger output 2 items from 4 to 5.

  But, 'Pagenation' can set in Action 'SharePoint - Get Items'.

So, I need to Pagenation example flow.

Regards,

Yoshihiro Kawabata

[yayala]

Hi @yoshihirok

Were you able of copying large list to another sharepoint list? I have the same scenario and my flow is limited to copy 100 items.

Thanks in advance!

[yoshihirok]

Hi @yayala

Yes, I have 100s, 1000s items in my SharePoint list.

Or, A Trigger  got over 100s items each arrived like very hot twitter keyword

And, I hope get over 100s items to do something

Regards,

Yoshihiro Kawabata

[yayala]

Thanks @yoshihirok!

I enabled the pagination and my flow works.

[MihirY]

But what is the maximum limit that we can give for pagination. I know in SP online there cannot be more than 5K items per view. But I can store more than 1 Lakh records in list. If I give limit as 1Lakh will it be working?

[beckemzj]

This video should be helpful for those of you trying to use Power Automate for looping through a large number of records: https://youtu.be/5NtzcfmSGes

[DeeTronSEAM]

Here are two other resources I found helpful when dealing with this:

https://alextofan.com/2019/08/22/how-to-get-more-than-5000-item-from-sharepoint-online-in-flow/

https://www.tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/

One thing I would add to any discussion about dealing with large datasets in flow is that you have to consider all the throttle and limitation points appropriate for your Flow licensing.  E.g.:

https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-limits-and-config?tabs=azure-portal  

https://docs.microsoft.com/en-us/power-automate/limits-and-config 

With the solution I am currently working on, I'm in a basic (i.e. "Low") license enviro. I originally had to deal with the +5000 records pagination issue, but then surfaced throttling and loop limits as I attempted to update a SQL Azure Database record by record.  The timeouts from the throttling caused cascading problems, and the loop limits required Flow mods to chunk the larger dataset (~12,000 files/records).

It's been a real learning experience and I couldn't have figured things out without the blog posts above.  Some of this might be better dealt with by just licensing the Flow directly (Per Flow license gets the most generous limits, currently), but by having a better understanding of this, I know I will be changing the design of all of my flows going forward so they are scalable regardless of what happens to the underlying dataset sizes.