Rest - Error raised on Count items with filters in large list

Question

The solution proposed in the linked thread does not seem to work for me as you can see below..

---

I have a rather huge list (2500 items) on SharePoint 2013 and I would like to get an idea of how many items that meets selected criterias, but I'm unable to get it working like i would like.

I know the itemcount endpoint, however that only returns the total amount of items in the list and I cannot seem to filter that.

I've tried this which I have found somewhere on this site:

_api/web/lists(guid'D38E6516-FB4F-4FCF-9E29-4FEC9CE06D2B')/Items/$count?&$filter=startswith(Title,'T757388')

But that only returns an error.

If I was dealing with a small list I would just make a normal Query and Count the items in the array, but the payload size exceeds the limit if I try this.

Anybody able to cast some light on this?

Answer 1

Here's the solution to my issue (HK is the title of the list):

 /_vti_bin/ListData.svc/HK/$count?&$filter=((Updated_by_workflow eq false) and (Validated_by_manager eq false) and (Manager_Mail ne null) and (Employee_ID ne null))

Answer 2

Apologies for a late answer but I hope someone steps into my code when dealing with more than 100,000 in a SharePoint list. It's good to mention that I gat some of the code on the web(Can't recall the author's name to quote him). Then modified it to use JavaScript Prototypes to Load the methods once when the page is refreshed to cope performance issues.

Here you go Morten:

var HugeList = function() { this.altWeb = _spPageContextInfo.webAbsoluteUrl + '/' || ''; this.curWebsite = window.location.protocol + '//' + window.location.host; this.completeUrl = this.curWebsite + '/sites/thewire/' || this.altWeb; };

HugeList.prototype.replayCenter = function(listName) { var ItemCount = this.GetItemCount(this.completeUrl, listName); this.createRestUrl(this.completeUrl, ItemCount, listName); };

HugeList.prototype.GetItemCount = function(siteurl, ListName) { var ItemCount = ''; $.ajax({ url: siteurl + "/_api/web/lists/GetByTitle('" + ListName + "')/ItemCount", method: 'GET', async: true, headers: { Accept: 'application/json; odata=verbose' } }) .done(function(data) { ItemCount = data.d.ItemCount; }) .fail(function(data) { console.log(data); }); return ItemCount; };

HugeList.prototype.createRestUrl = function(siteurl, ItemCount, ListName) { // prettier-ignore var listServiceUrl = this.completeUrl+ "_api/web/lists/GetByTitle('" + ListName + "')/Items?$select=Title, BRANCH_DEPARTMENT, PHONE, COMPANY&$top=200"; //Step 3: Rest call to procerss each items of list $.when(this.processList(listServiceUrl, ItemCount)).done(function() {}); };

HugeList.prototype.processList = function(nextUrl, ItemCount, gridID) { gridID = gridID || 'table_id'; var dfd = new $.Deferred();

if (nextUrl == undefined) { dfd.resolve(); return; }

this.getJSONDataFromUrl(nextUrl).done( function(listItems) { var items = listItems.d.results; var next = listItems.d.__next || '';

  var indexAt = next.indexOf('_api');
  var resUrl = next.slice(indexAt);

  $.when(this.processList(this.completeUrl + resUrl, ItemCount)).done(
    function() {
      dfd.resolve();
    }
  );

  var tableHTML = '';
  var table = $('#' + gridID).DataTable();
  $.each(items, function(index, obj) {
    // prettier-ignore
    var phonenumber = obj.PHONE ? obj.PHONE : "-";
    // prettier-ignore
    tableHTML +="<tr><td>" + obj.Title + "</td><td>" +obj.BRANCH_DEPARTMENT 
    +"</td><td>" +phonenumber +"</td><td>" +obj.COMPANY +"</td></tr>";
  });
  table.rows.add($(tableHTML)).draw(); //Append each list row to data tabel
}.bind(this)

); // Bind to HugeList context. return dfd.promise(); };`

HugeList.prototype.getJSONDataFromUrl = function(endpoint) { return jQuery.ajax({ url: endpoint, method: 'GET', headers: { Accept: 'application/json; odata=verbose', 'Content-Type': 'application/json; odata=verbose' } }); };

var largeList = new HugeList(); largeList.replayCenter('EmployeesList');