1

I've got a SharePoint site that I'm running some Rest API queries on and filtering results. I was just wondering if anyone had a quick tip on if its possible to pull a count from the following code snippit from a datatable

rowCallback: function(row, data){
if (data["RegistrationDate"] <= expiration.toISOString()){
    alert (the number of items that come back from this filter?);
    $('td:eq(6)', row).css('background-color', 'red');
}
else{
}

2 Answers 2

1

Without seeing the rest of your code, based on just that little snippet there, could you not initialize a counter variable somewhere outside of that function, and then inside that if statement (where you have your alert line), increment the counter?

There's probably a better way to do it wherever you get your REST responses back, but without seeing the rest of your code it's hard to say.


To address your comment - you're saying you're looking for a way "to filter the count inside the if statement", but I think that's the wrong way to look at it. The if statement in your code snippet is inside the DataTables rowCallback function, which fires once for every row. So at that point in the code, the data object really only represents one SharePoint list item (I'm assuming you are retrieving list items through a REST call to SharePoint and then passing those results to DataTables). So you can't really "filter" per se at that point in code, because you don't have access to all the list items, you only have access to the one list item that's being put in that particular row. That's why I suggested a counter, because the rowCallback function gets fired once for every item. So it would be something like this:

// before you create the table, initialize a counter variable
var counter = 0;

// create the table, and the rowCallback will get fired once for every list item
$('#example').dataTable({
    "rowCallback": function (row, data) {
        if (data["RegistrationDate"] <= expiration.toISOString()) {
            $('td:eq(6)', row).css('background-color', 'red');

            // increment the count by one
            counter++;
        }
        else {
        }
    }
});

The better way to do it would be to filter the results you get back from SharePoint before you create the table. I can give you an example, but you will have to interpret how to work that into the existing code you have, without seeing your other code I can't really structure my example in a way that would naturally fit what you already have. But see if this sparks any ideas:

$.ajax({
    url: your-url,
    method: 'GET',
    headers: {
        "accept": "application/json;odata=verbose"
    }
}).done(function(data) {
    // now you have your results
    var filteredResults = data.d.results.filter(function(item) {
        return item["RegistrationDate"] <= expiration.toISOString();
    });

    // now you have your count, before you make your table
    var count = filteredResults.length;

    // go ahead and make your table
    $('#example').dataTable({
        "rowCallback": function (row, data) {
            if (data["RegistrationDate"] <= expiration.toISOString()) {
                $('td:eq(6)', row).css('background-color', 'red');
            }
            else {
            }
        }
    });
});
4
  • I suppose I could run a completely separate ajax query and build the count there, That was one of the solutions I ran with but it makes for ugly code, i was just wondering if there was a quick easy way to filter the count inside the if statement and pass that variable up to my table. Not a big deal, was just wondering if there was cleaner coding. Sorry for the lack of code, I was hoping what i posted was sufficient to get the point across. The network I'm writing all this on is a closed network and its hard to pass it back and forth, not as easy as a copy/paste to post the full code.
    – Chuck.M
    Aug 16, 2018 at 13:29
  • @Chuck.M updated my answer. Aug 16, 2018 at 14:40
  • That did it. When you mentioned running it outside the function i didn't know about the .filter function within the call.done. I was thinking run another ajax query with the filter built into Rest and then working with that count and that got really ugly. I really appreciate you Dylan, you've helped me on a couple of these. I really don't know much about what I'm doing so I'm very appreciative for your assistance
    – Chuck.M
    Aug 16, 2018 at 16:53
  • @Chuck.M absolutely no problem, I remember back when I was getting started and was trying to figure everything out, I relied on good answers in support forums like this. So I'm just kind of paying it forward. Aug 16, 2018 at 17:29
0

You can get the ItemCount property of the source list when querying for Items by modifying your REST call to include an expansion of the ParentList property

https://site/_api/web/lists/getbytitle('ListName')/Items?$expand=ParentList

This will add a ParentList object to each row of data returned, where ParentList.ItemCount is the total number of items in the list. Then you can fetch the value from e.g. data[0].ParentList.ItemCount

{
  "FileSystemObjectType": 0,
  "Id": 1,
  "ID": 1,
  "Modified": "2019-05-24T14:36:59Z",
  "Created": "2017-05-26T12:53:44Z",
  "AuthorId": 1,
  "EditorId": 1073741823,
  "OData__UIVersionString": "8.0",
  "Attachments": false,
  "GUID": "428bc81d-56fd-466a-8462-a844e2c736cd",
  "ParentList": {
    // peripheral data from the parent list (omitted)
    "ItemCount": 4983
  }
} 

The downside of this is that you get a lot of extra data back with the request, which slows everything down. Therefore you should also consider using $select to return only the fields you need to use.

On the other hand, it's not too much effort to just issue an initial request for the sole purpose of getting the ItemCount. For example, I use this function (below) and put it in a jQuery.when() that, when resolved, returns the ItemCount property to the next function, which gets the List Items and also passes the ItemCount variable to the DataTables deferLoading setting.

  // Just get the item count for the given list
  function getListItemCount(listId) {
    // wrap the whole thing in a deferred so we can use it in .when()
    var dfd = jQuery.Deferred();

    // REST call 
    jQuery.ajax({
      "url": _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists(guid'" + listId + "')",
      "data": {
        // only return the ItemCount and nothing else
        "$select": "ItemCount"
      },
      "headers": {
        // return cleaner data by specifying 'nometadata'
        "accept": "application/json;odata=nometadata"
      }

    }).done(function (d) {
      // return the ItemCount to the next function
      dfd.resolve(d.ItemCount);
    }).fail(function (e) {
      dfd.reject(e);
    })

    return dfd.promise();
  }

Usage (renderTable accepts ItemCount as a parameter)

    var listId = "5370628A-463B-4F87-8A60-BB54C628F708";
    jQuery.when(
      getListItemCount(listId)
    ).then(renderTable);

Note I use jQuery.when() here but it would also work to just do:

getListItemCount(listId).then(renderTable);

I decided to use .when() specifically because I want to implement other "preparatory" functions to get additional data before passing control to the renderTable function. For example, when using DataTables to render very large lists it is helpful to fetch several pages' worth of data up front, to reduce apparent loading time.

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.