I want to count number of rows in 'Whole' table (Including All Pages) with specific column value.

AlphaTry

Hi All, This is my first post here. I'm quite new to web development, getting used to datable.

I want to do a client side validation before user request gets submitted. The page has a Datatable ("#datatableOne") containing column 'Status' (at #7) whose possible values are 'U****nknown', 'Completed', 'Pending', 'Not Found'. Also these column value are wrapped inside a '<span>' tag.

<td>
   <span class="label label-Default">Unknown</span>
</td>

One More thing, This table is being loaded using Ajax request to ASP.MVC Controller ActionMethod.

Now I want to calculate the number of rows with **'Unknown' **status. I tried the following, but it only calculates the count of 'Unknown' rows currently shown and not consider the rest of paginated rows hidden behind other pages. I'm doing it outside (document).ready(), in a separate function.

function tableOneRowCount(){
    var rows = $("#datatableOne td:nth-child():contains('Unknown')" ).length;
    alert("Unknown rows :" + rows);
}

Also tried doing the following. But this has even more unusual behavior. It is not including the paginated rows unless you make them visible once. so unless I select 2nd page it won't include 'Unknown' rows for 2nd page. Again doing the following outside (document).ready(), in a separate function

function tableOneRowCount(){
    var table = $('#datatableOne').DataTable();
    var rowCount = table.rows(':contains("Unknown")').data().length;
    alert("unknown rows : " + TableOneUnhandledRows);
}

I believe this is because I'm loading datatable via Ajax. If I try counting rows on a static table( Non-Ajaxified ), it shows a correct number of 'Unknown' rows.

If there any better approach kindly give a feedback. Thanks,

Following is how I'm loading my datatable.

var tableOne = $('#datatableOne').DataTable({
            ajax:{
                url: '@Url.Action("AssetsUnderContract_AjaxHandler", "SiteReport")',
                type: 'GET',
                data: { "SiteReportID": SiteReportID }
            },
            "bProcessing": true,
            "columns": [
                {"data": "report_asset_id"},
                {"data": "product_name"},
                {"data": "serial_number"},
                {"data": "description"},
                {"data": "site_location"},
                {"data": "asset_guarantee"},
                {"data": "asset_calibration_status"}
            ],
            "columnDefs": [ 
                {
                "targets": 7,
                "data": null,
                "defaultContent": '<button class="btn btn-round btn-primary btn-xs" type="button">Load</button>'
                }
            ],
            "fnRowCallback": function (nRow, aData, iDisplayIndex, iDisplayIndexFull) {        
                
                if(aData["asset_guarantee"] == true){
                    $("td:eq(5)", nRow).text("").wrapInner('<span class="glyphicon glyphicon-check"></span>').css("text-align", "center")
                }else {
                    $("td:eq(5)", nRow).text("").wrapInner('<span class="glyphicon glyphicon-unchecked"></span>').css("text-align", "center")
                }

                if(aData["asset_calibration_status"] == 0){
                    $("td:eq(6)", nRow).text("Unknown").wrapInner('<span class="label label-default"></span>').css("text-align", "center")
                }else if(aData["asset_calibration_status"] == 1){
                    $("td:eq(6)", nRow).text("Completed").wrapInner('<span class="label label-success"></span>').css("text-align", "center")
                }else if(aData["asset_calibration_status"] == 2){
                    $("td:eq(6)", nRow).text("Not Found").wrapInner('<span class="label label-danger"></span>').css("text-align", "center")
                }else if(aData["asset_calibration_status"] == 3){
                    $("td:eq(6)", nRow).text("Pending").wrapInner('<span class="label label-danger"></span>').css("text-align", "center")
                }
                return nRow;
            }
        });

allan

Hi,

table.rows(':contains("Unknown")')

I can see your thinking there, because it works with jQuery. But I'm afraid the rows() selector does not descend into children - at least not intentionally (consider for example hidden columns, it wouldn't be able to select based on that).

There are quite a few ways to do this with the DataTables API, but I think the most succinct might be to use the row-selector as a function:

var count = tableOne.rows( function ( idx, data, node ) {
    return data.asset_calibration_status === 0;
  } ).count();

And have similar counters for the other options.

This works nicely in this case since your have your asset_calibration_status parameter which can be easily checked. Then we can select the rows with the value we are interested in.

Regards,
Allan