Sharepoint get items and pagination behaviour

(1) Share

Report

Posted on by Rohit4044041

286

Hello

We have large document library with files and folders and metadata. total item count is about 44k.

We need to move some folders as per business logic as per the schrdule.

we are using get items action in power automate.

Get itemd has few filters and pagination threshold set to 100 and TOP parameter set to 50.

below is the behavior and hence confusion

1. When order by is Modified asc

We get 118 items returner

2. When order by is Modified desc

We get 100 items returned

and to make it more confusing when we add filter by hardcoding ID i.e. ID ge 30000 , we get zero results. but if we make it ID ge 35000 , we get 2037 results.

Top count is left blank and pagination set to 2000

So, what is happening here?

1. why do we get two different counts just on chage of ORDER BY

2. Why is TOP parameter not respected. (is it ignore when pagination is on?)

3. how is filter working. are items are returned first by pagination and then filtered OR filter is respected and then it gets those items from server side.

I did read number of articles but couldn't comprehend it.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

When you enable pagination the Top count becomes the page size. If you set the threshold to 100 then you should get 100 items after it retrieves them in batches of 50.

My suspicion on the 118 vs 100 is that when doing asc the modified date on items 100-118 are considered by the system to be the same. But when doing desc there are different items returned and for that set there is a difference between 100 and 101. At least that would be my theory on why it does that.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
Rohit4044041 286 on at

Like (0)

Report

But we do see different modified dates at the beginning and end of the response.

and to make it more confusing when we add filter by hardcoding ID i.e. ID ge 30000 , we get zero results. but if we make it ID ge 35000 , we get 2037 results.

Top count is left blank and pagination set to 2000

Was this reply helpful? Yes No
Suggested answer

stampcoin 5,058 Super User 2025 Season 2 on at

Like (0)

Report
Rohit4044041 Hi,

Your case symptoms sounds like the index problem. have you checked the column index properly ?

check the indexing and you may consider recreating the index, Id, Modified.

This is the first thing need to be checked.

To put it simply for you understanding:

when you trigger the get items, filter is the first to be checked against query.

The next is sort the result.

Then pagination.

If you have folders and files mixed, ordering by Modified can change which items appear first. The order matters because:
Some items (e.g., files, folders, special items) might get filtered implicitly based on indexing and internal SharePoint thresholds.
When your data set is large (44k), ordering asc vs desc means different subsets of the data might meet internal thresholds before pagination applies.
Ascending vs Descending can cause thresholds to be hit differently.
Items that fail SharePoint’s internal threshold checks won't be returned, causing count differences.

Please feel free if you get problem here.

Enjoy Power Platform and have a great day 🚀 | My LinkedIn

If the answer helps, please consider 👍, Thanks.

Was this reply helpful? Yes No
Rohit4044041 286 on at

Like (0)

Report

Thank you @stampcoin . We have turned on indexing on all columns involved in sort and filtering.

Even if I take off sorting ( order by) ,

Top count 10000. Pagination threshold set to 200

With above setting, we can expect max 200 records returned, right? However , we get 637 records returned. Is that expected?

Was this reply helpful? Yes No
stampcoin 5,058 Super User 2025 Season 2 on at

Like (0)

Report

@Rohit4044041 Hi,

I think this is the confused part here.

you should get the result (637) if match, this TOP is not like the 'TOP' in sql.

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

doe this make sense to you ?

did you figure out the sorting problem ?

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

Like (0)

Report

@Rohit4044041 If pagination is turned on then Top Count should always be a lower number than Threshold. With pagination turned on Top count becomes the page size and Threshold the maximum number of records to retrieve overall. Because top count is set to 10,000 I assume there are 637 records in the first page. It won't get a second page so the threshold doesn't apply. I agree it should limit the total to 200, but with such a large top count I can see how that might not happen.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
stampcoin 5,058 Super User 2025 Season 2 on at

Like (0)

Report
@Rohit4044041 Hi，

I was curious regarding your setting, I thought it was 1000.

If you use TOP=10000, and threshold set to 200, you should get error ( hit 5000), 200 will be ignored.

which means even you don't have the error, your result should be based on 5000.

To make it easily understand ( I hope),For example : you have a list which has 50000 Purchase order lines, and you want only those from Supplier “Contoso”—around 6000 +matches:

Top Count: 1 000

Filter Query: Supplier eq 'Contoso'

Order By: ID ASC

Pagination Threshold: 6000 (or leave at 50 000 ).

on server side, it will check the filter first, it will fetch out all matches, let's say it gets 6001,
the flow will then pull 1 000 “Contoso” orders per page ( 6 pages) and continue paging until it’s fetched all 6000, the extra 1 you miss it.

if you use 7000 as Threshold, the Page number in total is 7 ( last page only 1 record). then you will get 6001.

if you know your result will not hit threshold ( default 5000), you don't need pagination enabled, for example you know the result will be around 1000-2000, you can simply use TOP=2000, pagination not enable.

Please feel free to post your question if you get confused.

Was this reply helpful? Yes No
Rohit4044041 286 on at

Like (0)

Report
Hi Stampcoin

Thank you for your detailed response and apologies for delay in responding.

Yes, your response makes sense. If I take your example further and tweak it a bit

1. I get 119 records for below setting and If I change ID to DESC , I get 100 records.. So may be there is some bug in the connector

Top Count: 1 00

Filter Query: Supplier eq 'Contoso'

Order By: ID ASC

Pagination Threshold: 100

2. Also If I use below setting I get only 2 records. Is it becasue its only looking in first 100 records? (whic doesnt make sense because we do have about 6k contoso records)

Top Count: 1 00

Filter Query: Supplier eq 'Contoso' and ID ge 150

Order By: ID ASC

Pagination Threshold: 100

While these details might not matter but its not a giving confidence of expectd reocrds ot processed. For now, we just turned off pagination and playing with TOP setting . This way we are at least sure what to expect and avoid surprise.

Was this reply helpful? Yes No
stampcoin 5,058 Super User 2025 Season 2 on at

Like (0)

Report

@Rohit4044041 Hi,

That should be a bug if that so.

1. The result should return 100, when you specify the TOP value more than the Threshold value ( for example TOP=200), then 119.

2. The result should return 100, but the behavior seems using pagination first, and filter on each page, which is wrong I think.

I have way more data than yours, but I couldn't repeat the ones you got.

If you can make sure that the your data indexing correctly ( we are moving data from SP to blob), and you continue get the problem.

I would suggest that use HTTP get rather than the current one.

Was this reply helpful? Yes No