Answered

Sharepoint Get Items - Not Returning All Records

(0) Share

Report

Posted on by Gesbos1

Hi guys,

I have a Get Items step that is pulling data from a list in Sharepoint that has greater than 5k records. In the "Filter Query" section of Get Items, I'm querying for any records with a "Targeted End Date" greater than or equal to today. It should be pulling back 54 records, but I'm only seeing 20 records. What am I doing wrong?

All responses (8)

Answers (1)

Chris911 2 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

I've wasted hours trying to figure out why my "Get Items" was only returning 100 items.

PowerAutomate clearly states in Top Count that "Total number of entries to retrieve (default = all)" ALL. Not 100. ALL.
How does Microsoft let a mistake like that get out?
dperez13 428 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

my question is , if i have 1000 files and i do get files. if i do filter query = ID = 1. will get files still bring back 100 or 1000 files, or will it only bring the items im filtering for?

so if i have thousands of files, but if i only need 3 and i do filter query to only grab those 3, is it still going to bring back 100 in the json body?
batmanMon 164 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

@grantjenkins you are a star!
ScottShearer 25,142 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

@Gesbos1

Please remove the entry in top count. Set the threshold to no more than 5000 and try again with my suggestion above.
Nived_Nambiar 15,766 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

Hi @Gesbos1

I think it is related to license issue

Refer this post
https://powerusers.microsoft.com/t5/Building-Flows/Sharepoint-Get-Items-error-pagination-policy/td-p/1621693

Hope this helps you !
Gesbos1 8 on at

Like (0)

Report

Re: Sharepoint Get Items - Not Returning All Records

Thanks Grant. I get this error when i set Top to 5000 and pagination to 30000:
ScottShearer 25,142 on at

Like (2)

Report

Re: Sharepoint Get Items - Not Returning All Records

@Gesbos1

@grantjenkins is correct in that Get items will return just 100 items by default. The filter query filters before the records are returned to you, so while you should turn on pagination and set the threshold, that is not your issue.

Can you replace the utcNow() expression with the following and try again:

utcNow('yyyy-MM-dd')
Verified answer

grantjenkins 11,053 on at

Like (4)

Report

Re: Sharepoint Get Items - Not Returning All Records

By default, Get items only returns the first 100 items. In your case you should do two things since you have more than 5000 items.

Set Top Count to 5000. This will return the first 5000 items.

Go into Settings and turn on Pagination, then set a Threshold to something greater than the number of items you expect to have over the next few years. If you have around 6000 items in your list and expect there to be around 10000 items within the next few years, then set the Threshold to 15000 (as an example). When you set Pagination on, it will return the Top Count in batches. So if your Top Count is 5000, it will return the first 5000, then the next 5000, and so on until all items are returned or it reaches your Threshold.