cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcbaec
Frequent Visitor

+5000 files filter query using Get Files (Properties Only)

I have been banging my head against the screen for the last week..

 

I have one library with 45k files.

I want to update metadata across the library. Since with the Get function we reach the 5,000 limit threshold I want to use a DoUntil loop and apply filter by the last item/file ID. So far so good. I tried with a list with over 20k items and it worked well.

When I tried with the library, no matter what I do and what filter I apply to it, I cannot get more than the first 5000 files of that library. If I use the Filter Query 'ID gt 6000' it returns me an empty array and I know that the library has 45k files!

mcbaec_1-1619547391018.png

For simplicity I then create an HTML table on those outputs and result is as follows...

mcbaec_2-1619547507496.png

If I set the filter query say to 'ID gt 100' and the TopCount=100 than I get the result as expected.

 

This is probably a limitation of working with Sharepoint Libraries? is there any work around? or shall I move the 45k files to another location e.g. OneDrive?

 

Thanks

3 REPLIES 3
rrovira
Microsoft
Microsoft

Hello, @mcbaec

 

Yes, the documentation says that if you exceed the threshold limit Power Automate will not run properly and will prompt an error message. (I'll paste the link to the documentation here: In-depth analysis into 'Get items' and 'Get files' SharePoint actions for flows in Power Automate | ...). I have one question, do you have a premium plan? If you do, you can increase that limit up to 100.000 items (runs a little bit slower though).

 

Here's another solution (Do Until Method): How to get more than 5000 items from SharePoint Online in Flow - Alex Tofan's blog where they explain how to achieve this without a premium plan! 

 

Hope I could help!

 

Cheers, 

Rodrigo! 

mcbaec
Frequent Visitor

Thanks for your reply. So as I understand for libraries we basically need a premium license. I tried the DoUntil method but it just does not work. It won't get passed the 5000 items. when ID>5000 it returns and empty array and then the DoUntil ends because the array is empty although the reality is there are still 40k files to go.
Now, even if I go for a premium license, 100k files is still not a lot. I managed to store 45k files in less than 1 year so if I upgrade to a Premium I will still be facing problems next year. Is there another way to manage and update files in SP libraries or do I just have to face the fact and re-think my strategy so that I never have more than 100k in a document library?

thanks

rrovira
Microsoft
Microsoft

Well, the documentation does tell us that in order to process more items we could trigger multiple flows over the same data (this could be one idea). Another recommendation could be to separate the data into different libraries and create different flows, but that really depends on what you need, but if the amount of data is projected to grow in the next couple of year or months, then it would really be a problem because of the threshold limit. Unless I'm overseeing something, in which case I would appreciate if someone had any other piece of advice! 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,649)