Jquery Question posted in
The official Jquery Api Documentation can be found here.

Jquery – Display the total sum of a DataTable column in Grocery CRUD 2.0.1

I am using Grocery CRUD 2.0.1 with the DataTables theme under Code Igniter 4.5.1

The resulting table has an automatic filter at the bottom under each column and depending on the used keywords, the displayed rows are dynamically filtered.

I have a column (Total) for which I want to calculate the sum of all displayed (filtered) results and display it in a specific div.

I tried many approaches but I can’t figure out a way to achieve it.

I used the following code in the datatables.js file right after

$(document).ready(function() {
    console.log("Script running!");
    function calculateGrandTotal(table) {
        console.log("table:", table); // Log the table object
        var totalElements = table.column('.total').data().toArray(); // Get data from "total" column
        if (!totalElements) {
            console.error("Could not calculate totalElements!");
            return;
        }
        console.log("totalElements:", totalElements); // Log the data array
        var grandTotal = 0;
        for (var i = 0; i < totalElements.length; i++) {
            var price = parseFloat(totalElements[i]);
            grandTotal += price;
        }
        return grandTotal.toFixed(2);
    }
    var grandTotalElement = document.getElementById("grand-total-display"); //The div id in the View
    var table = $('.groceryCrudTable').DataTable; // Get DataTable instance from the event target
    if (table) {
        console.log("DataTable instance found!");
        grandTotalElement.textContent = "Grand Total: " + calculateGrandTotal(table) + " DA";
    } else {
        console.error("DataTables not found on the table!");
    }
});

Then the console returns the following:

enter image description here

That’s the closest I could get after trying, tinkering and searching everywhere.

It seems that the initialized DataTable instance doesn’t recognize the column method and I don’t know how to reach those column values in order to sum them. For info, I was able to to assign a specific css class named .total to my target column using this code in the Controller:

$crud->callbackColumn('Total', function ($value, $row) {return "<p class="total">$value</p>";

but if you have a better idea, I’m all ears.

It also seems that the DataTables version used with Grocery CRUD 2.0.1 is not the most recent (I think it’s 1.9.2) so many of the suggested ideas I found around did not apply or led to incompatibility issues or maybe it’s just me…

I would be grateful if any expert in Grocery CRUD and Javascript could help with that.

Tags:

2

Answers


  1. 0

    var table = $('.groceryCrudTable').DataTable; needs to be var table = $('.groceryCrudTable').DataTable(); [I am assuming its a TYPO mistake only]

    Now You can use data-table call back function like below:

    $(function() {
  $('#groceryCrudTable').DataTable({
    "footerCallback": function(row, data, start, end, display) {
      var api = this.api(),
        data;

      total = api
        .column(3)
        .data()
        .reduce(function(a, b) {
          return parseInt(a) + parseInt(b);
        }, 0).toFixed(2);

      $('#grand-total-display').val(total);
    }
  });

});
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
<table id="groceryCrudTable" class="table table-striped first-table" style="width:100%">
  <thead>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Salary</th>

    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Tiger Nixon</td>
      <td>System Architect</td>
      <td>Edinburgh</td>
      <td>45000</td>
    </tr>
    <tr>
      <td>Cedric Kelly</td>
      <td>Senior Javascript Developer</td>
      <td>Edinburgh</td>
      <td>55000</td>
    </tr>

  </tbody>
</table>

<br>
<br>
<input type="text" id="grand-total-display">
    Login or Signup to reply.
  2. 0

    The issue you’re encountering is because the DataTable instance isn’t correctly referenced. Specifically, $('.groceryCrudTable').DataTable; should be $('.groceryCrudTable').DataTable(); to correctly initialize the DataTable instance. Additionally, the table.column('.total').data().toArray(); method requires the appropriate selector and DataTable API usage.

    $(document).ready(function() {
    console.log("Script running!");
    
    function calculateGrandTotal(table) {
        console.log("table:", table); // Log the table object
        
        // Get data from "total" column
        var totalElements = table.column('.total').data().toArray(); 
        
        if (!totalElements) {
            console.error("Could not calculate totalElements!");
            return;
        }
        
        console.log("totalElements:", totalElements); // Log the data array
        
        var grandTotal = 0;
        for (var i = 0; i < totalElements.length; i++) {
            var price = parseFloat(totalElements[i]);
            grandTotal += price;
        }
        
        return grandTotal.toFixed(2);
    }

    var grandTotalElement = document.getElementById("grand-total-display"); // The div id in the View
    
    // Initialize DataTable instance correctly
    var table = $('.groceryCrudTable').DataTable(); 
    
    if (table) {
        console.log("DataTable instance found!");
        
        // Ensure the column selector is correct
        var grandTotal = calculateGrandTotal(table);
        if (grandTotal !== undefined) {
            grandTotalElement.textContent = "Grand Total: " + grandTotal + " DA";
        } else {
            grandTotalElement.textContent = "Grand Total: Error calculating";
        }
    } else {
        console.error("DataTables not found on the table!");
    }
});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search