0

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

enter image description here

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

3 Answers 3

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

1
  • Thanks for the suggestion Ricardo :) This makes complete sense to me
    – Vivek
    Jun 11, 2021 at 8:54
1

@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?

4
  • Thanks for the detailed explanation. Things are much more clearer to me know. The main crux is to index the column else it would not work as expected correct? Now since I have close to IM items , can the columns with data be indexed at this stage and would it work post indexing?
    – Vivek
    Jun 11, 2021 at 8:53
  • Indexing the column is crucial here. I don't use Sharepoint Online too much yet (I'm on SP2013), so I don't know if you can index a column with this amount of data. On SP2013 there are some hours defined by the admin when we can do this kind of operations…
    – AymKdn
    Jun 11, 2021 at 12:48
  • Thank you for getting back. Do you have a sample javascript code to move 5K plus list items to a new list?
    – Vivek
    Jun 11, 2021 at 14:04
  • You can use the code provided by Ricardo to get all items and then transfer them to somewhere. Just be aware you'll lose the author and creation date for all items
    – AymKdn
    Jun 12, 2021 at 9:50
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.