Скачать или смотреть Resolving OData Query Limitations in Microsoft Flow for SharePoint Lists with 20,000+ Rows

Discover how to effectively handle `OData query` limitations in Microsoft Flow when working with large SharePoint lists, including solutions for filtering by person type columns.
---
This video is based on the question https://stackoverflow.com/q/74286592/ asked by the user 'Ganesh Narayan' ( https://stackoverflow.com/u/17655891/ ) and on the answer https://stackoverflow.com/a/74287136/ provided by the user 'Ganesh Sanap - MVP' ( https://stackoverflow.com/u/8624276/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Problem with OData query in Microsoft Flow for GetItems action on a SharePoint list having 20000+ rows

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding OData Query Limitations in Microsoft Flow for SharePoint

Working with large datasets in SharePoint can pose challenges, especially when using Microsoft Flow (now known as Power Automate) to retrieve records. If you've ever tried to filter records based on a person type column using an OData query, you might have faced issues, particularly with lists containing more than 20,000 rows. This post will explain the problem and provide effective solutions to handle the situation.

The Problem

Imagine you've created a Microsoft Flow to extract records from a SharePoint list containing over 22,300 items. You want to filter based on an email address using your OData query, such as:

[[See Video to Reveal this Text or Code Snippet]]

However, the query returns no records, even though a similar query with an ID retrieves the expected result. It's perplexing and may lead you to think there are limitations related to retrieving records greater than 20,000.

Why Does This Happen?

Based on Microsoft SharePoint's limitations:

When querying a person or group column, if the number of items exceeds 5,000, the filter condition may fail. In your specific case, it might be due to duplicate entries or multiple representations of '[email protected]'.

On the contrary, other queries, such as filtering by ID or another email, may work perfectly because they fall below this threshold or are indexed properly.

Solutions to Overcome OData Query Limitations

The good news is that there are several strategies to retrieve items effectively, even from large lists. Here are some practical steps you can take:

1. Add Indexing to the Person/Group Column

Indexing speeds up the retrieval process for columns in SharePoint, especially when dealing with large datasets. By creating an index on the Person/Group column, you enhance the query performance significantly.

2. Set the Top Count in Get Items Action

Go to your Get Items action in Power Automate and set the Top Count to a maximum of 5000. This allows the action to fetch up to 5,000 records at a time efficiently.

3. Enable Pagination

Enabling pagination in the settings of the Get Items action is crucial for large lists. By doing this, you can specify how many items you want to pull in total (up to 100,000 items) and determine how many items are fetched each time.

4. Consider Alternatives to Direct Queries

If direct OData filtering continues to produce failures, consider using an alternative approach. For instance, you might retrieve all items or a batch of items first, and then use an apply-to-each construct to filter via a condition within the Flow.

Conclusion

Understanding how to manage OData query limitations in SharePoint can significantly enhance your experience with Microsoft Flow. The strategies outlined above can help you efficiently handle large datasets and keep your automation processes running smoothly. Whether it's through indexing, adjusting the top count, enabling pagination, or modifying your approach, there's a solution for every scenario.

By implementing these solutions, you will be better equipped to retrieve the desired items from your SharePoint list without running into limitations that can hinder productivity.