Recursive function to handle list view threshold not working

Question

We have a list with 0.5 Million items.

The end goal is to get and handle threshold using recursive function. Followed the below article however didn't work as per our requirement. Rest to read more than 5000 items from document library

Code

function FilteredInvoiceNew(ColumnName,ColumnVal){
 
var dataset = [];
var FltrInv = [];
var response = response || [];
var ApprovalStatus = "";
var listName="Claimlist";
var myQuery= _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('ListName')/items?$select=ID,P_x0020_No,Inv_x0020_Date,Item_x0020_No,ProductCode,Qty,Prj_x0020_Name,
Distributor_x0020_No,Distributor_x0020_Name,Contractor_x0020_Name,Claim_x0020_Amount,YourReference,YourProductCode,YourPONumber,IsApproved,Comments";

if(ColumnName!=="" && ColumnVal!==""){
            
  myQuery+="&$filter=substringof('"+ColumnName+"',"+ColumnVal+")";      
}   

myQuery+="&$top=500";
myQuery+="&$orderby=ID desc";
 
 debugger;
 
        return $.ajax({
            url: myQuery,  
            method: "GET",  
            headers: {  
                "Accept": "application/json; odata=verbose"  
            },
            success: function(data){
                response = response.concat(data.d.results);
                if (data.d.__next) {
                    myQuery = data.d.__next;
                    FilteredInvoiceNew();
                }
              
                const lookup = response.reduce((a, e) => {
                a[e.YourReference] = ++a[e.YourReference] || 0;
                return a;
                }, {});

                FltrInv = response.filter(e => lookup[e.YourReference]);
                console.log(FltrInv);
                
                $.each(FltrInv, function(index, row) {
                    
                    // SomeCode
                    
               });
   
 },
 
 error: function(error){
 
  console.log(error);
 }
 
  });// End of Ajax Call
 
}

Executing the function goes to the error section and throws the threshold error as shown below

Also tried nested function to include the below logic and it skips the nested function.

Would be extremely grateful if anyone could let us know if this is achievable.

Thanks in advance

Answer 1

My understanding of the listview threshold is that regardless of using top query, if the first filter you are applying returns more than 5k as result you will always get the threshold error.

Note that the solution provided in the reference you posted is not including any filters, same if you check the pnpjs examples of pagged items.

Either you remove the filters and manage it via js or you will probably need to convert the columns you are filtering to a delegable type which probably won't cause this issue.

You can see the examples from pnpjs: https://pnp.github.io/pnpjs/sp/items/

// we can also use filter as a supported odata operation, but this will likely fail on large lists
const allItems: any[] = await sp.web.lists.getByTitle("BigList").items.select("Title").filter("Title eq 'Test'").getAll();
console.log(allItems.length);

I am not sure this apply to the rest api but we were able to scale a powerapp by filtering the data based on a number column. The app is accessed by 1-5k people daily and there were more than 500k records already. but follows the link for which fields you can try to filter from together with the limit/top query

https://docs.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-operations-for-sharepoint

Answer 2

@ricardo-silva provided the correct answer.

Your first filter must be INDEXED and must return LESS THAN 5,000 items. Then you can apply other filters.

The only other option is to collect the 500k items by packets of 5,000 items, and to then filter using JavaScript… But it represents 100 requests and it will take too much time.

I'd recommend creating special columns that could help filtering the list. You could "join" several columns into the same one and use it to filter.

For example, you want all items for John Doe during Q1 FY2021, but you know that John Doe has more than 5,000 items in the list across all quarters, and there are more than 5,000 items in Q1 FY2021 across all users… but there are always less than 5,000 items for both a user during a quarter. Then you can have a TEXT column with the value ̀John Doe Q1 FY2021, and after indexing it you will filter on this column. Makes sense?

Answer 3

You can also use RenderListDataAsStream function for SharePoint online. If the columns are indexed this function can return result set of more than 5000 items in single query.

Microsoft Documentation

Detail explanation blog for different approaches to read large list data