Hi,
You raised some very good questions. When working with SharePoint libraries that contain thousands of folders or files, it is important to design the flow in a way that avoids ordering issues and the SharePoint list threshold.
1. Is there a recommended production-safe pattern to retrieve the latest folder in a SharePoint library with 5000+ items?
Yes. A production-safe approach is to let SharePoint return only the latest item instead of retrieving all folders and processing them in Power Automate. This can be done using OData sorting with Top Count in the Get files (properties only) action.
For example:
Filter Query
FSObjType eq 1
Order By
Name desc
Top Count
1
Since your folder names follow a timestamp format like yyyyMMddHHmmss, sorting by Name descending will return the newest folder directly. This avoids retrieving thousands of folders and is much safer for large libraries.
2. Is last(body(...)) reliable when no Order By is used?
No, it is not reliable. SharePoint does not guarantee the order of items returned by the Get files (properties only) action unless an Order By clause is specified. Without ordering, the result set may appear arbitrary, so using last() or first() may not consistently return the newest folder.
3. Could threshold behavior cause incomplete results even when Top Count is set?
Yes. SharePoint libraries have a list view threshold of 5000 items. Even if Top Count = 5000, SharePoint may still apply internal filtering or indexing rules. If the query is not optimized, the flow may not retrieve all items or may return inconsistent results.
Using Filter Query + Order By + Top Count ensures SharePoint performs the sorting server-side and only returns the required result.
4. What is the best practice to retrieve the latest folder and its file in large libraries?
A common and reliable pattern is:
-
Use Get files (properties only) to retrieve the latest folder
Filter Query
FSObjType eq 1
Order By
Name desc
Top Count
1
-
Use another Get files (properties only) action targeting that folder to retrieve the file inside it
Filter Query
FSObjType eq 0
Top Count
1
-
Use the returned Identifier with Get file content or any subsequent actions.
For very large libraries or more advanced scenarios, another option is to use the Microsoft Graph API to query the document library. Graph supports server-side sorting and querying, which can sometimes provide more flexibility and better performance when working with large datasets or complex folder structures.
This approach ensures that SharePoint (or Graph) handles the filtering and sorting efficiently, rather than retrieving thousands of items into the flow.
Hope this helps.
