Calculate colum cell value base on previous column cell

karibusana

Hi I've got a table where data are loaded via ajax call.
I'm trying to achive the following where column 3 value is calculated base on previous col3 value plus current col1 value minus current col2 value.

col1 | col2 | col3
100 | 0 | 100
100 | 0 | 200
0 | 150 | 50
0 | 200 | -150
200 | 0 | 50

Can anyone please point me to the right direction?

Many thanks

rf1234

Every time your data table is drawn you need to loop through your data table and recalculate column 3. You can do this using rows().every() for example. https://datatables.net/reference/api/rows().every()

Since you are using ajax you can use the field names returned from the server instead of addressing the columns by column index using column().data().

Let's assume you also return column 3 ("FieldThree") from the server and want to update it on each draw because ordering might have changed or for other reasons. If you are manipulating the data sources of the data table you need to use row().invalidate() to make sure data tables notices the change and updates the data table accordingly. (The data table is derived from the values returned from the server but it needs updating if you change those values with client side manipulations!)

table
    .on('draw', function () {
        var previousFieldThreeValue = 0;
        table.rows().every( function (rowIdx, tableLoop, rowLoop) {  
            var data = this.data();
            data.FieldThree = previousFieldThreeValue + data.FieldOne - data.FieldTwo;
            previousFieldThreeValue = data.FieldThree;
            this.invalidate();
        });
    })

karibusana

Hi @rf1234 thanks for your help.

This is how I've adapted your solution to my problem:

          dataTable.on('draw', function() {

            dataTable.rows().every(function(rowIdx, tableLoop, rowLoop){

                var Row = this.data();

                if(rowIdx == 0){

                  // Get calculation values
                  var totaleSaldo = $('.totaleSaldoRisorseCassa').html().replace(',','.');
                  var editUscita = Row[7].replace('€','').replace(',','.');
                  var editEntrata = Row[6].replace('€','').replace(',','.');
                  // Sum the total value
                  var sum = parseFloat(totaleSaldo) + parseFloat(editEntrata) - parseFloat(editUscita);
                  // Show new value in the cell
                  dataTable.cell(rowIdx,8).data('€ ' + sum);

                }

                if(rowIdx != 0){

                  // Get previous row
                  var parentRow = $(this).closest("tr").prev()[0];
                  // Get previous row data
                  var rowData = dataTable.row(parentRow).data();
                  // Get calculation values
                  var previousValue = rowData[8].replace('€','');
                  var editUscita = Row[7].replace('€','').replace(',','.');
                  var editEntrata = Row[6].replace('€','').replace(',','.');
                  // Sum the total value
                  var sum = parseFloat(previousValue) + parseFloat(editEntrata) - parseFloat(editUscita);
                  // Show new value in the cell
                  dataTable.cell(rowIdx,8).data('€ ' + sum);

                }

            });

          });

The only prome is if I want to sort the rows or search in the table then the calculation does't work anymore. Any solution to fix this problem? many thanks

rf1234

try this:

dataTable.on('draw order search', function() {
...

check the docs regardings events please:
https://datatables.net/reference/event/

What I marked in yellow may cause problems too:

Just give it a try.