Cant get List items when list item count gets over 5000

Question

As the title says, I am using pnp js in a SharePoint online list:

here is my code:

 let list = sp.web.lists.getByTitle("Resource Projection");
 let query = `Quarter_x0020_Num eq ${currentQ} and Period_x0020_Year eq ${year}`;
 console.log(query);
 list.items.filter(query).getAll().then((items) => { /removed }

Here is an error:

Uncaught (in promise) ProcessHttpClientResponseException: Error making HttpClient request in queryable: [500] 
at new t (https://cdnjs.cloudflare.com/ajax/libs/pnp-pnpjs/1.1.4/pnpjs.es5.umd.bundle.min.js:42:2838)
at https://cdnjs.cloudflare.com/ajax/libs/pnp-pnpjs/1.1.4/pnpjs.es5.umd.bundle.min.js:42:3679

The code works if i delete items from the list. But when in gets populated it throws that error.

Please help.

Answer 1

In SharePoint REST AJAX call, we used to use data.d.__next to fetch more than 5000 items by calling same function recursively.

Reference: Rest to read more than 5000 items from document library

Solution For You:

1. First of all add index on columns you are using for filter: Add an index to a SharePoint column.

2. Use getPaged method added in PnP JS to fetch the multiple items at a time, something like given below:

pnp.sp.web.lists.getByTitle("Config3").items.orderBy("Title").top(2).getPaged().then(d => {
    show(d);
    d.getNext().then(d => show(d));
});

Reference: Add code to avert 5000 list item limit.

Answer 2

It seems you have crossed the List item threshold of 5000 items. Below are pointers to follow when you want to query a list with more than 5000 items.

• Make sure your query must return below 5000 items.
• Filters columns used in Query should be indexed.
• This columns has to be indexed before list items has crossed this 5000 limits.
• If it has already crossed, you have 2 options to create indexes.
  1. Temporary increase the threshold limit to more than items counts in list, index the columns which are required in filters and then change the threshold limit to 5000 again
  2. Delete some items from list so that it is below 5000, index the required columns and go to recycle bin and restore the deleted items again
• Try to change the order of where clause like - Put your Period_x0020_Year eq ${year} first and then Quarter_x0020_Num. Please note It will only work if you year column value's is having matching items less than 5000.
• Get the data in batches/paging technique - Use row limit and Query options and ListItemCollectionPositionNext attribute to get position. You can refer to this link

There are some other ways also to query with more than 5000 items, You can find it here.