Datatables footerCallback, exclude rows and sum it with JSON data

Issue

This question is based on footerCallback with duplicate cell numbers

I implement the solution from @andrewJames (thanks).

I’m trying to sum last column of the table exept the salary of System Architect and Senior Javascript Developer

But now there is a problem when I use JSON data. The sum is always 0… instead of $9

HTML:

<div class="row">

  <div class="large-12 columns">


  <table id="example" class="display nowrap table1" cellspacing="0" width="100%">
      <thead>
        <tr>
          <th>Seq.</th>
          <th>Name</th>
          <th>Position</th>
          <th>Salary</th>
        </tr>
      </thead>
      <tfoot>
        <tr>
          <th>Seq.</th>
          <th>Name</th>
          <th>Position</th>
          <th>Salary</th>
        </tr>
      </tfoot>
    </table>

  </div>

</div>  

jQuery:

var salaryTable = {
    "data": [
        {
            "Seq": "1",
            "Name": "Tiger Nixon",
            "Position": "System Architect",
            "Salary": "$1"
        },
        {
            "Seq": "1",
            "Name": "Garrett Winters",
            "Position": "Accountant",
            "Salary": "$1"
        },
        {
            "Seq": "3",
            "Name": "Ashton Cox",
            "Position": "Junior Technical Author",
            "Salary": "$3"
        },
        {
            "Seq": "4",
            "Name": "Cedric Kelly",
            "Position": "Senior Javascript Developer",
            "Salary": "$4"
        },
        {
            "Seq": "5",
            "Name": "Airi Satou",
            "Position": "Accountant",
            "Salary": "$5"
        }
    ]
}; 
 
 
 $(document).ready(function() {
    var table = $('#example').DataTable( {
        rowReorder: {
        selector: 'td:nth-child(2)'
      },
      data: salaryTable.data,
            columns: [
            { data: "Seq" },
            { data: "Name" },
            { data: "Position" },
            { data: "Salary" }
        ],
      responsive: true,
      scrollX: true,
      scrollY: "80vh",
        scrollCollapse: true,
            paging: true,
      lengthChange: false,
      lengthMenu: [ [10, 25, -1], [10, 25, "All"] ],
      "order": [[ 0, "asc" ]],
      "footerCallback": function ( row, data, start, end, display ) {
            var api = this.api(), data;
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            filteredTotal = api.rows().data().reduce(function (a, b) {
              // filterMe will be true or false:
              filterMe = b[2] === "System Architect" || b[2] === "Senior Javascript Developer";

              // if filterMe is true then use 0, otherwise use the actual amount from b[3]:
              salary = filterMe ? 0 : intVal(b[3]);

              return a + salary;
            }, 0 );
 
            // Total over this page
            pageTotal = api
                .column( 3, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $( api.column( 3 ).footer() ).html(
                '$'+filteredTotal+'/ all $'+pageTotal
            );
        },
        buttons: ['pdf', 'print']
    } );
 
    table.buttons().container()
        .appendTo( '#example_wrapper .small-6.columns:eq(0)' );
} );
   $(document).foundation();

Fiddle with the problem: https://jsfiddle.net/87360vyx/1/

Solution

The difference between this Ajax approach and your previous HTML approach is this:

When DataTables pulls its data directly from an HTML table, a row of data is handled as a JavaScript array of values:

[ '1', 'Tiger Nixon', 'System Architect', ... ]

But with the JSON you are using in this question, the equivalent row of data is stored in DataTables as a JavaScript object:

{ Seq: "1", Name: "Tiger Nixon", Position: "System Architect", ... }

Therefore you cannot access row values using code like b[2]. Instead you have to use b.Position. And instead of b[3] you have to use b.Salary.


You can see the difference for yourself by adding a console.log( b ); statement to each of the different versions of the code, inside the rows().data().reduce(...) code. That will show you exactly how DataTables is handling its row data, internally.

You can also an official example here: Ajax data source (objects)

Answered By – andrewJames

Answer Checked By – Marilyn (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.