Limit Column By View or Pagination Threshold

(0) Share

Report

Posted on by AlanChen

101

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?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
AlanChen 101 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
AlanChen 101 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
AlanChen 101 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

Pstork1 68,707 Most Valuable Professional on at

Like (2)

Report

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.

Was this reply helpful? Yes No
AlanChen 101 on at

Like (0)

Report

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

Was this reply helpful? Yes No