Indexed column throws threshold error while filtering based on range

Question

We have a list with over 1M items. The end goal is to filter the column InvDate based on date range.

The InvDate is an indexed single line of text column , However it throws the List View Threshold error while getting the item using RestAPI

Code

function FilterDate()
{
  //var initialUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ClaimListNew')/Items?$select=ID,*";
  
var sdate = $('#sdate').val();
var edate = $('#edate').val();


var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*";
myQuery+="&$filter=InvDate ge '" +sdate+ "' and InvDate le '" +edate+ "'";
myQuery+="&$top=4999";
myQuery+="&$orderby=ID desc";


// start the process
var getAllItemsRequest = GetAllItems(myQuery);

// wait for it all to complete
getAllItemsRequest.done(function (allItems) {

console.log(allItems);
   
  
    
}).fail(function (error) {
    console.warn("Error getting all items");
    console.warn(JSON.stringify(error));
});
}

Function to get all the list items

function GetAllItems(requestUrl) {
    var deferred = $.Deferred();
    $.ajax({
        url: requestUrl,
        method: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        }
    }).done(function (thisResult) {
        if (thisResult.d.__next) {
            // if the __next property is present,
            // that measn that there's more to get,
            // so don't start resolving things yet,
            // just make the next request
            var nextRequest = GetAllItems(thisResult.d.__next);
            
            // since GetAllItems returns a promise,
            // we have to wait for that promise to resolve
            // before resolving the promise we made 
            // at the beginning of this function
            nextRequest.done(function(nextResult) {
                // nextResult should be a plain array of items,
                // so we need to combine that with the array
                // of items we got in this call in thisResult.d.results
                var combinedResults = thisResult.d.results.concat(nextResult);
                
                // then resolve the combined results back up to the next level
                deferred.resolve(combinedResults);

            }).fail(function(error) {
                deferred.reject(error);
            });
        } else {
            // __next doesn't exist, so
            // there's nothing more to get, so
            // start resolving the nested promises
            deferred.resolve(thisResult.d.results);
        }
    }).fail(function (result) {
        deferred.reject(result);
    });
    return deferred.promise();
};

The above function has retrieved all 1 Million items without any filters as below

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('ClaimlistNew')/items?$select=ID,*";

When we use query with the below filter , we get the threshold error

var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*"; myQuery+="&$filter=InvDate ge '" +sdate+ "' and InvDate le '" +edate+ "'"; myQuery+="&$top=4999"; myQuery+="&$orderby=ID desc";

The end goal is to filter the InvDate column with the date range.

Would be grateful if anyone could let me know how this can be achieved.

Answer 1

As the column type is text so Greater than and Less than condition will not work so you should first convert the column type to Date or DateTime.

Then you need to check below.

1. The date should be in proper format. you can pass ISOString format date
2. & should be used as &

so the FilterDate function should look like below

function FilterDate()
{
  //var initialUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('ClaimListNew')/Items?$select=ID,*";
  
var sdate = new Date($('#sdate').val()).toISOString();
var edate = new Date($('#edate').val()).toISOString();


var myQuery= _spPageContextInfo.webAbsoluteUrl +"/_api/web/lists/getbytitle('MyList')/items?$select=ID*";
myQuery+="&$filter=(InvDate ge DateTime'"+sdate+"') and (InvDate le DateTime'"+edate+"'");
myQuery+="&$top=4999";
myQuery+="&$orderby=ID desc";


// start the process
var getAllItemsRequest = GetAllItems(myQuery);

// wait for it all to complete
getAllItemsRequest.done(function (allItems) {

console.log(allItems);
   
  
    
}).fail(function (error) {
    console.warn("Error getting all items");
    console.warn(JSON.stringify(error));
});
}