Limit Column By View or Pagination Threshold

[AlanChen]

Hi all,

i been having this issue, we have a list in sharepoint which exceeded the threshold limit. so we create a custom view to filter each view within 2000 items using ID no.

Each custom view will have a column of returning date and email address, which if the date was today and with '@' in email. The flow will send out a email to me.

So, i make a flow to each view, which i set limit column by view. also i do pagination and threshold to the limit if items in the view.

Then there is this issue which im not sure where i done wrong. Each of the flow send me a same item of email which i tought it only send based on the setting i set on the column view.

so the question is, does the filtering work? how do i make the flow run based on the custom view only. Is the pagination and threshold need to off or any other way?

[Pstork1]

SharePoint Views in Power Automate are only used to select which columns are included.  The filtering settings of the view are ignored.  Filtering and sorting needs to be done using Odata settings in Get items.   But,If you turn on Pagination it is possible to retrieve more than 2,000 records from SharePoint.

[AlanChen]

Hi @Pstork1 

Should i off pagination and select the column view only? but how do i know it will run all item in the view? should i put a number of the top count to make sure it at least seach the 2000 item.

[Pstork1]

If you have a large list then I would recommend the following:

1) Use a SharePoint view to select which columns you want to retrieve. Fewer columns will lead to better performance so limit the columns to just the ones you need. The filtering done in the view will be ignored

2) Turn on Pagination and set the limit higher than the number of items you have in the list

3) Set Top count to something between 0-500. When pagination is turned on Top Count becomes the page size that will be retrieved.  I usually use around 100

4) Limit the rows returned using an OData Query.

[AlanChen]

Hi @Pstork1 ,

thanks for reply, i have reply as below

If you have a large list then I would recommend the following:

1) Use a SharePoint view to select which columns you want to retrieve. Fewer columns will lead to better performance so limit the columns to just the ones you need. The filtering done in the view will be ignored

Due to large list, i have multiple view created. so can I will use default as below to let it run all?

2) Turn on Pagination and set the limit higher than the number of items you have in the list

I now turned on pagination and set to 5000 threshold which is the max.

3) Set Top count to something between 0-500. When pagination is turned on Top Count becomes the page size that will be retrieved.  I usually use around 100

What does top count does, is it the total view per page? entire list view size?

4) Limit the rows returned using an OData Query.

I do not understand how to use the OData query, is it not same as query filter?

I tested the flow as i set above, i can get the result that i need. But, im not sure does the flow did scan all the items?

Because of the list have around 7800+ items in ID numbering. if i do threshold 5000, does it still scan other ID that over 5000?

Due to this, i have created below view list to filter view by ID. But you did mention filtering done in view will be ignored, does this mean my view below is not valid?

[Pstork1]

1) Views only limit the columns returned, not the rows.  If you have too many of certain types of columns, like lookups in the list, not using a view can be a problem.

2) 5,000 is not the max for pagination. I have lists larger than that with pagination set to 6,000+

3) If pagination is disabled top count is the total number of records returned. If pagination is enabled top count is the number of records in a page.

4) The query filter in Get items is an OData query Power Automate OData filter query with examples - SPGuides

If your list is 7800+ then your pagination setting should be 8.000. As I've already said.  SharePoint list views that filter the number of rows in the view are ignored by Power Automate. Power Automate only uses the views to determine the columns returned not the rows. Assuming the only thing different about your views are the filter then Power Automate will treat all of them as the same.

[AlanChen]

Hi @Pstork1 ,

I need some time to digest, but I get below result while doing some test

1. I set the view to default which to use all column (Do not limit).

2. Cannot set max pagination, so i probably maintain at 5000.

3. I did try only put 1 into pagination, I'm still able to get the result!!! so now is a bit confuse of how pagination work actually.

4. Top count still blank. (do i need put anythins?)

Correct me if my understanding below are wrong,

Limit columns by view = is to make sure the flow follow the columns that i need in the view which i created?

Pagination & Threshold = Is to run on how many row and continue on another page? if i set 1, it will run 1 row continuesly till end? if set 5000, it will run 5000 continuesly? if I have items more than maybe 70,000. it still continue to run 5000 row and continue after 5000? 

[Pstork1 · Accepted solution]

1) The view has nothing to do with how many records are retrieved

2) The error you are getting is for minimumItemsCount, that's not the pagination maximum. I have a list I use that has 5101 records in it.  I set maximum for pagination to 6000 and it works.

3) Not sure what is going on with that

4) I think your error is coming from Top Count being blank.  I think its trying to retrieve all the records since Top Count is blank and you can't retrieve more than 5,000 without doing it in pages.

[AlanChen]

Hi @Pstork1 

Possible is the cause of top count is blank. for now all my flow will set top count 100 and pagination 5000 so item list exeeded threshold will run.

Thanks for all the info @Pstork1 , it did let me figure out more on what should i do