4

Why does the first query runs ok and the second throws list view threshold error?

Note that it's a regular SP.ListTemplateType.tasksWithTimelineAndHierarchy List with Id and PercentComplete as default and indexed columns.

Note also: /_api/Web/Lists/GetByTitle('actions')/itemcount equals to 7000 items:

items.filter(x => x.PercentComplete ==1) // equals 1000
items.filter(x => x.PercentComplete < 1) // equals 6000

First Query

/_api/Web/Lists/GetByTitle('actions')/items?$filter= Id gt 4000&$top=4000

=>returns ok

Second Query

/_api/Web/Lists/GetByTitle('actions')/items?$filter= Id gt 4000 and PercentComplete lt 1&$top=4000

returns=>

{"odata.error":
   {
   "code":"-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
   "message": {"lang":"en-US",
      "value":"The attempted operation is prohibited because 
      it exceeds the list view threshold enforced by the administrator."}
    }
}
  • great, @Akhoy! gonna index the column. brb – egidiocs Mar 25 '16 at 21:53
  • @Akhoy, the PercentComplete column is a default column (from Tasks Content Type) and it's indexed by default, as is DueDate.... So, there is anothe reason... – egidiocs Mar 25 '16 at 22:30
0

As per your comment, though you have indexed the column, if the $filter query returns results more than 5000 items, it will give the error.

What you need to do is form a query where your $filter returns less than 5000

If you want more than 5000 items then REST API is not the method you can use

0

If you have list items more than 5000, or you are anticipating you might have items more than 5000 in the future then the best practice is to make rest call in more than 1 batches of 5000 starting from your top ID + 4999 and so on and stack it up in an array. I'll show you an example how I have fetched data for 15000 items (you can do as per your requirement).

var itemStorageVariable =[]; var ListName = "actions"; function stackItems() { var checkTopIdFilter = "?$select = Id&$orderby=Id asc&$top=1"; \\your filter to get the top id var callTrainingReqListCheckTopId = GetListItems(ListName , null, checkTopIdFilter); $.when(callTrainingReqListCheckTopId).then(function (tData) { if (tData.d.results.length != 0) { var startId1 = tData.d.results[0].Id; var endId1 = startId1 + 4999; var startId2 = endId1 + 1; var endId2 = startId2 + 4999; var startId3 = endId2 + 1; var endId3 = startId3 + 4999; var startId4 = endId3 + 1; var endId4 = startId4 + 4999; var trFilter1 = "?$filter=((Id ge " + startId1 + ") and (Id le " + endId1 + "))&$top=5000"; var trFilter2 = "?$filter=((Id ge " + startId2 + ") and (Id le " + endId2 + "))&$top=5000"; var trFilter3 = "?$filter=((Id ge " + startId3 + ") and (Id le " + endId3 + "))&$top=5000"; var callTrainingReqList1 = GetListItems(ListName, null, trFilter1); //rest call to get data of first 5000 var callTrainingReqList2 = GetListItems(ListName, null, trFilter2); //rest call to get data of next 5000 var callTrainingReqList3 = GetListItems(ListName, null, trFilter3); //rest call to get data of next 5000 $.when(callTrainingReqList1, callTrainingReqList2, callTrainingReqList3).then(function (trData1, trData2, trData3) { for (var i = 0; i < trData1[0].d.results.length; i++) { itemStorageVariable.push(trData1[0].d.results[i]); //pushing first 5000 data into array } for (var i = 0; i < trData2[0].d.results.length; i++) { itemStorageVariable.push(trData2[0].d.results[i]); //pushing next 5000 data into array } for (var i = 0; i < trData3[0].d.results.length; i++) { itemStorageVariable.push(trData3[0].d.results[i]); //pushing next 5000 data into array } }); } }); } function GetListItems(listName, id, filter) { var siteURL = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items" + filter; return $.ajax({ url: siteURL, async: false, method: "GET", headers: { "Accept": "application/json;odata=verbose" } }); }

Now you can call this function stackItems() wherever you want and you get all the data in itemStorageVariable. To access your data, run a loop through the length of your array and you will get each item in it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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