Friday, February 21, 2020

How to use REST API call to query SharePoint list over 5000 threshold?

We have a SharePoint SPFx project that is using SPFx JS and REST API to query few large SharePoint lists with over 5000 items. We got the following error:

Microsoft.SharePoint.SPQueryThrottledException
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

The two calls for SPFx JS and REST call are listed below.

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999')

let spRecs = await web.lists.getByTitle(listName).items.filter("Publication_x0020_Record_x0020_I eq " + recordId).getAll();

After some testing, it seems like we could resolved this issue by adding the index. We looked at the two calls and both are using filter on the field "Publication_x0020_Record_x0020_I" lookup column to another list. The solution is to add the index to this field!

After index column "Publication_x0020_Record_x0020_I" , the calls will be successful. You could also add other fields to the filter as long as the first filter returns less than 5000 items. Here is the example:

https://mycompany.sharepoint.com/sites/pubs-stg/_api/web/lists/getbytitle('Journals')/Items?$select=ID&$filter=(ISDNNumber eq '0160-6999') and (Abbreviation eq 'AADE Ed J')