1

I'm working on a SharePoint Online and try to do some filters using REST API but I'm currently facing an issue on my list above 5000 items.

I need to expand multiple fields, all of them are indexed and everything is working fine until I try to filter on a Datetime that is also indexed

For example:

This is working fine:

/_api/web/lists/getByTitle('Entry')/items?$expand=Collaborator, User, Author&$select=* , Author/EMail, Author/Title,  Collaborator/Title, Collaborator/EMail, User/Title, User/EMail

But this won't:

/_api/web/lists/getByTitle('Entry')/items?$expand=Collaborator, User, Author&$select=* , Author/EMail, Author/Title,  Collaborator/Title, Collaborator/EMail, User/Title, User/EMail&$filter=EntryDate ge datetime'2022-07-16T22:00:00.723Z' and EntryDate le datetime'2022-10-17T21:59:59.723Z'

All columns used are properly indexed but I face the threshold issue and I cannot filter using those columns.

2
  • What is the error message? how many items are there between the date range you provided? Oct 17, 2022 at 10:20
  • The error message is the classic "The attempted operation is prohibited because it exceeds the list view threshold." In the date range there is more than 5000 items
    – Arnaud
    Oct 17, 2022 at 10:22

1 Answer 1

0

This is a known limitation in SharePoint. You cannot retrieve more than 5000 items if filtered query returns more than 5000 items, even after applying indexing on the column used in filtering.

In case you want to use multiple filter conditions, the first filter condition must be the strongest one and in order to apply remaining ones, it must return less than 5000 items.


For more information, check below links:

  1. Recursive function to handle list view threshold not working
  2. Filtering large list (more than 5K items) using two columns with REST API
  3. List view threshold exceeded for unfiltered list retrieved by REST API
2
  • 1
    Thank you for your help, I will do some changes to my application then
    – Arnaud
    Oct 17, 2022 at 11:05
  • You're welcome! Oct 17, 2022 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.