How can I do any multiplication or any mathematical operation for 2 rows in data table

Question

I am new to development, and trying to build data table with JSON. So far I did

{
    "qty": "2",
    "price": 1
}, {
    "qty": "3",
    "price": "5",
}, {
    "qty": "3",
    "price": "4",
}

var table = $('#example').DataTable({
            data: dataSet,
            columns: [{
                        title: "qty",
                        data: "qty"
                    },
                    {
                        title: "price",
                        data: "price",
                        render: function(data, type, row) {
                            return '';
                        },
                        {
                            title: "total",
                            data: "multiplication'}
                        }
                    ]

I want to perform multiplication of two rows qty and price and store result in third row.

user_13983399 · Answer

Here is a simple solution you can do this with columnDefs option:

In your column definition, add a third data column with the value null. (Remember to have the number of columns matching in your HTML). I created a third HTML column with the Total. The table will render the column empty.

When it gets to the columnDef, you want to make sure you target the correct column, remember it reads them based on a 0 index, so 2 is actually 3.

columnDefs: [{
                "targets": 2,
                "render": function(data, type, full, meta) {
                    return type === 'display' ? ((full.qty)*(full.price)) + '' : data;
                }

Remember, the full parameter is the data for the entire row, so in the return of your render, you multiply the two data values (full.qty)*(full.price)

var dataSet = [
{
    "qty": "2",
    "price": "1",
}, 
{
    "qty": "3",
    "price": "5"
}, 
{
    "qty": "3",
    "price": "4"
}]

var table = $('#example').DataTable({
            data: dataSet,
            columns: [
            {"data" : "qty"},
            {"data": "price"},
            {"data" : null}
            ],
            columnDefs: [{
                "targets": 2,
                "render": function(data, type, full, meta) {
                    return "$" + ((full.qty)*(full.price));
                }
            }]
});

td {
  text-align: center !important;
}



 





  
    
        
      Quantity
            Price
            Total

user_463319 · Answer

Consider another example.

$(function() {
  var dataSet = [{
      "qty": 2,
      "price": 1.00,
    },
    {
      "qty": 3,
      "price": 5.99
    },
    {
      "qty": 3,
      "price": 4.00
    }
  ];

  function calcTotal(q, p) {
    var t = 0.00;
    t = parseInt(q) * parseFloat(p);
    return t.toFixed(2);
  }

  $.each(dataSet, function(i, r) {
    r.total = calcTotal(r.qty, r.price);
  });

  var table = $('#example').DataTable({
    data: dataSet,
    columns: [{
        "data": "qty"
      },
      {
        "data": "price",
        "render": function(data, type, row) {
          return '$';
        }
      },
      {
        "data": "total",
        "render": function(data, type, row) {
          return "$" + data;
        }
      }
    ]
  });

  $("#example").on("change", ".price-input", function(event) {
    var row = $(this).closest("tr");
    var qty = table.cell($("td", row).eq(0)).data();
    var price = $(this).val();
    var total = table.cell($("td", row).eq(2).get(0));
    total.data(calcTotal(qty, price)).draw();
  });
});

td {
  text-align: center !important;
}

.price-input {
  width: 5em;
}









  
    
      Quantity
      Price
      Total

This updates the Data Set before building the Table. So the values are already there. You can then use a change callback to modify that data.

Note: Most interfaces want to change the Quantity and not the price, but since your example changed the Price, I kept it as is. You can easily modify this to work the other way.

How can I do any multiplication or any mathematical operation for 2 rows in data table

2 Answers