question

PramodButte-3924 avatar image
0 Votes"
PramodButte-3924 asked Jerryzy edited

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

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.

@BakerKong-MSFT

office-sharepoint-onlineoffice-js-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Jerryzy avatar image
0 Votes"
Jerryzy answered Jerryzy edited

Hi @PramodButte-3924 ,

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Hi @Jerryzy-MSFT

Thank you for your response on this questions.

for this is have tried to use people column email and created single line of text column for each people picker column and update this people picker email values with respective columns.

Also added these single line of text columns into indexed columns, then trying to call rest api with below url:

  /_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')

as TestEmail1_Email is single line of text columns. But it seems failed and getting same issue.

please suggest any things if you will find any solutions.

0 Votes 0 ·

Hi @PramodButte-3924 ,

The issue is because of multiple conditions filter using Or opeartor, it works for single indexed column filter but for multiple fields filter, it'll still throw the error, you can try to filter a single field using JavaScript code in the answer, it should be working.

For multiple fields filter, it's suggested to use SharePoint Search Rest API instead:

calling SharePoint search using REST



Thanks
Best Regards

0 Votes 0 ·