How to use multiple people picker fields or other fields filter in threshold larger list in rest api query?

Pramod Butte 1 Reputation point
2020-12-23T15:04:00.12+00:00

We have SharePoint online list which exceeds 5000 items threshold limit.

I need to filter above list data into using multiple people or group fields or other multiples fields like..

(TestEmail1/Email eq 'abc@xyz .com') or (TestEmail2/Email eq 'abc@xyz .com') or (TestEmail3/Email eq 'abc@xyz .com') in rest API query.

below is rest api query.

/_api/web/lists/getbytitle('abc')/items?$select=Id&$filter=(TestEmail1/Email eq 'abc@xyz.com') or (TestEmail2/Email eq 'abc@xyz.com') or (TestEmail3/Email eq 'abc@xyz.com') or (TestEmail4/Email eq 'abc@xyz.com')  

Please suggest me any solution for this issue in JavaScript.

@Baker Kong-MSFT

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,565 questions
JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
863 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Jerryzy 10,561 Reputation points
    2020-12-24T09:32:14.357+00:00

    Hi @Pramod Butte ,

    Per the test, SharePoint Online large list didn't support to filter multiple person fields even add the columns as indexed column.

    Otherwise it will throw the error "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator."

    For single person field, the workaround is to add it as indexed column and filter it with user id instead of EMail like the code snippet in attachment, you can get the user id using this endpint /_api/web/siteusers :

    51132-code.txt

    51121-snipaste-2020-12-24-17-26-39.png

    51131-snipaste-2020-12-24-17-29-52.png

    It will have a next property for items to paging:

    51113-snipaste-2020-12-24-17-30-54.png

    Thanks
    Best Regards


    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.