1

If I have a list that contains, for example, 3,000,000 items.

If I apply the following settings in the Get items action:

Top Count:  `5000`

Settings > Pagination > Threshold:  `100000`  

Odata filter:  `ExpiryDate ne null and ExpiryDate eq '2022-09-08'` 
    
(this filters the list to entries where the ExpiryDate column value is not null AND is equal to todays date)   

Which of the following behaviours will occur:

A) Get items will search the whole list of 3,000,000 items and return a maximum of 100,000 results

OR

B) Get items will only search 100,000 items out of the 3,000,000 items in the list?
(and therefore not do a full search of the list)

OR

C) Something else?

What I am really trying to clarify is:

Does the Pagination Threshold setting in the Get items action relate to how many items can be returned or how many items can be searched?

It would also be good to know:

If I set Pagination > Threshold to 100,000, what should I set the Top Count value to and why?

Edit:

I don't understand the relationship between Top Count and Pagination Threshold.

The official docs state:

To specify the number of items to be returned, expand the Advanced options, and specify the number in the Top Count action property.

However, the following test proves that statement to be wrong:

List Items:  3,600 
Pagination Threshold:  5,000 
Top Count:  500

A Get items action with the above configuration will return ALL items in the list (i.e. 3,600).

Related Reading:

In-depth analysis into Get items and Get files SharePoint actions for flows in Power Automate

How to get more than 5000 items from SharePoint Online in Flow

More action settings and four new connectors

Limits for automated, scheduled, and instant flows

Working with Large Data in Power Automate through Pagination (Video)

1 Answer 1

1

To confirm, the Get items action you are describing is "filtering" a data set rather than "searching". Searching implies it is using the SharePoint Search service which it is not.

Which of the following behaviours will occur:

Get items will filter the whole list of 3,000,000 items and return a maximum of 100,000 results

The column "ExpiryDate" would need to be indexed in order for the filter query to work e.g. Power Automate is dealing with the same list view threshold, since it is likely just using the SharePoint REST API.

Supposing that "ExpiryDate" is indexed, then the entire list will be filtered using this indexed column. The pagination value defined in Power Automate will limit your query to whatever you have defined e.g. 100k, meaning only 100k would be returned despite the whole list being filtered.

It's worth noting that in this article, Microsoft acknowledge a limitation with the Get items action:

"While using Get items on lists with more than 5000 items with a filter query, you may observe that no records are returned if there are no items matching the filter query in the first 5000 items. To fix this, enable Pagination on Get items from the action settings menu when working with lists with more than 5000 items."

Does the Pagination Threshold setting in the Get items action relate to how many items can be returned or how many items can be filtered?

The pagination limit relates to how many items will be returned. All items are filtered, since it's defined in your query. Microsoft describe the pagination setting as follows:

"Pagination enables you to handle more records than are returned in a single call from a service. For example, some services only return back 50 items at a time. However, in the flow, you may want to get all of the items in the list. By turning on pagination, the flow engine will continue to call the service until it has all of the items – or – hits the limit that you explicitly define in the flow."

What is the relationship between Top Count and Pagination Threshold?

There is no relationship, if you define a value in the top count then you will only receive that amount of items back from the flow. This is described here:

In-depth analysis into Get items and Get files SharePoint actions for flows in Power Automate

8
  • Could you please answer the specific questions in the original post: 1) Which behavior will occur? (A, B or C) 2) Does the Pagination Threshold setting in the Get items action relate to how many items can be returned or how many items can be searched? 3) What is the relationship between Top Count and Pagination Threshold? If you could provide links to support this information that would be great. Sep 15, 2022 at 5:32
  • I have updated the answer. Sep 15, 2022 at 8:35
  • For reference, I had read somewhere (I cannot find the link at the moment), that the relationship between Top Count and Pagination > Threshold was that it would return 'batches' of the number defined in Top Count until it reached the amount defined in Pagination > Threshold. So, in another list, separate to the one in this post, I set Top Count to 500 and Pagination Threshold to 5000. The list contains 3,660 items, and the Get items with that configuration is returning ALL of the items. So there seems to be some sort of relationship between those two settings. Sep 16, 2022 at 12:14
  • This is not the case, the top count refers to the $top parameter passed in the SharePoint REST API call which can be referred to here: docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/… it's also referred to in Microsoft's Get items article: "To specify the number of items to be returned, expand the Advanced options, and specify the number in the Top Count action property." Sep 16, 2022 at 13:13
  • If you can provide documentation from Microsoft, defining a technical relationship between the two then please do provide this (but all other documentation contradicts this notion). Is there anything missing from the answer? If not, please do upvote or mark this as the accepted answer to help inform the community. Sep 16, 2022 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.