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:

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.

EDIT: After trying both @Rohit and @Death-is-the-real-truth answers which did not succeed in my environment, here is the last state of my script:

var table = $('.groceryCrudTable').DataTable();
if (table) {
console.log("DataTable instance found!");
console.log("table:", table); // Log the table object
} else {
console.error("DataTables not found on the table!");
} 
$('.groceryCrudTable').DataTable({
bRetrieve: true, // I added this in reaction to the below screenshot
"footerCallback": function() {
  var api = this.api(),
  total = api
    .column(6)
    .data()
    .reduce(function(a, b) {
      return parseInt(a) + parseInt(b);
    }, 0).toFixed(2);
 console.log("Total is:", total);
  $('#grand-total-display').val(total);
}
});

The below screenshot appeared before I added the bRetrieve function:

I think that one of the issues is the major difference between the latest version of DataTables (2.0.7) which most people are using to troubleshoot my issue and the version being used by the latest Grocery CRUD iteration, in my case (1.9.2). There's also the whole CodeIgniter environement, the loaded javascripts libraries, jquery and css files that make things even more complicated.

I don't see a way to troubleshoot that other than sending a zip of my App to be tested.

Alright folks, after lots of trials and tribulations, the key aspect was my datatables version (1.9.2) where the api and many other functions were not introduced yet (major refactoring starting from 1.10+). While most suggested solutions I found around on the Internet were based on api or some other functions to access the data in columns (such as fnGetColumnData), "this.api" was not recognized as a function by jquery.dataTables.js 1.9.2.

Also, updating my DataTables javascript to a newer version was not an option for me because I had to keep my jquery.dataTables.js version 1.9.2 otherwise it would've led to a nightmare of incompatibilities with the other components of my environment and a css mess.

I had to rearrange the different solutions to something compatible with 1.9.2 and here is the final result for those who want to achieve the same thing in Grocery CRUD 2.0.1 under CodeIgniter 4.5.1 which I think is a quite interesting feature to be honest.

Inside the loadDataTable function in the datatables.js file:

,"fnFooterCallback": function( nFoot, aData, iStart, iEnd, aiDisplay ) {
var table = $('.groceryCrudTable').DataTable(); // Get DataTables instance
var grandTotal = 0;
// Loop through visible rows (all rows if no filtering)
for (var i = 0; i < aiDisplay.length; i++) {
var rowIndex = aiDisplay[i];
var rowData = table.fnGetData(rowIndex); // Get data for the current row
var price = parseFloat(rowData[6]); // Extract value from "Total Price" column (6 is the column index)
grandTotal += price;
}
var grandTotalElement = document.getElementById("grand-total-display");
grandTotalElement.textContent = "Grand Total: " + formatCurrency(grandTotal);
}

N.B: formatCurrency is just a function to convert the resulting number to a currency format.