1

I have a SharePoint List with over 5000 records created by me. The below query when using ID in filter works. But when I Filter With CreatedBy me it gives me List View Threshold Error.

Error:

    _api/Web/Lists/GetByTitle('TestRecords')/Items?$select=ID,Title&$filter=Author/EMail eq 
    '[email protected]'

Works:

    _api/Web/Lists/GetByTitle('TestRecords')/Items?$select=ID,Title&$filter=ID gt '5'

Both the above query returns more than 5000 records. But when using ID it works and gives 100 records. When filtering with Author it gives threshold error. I even indexed the CreatedBy Column and it still gives Threshold error. Why does it not return only 100 records?

2 Answers 2

0

SharePoint rest api limit result to 100 by default, you can’t return more than 5000 items once even you query based on indexed column. If your result of "Author/EMail eq '[email protected]'" over 5k , It will give Threshold Error even with index. We can use “$top” to retrieve more than 100 items but up to 5000 like following sample

/_api/web/lists/getbytitle('myList')/items?$top=1000

If you want to retrieve items more than 5000,Please refer to following code

 var url = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('DocumentList')/items?$top=1000";
    var response = response || [];  // this variable is used for storing list items
    function GetListItems(){
        $.ajax({
            url: url,  
            method: "GET",  
            headers: {  
                "Accept": "application/json; odata=verbose"  
            },
            success: function(data){
                response = response.concat(data.d.results);
                if (data.d.__next) {
                    url = data.d.__next;
                    GetListItems();
                }
            },
            error: function(error){
                   // error handler code goes here
            }
        });
    }
1
  • Hi Ray - Thank you for the answer. The result of "Author/EMail eq '[email protected]'" is not more than 5000. It is just 5 records. One thing noticed is the query worked when the list had less than 5000 items. As soon as it crossed the threshold error was received for the same query which just returns 5 records. Also CSOM cannot be used. Need to find a solution without any code. Oct 18, 2021 at 9:02
0

In order to successfully filter by Author properties you have to request expansion of those properties in the URL.

items?$select=Id,Title,Author/Id,Author/Email&$expand=Author/Id&$filter=Author/EMail eq '[email protected]'

&$expand=Author/Id is the "trick".

I think when you do not expand Author properties SharePoint act like filter is not applied and trying to retrieve all items it falls on the List View Threshold Error.

Your Answer

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

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