Multiplication of two column and result in third column

jigar311982 · November 2020

Hello,

I am using datatables in below steps,

Table initialise with blank rows.
Then I am using custom JS for adding rows with data pulled from the server.
Data appeared in a row. works perfectly fine.
Now, here one column have some numbers(say price) coming from the server, and the second column have a custom render of Touchspin(where you can increase value by pressing plus and minus icon),
what I need to have a multiplication of Price and Number, and that value should appear in 3rd column.
Just like excel, where one column with fix value and another column can change value and 3rd column should show multiplied resuts.

Is that possible with any script, API?

Thanks,
Jigar.

colin · November 2020

You can use columns.render, the third parameter is the data for all the row so you can use that in your sums,

Colin

jigar311982 · November 2020

Hello Colin,

I need to get multiplication of two cell values and get in 3rd cell of the same row, how can i do that?

kthorngren · November 2020

Take a look at this example using columns.render. It is adding two strings together but instead you can multiply two numeric columns for a numeric result.

Kevin

jigar311982 · November 2020

Hello Kevin,
That works, but I have a cell with form input,
I need to get the value of this form input and then do multiplication, is that possible?

{
targets: 3,
width: "25%",
render: function(data, type, full, meta) {
return '\<input type="text" class="form-control touch" name="item_quantity" placeholder="Add Qunt" />';
},
},

kthorngren · November 2020

In that case you will want to use rowCallback instead. You will need an event handler to redraw, using draw(), the table when an input is changed. In the rowCallback function you will get the input value, perform the calculation then write it to the desired cell. Here is a simple example based on your input.
http://live.datatables.net/momabajo/1/edit

Kevin

jigar311982 · December 2020

Hello Kevin,
This works perfectly fine for existing data which either came from the server or local HTML data,
But when I add a row with the row.add(), it does not calculate.
My datatables have columns field defined,

        columns: [
            {data: 'item_id', visible:false},
            {data: 'item_name'},
            {data: 'item_price'},
            {data: 'item_quantity'},
            {data: 'item_total'},
            {data: 'item_action'},
        ],

and i am adding row with below Jquery,

$('.click2add').on( 'click', function () {

            var item_ids =  $('.itemid', this).attr("id");

            jQuery.ajax({
                url: "/menu/edit_item",
                data: { item_id:item_ids },
                type: "POST",
                success:function(data){
                    if(data.val == 1)
                    {
                        var ordertotal = 0;
                        var table = $('#itemorders_list').DataTable();
                        table.row.add( {
                            "item_id":   data.response.item_id,
                            "item_name":  data.response.item_name,
                            "item_price":     data.response.item_price,
                            "item_quantity": 5,
                            "item_total":  1,
                            "item_action":  'delete'
                        } ).draw();

                        $("#totalValue span").text(ordertotal+data.response.item_price);
                    }
                    else
                    {
                        toastr.error(data.msg);
                        toastr.options = {
                            "closeButton": true,
                            "debug": false,
                            "positionClass": "toast-top-center",
                            "onclick": null,
                            "showDuration": "300",
                            "hideDuration": "1000",
                            "timeOut": "2000",
                            "showEasing": "swing",
                            "hideMethod": "fadeOut"
                        };
                    }
                }
            });

            
        } );

jigar311982 · December 2020

Hi Kevin,

To get working in my code, need to change rowcallback data as data.item_price instead of data[2].

            rowCallback: function (row, data) {
      
                // Get input val of item_quantity input
                var val = $('input[name="item_quantity"]', row).val();
          
                // Set to 1 if undefined or blank
                if ( val === '' || val === undefined ) {
                  val = 1;
                }
                
                // Multiply Age by Multiplier
                var total = data.item_price * val;
                
                // Save in Total column
                $('td:eq(3)', row).html( total );
              }

By the way, I have one more question,
How can we send this complete dataTable data back to the server where we have to render form input?

colin · December 2020

You would need to go through the rows() or cells(), getting the val()of the node(). It may be worth considering Editor, as that will keep the table and the server in sync.

Colin

jigar311982 · December 2020

Is that possible to do sum at footer after multiplication values? means the total of total column change after change in multiplier? I can get a sum of default values only, but while changing multiplier, footer sum is not updating.

                footerCallback: function ( row, data, start, end, display ) {
                    var api = this.api(), data;
            
                    var intVal = function ( i ) {
                            return typeof i === 'string' ?
                                    i.replace(/[\$,]/g, '')*1 :
                                    typeof i === 'number' ?
                                            i : 0;
                    };
                    pageTotal = api
                            .column( 4, { page: 'current'} )
                            .data()
                            .reduce( function (a, b) {
                                    return intVal(a) + intVal(b);
                            }, 0 );

                    // Update footer
                    $( api.column( 4 ).footer() ).html(
                            pageTotal
                    );

                    $("#totalValue span").text(pageTotal);
            },

colin · December 2020

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

jigar311982 · December 2020

@colin , Can you please look at this link?

live.datatables.net/qenoguko/1/edit

kthorngren · December 2020

See this thread asking the same question about totaling rendered columns.

Kevin

jigar311982 · December 2020

@kthorngren, I added below code in your link, but that does not do some for multiplied column result values, it stays at 0.

      // Total over all pages
      totalsum = api
                .cells( null, 4, { page: 'current'} )
                .render('display')
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );

      $(api.column(3).footer()).html(
        '(' + totalsum + ' SAR) '
      );

can you run this in your multiplication example url?

kthorngren · December 2020

Please provide an updated test case link with what you are trying to do.

Kevin

jigar311982 · December 2020

@kthorngren , here is link,

I could able to get then sum of Age column, but I could not get the sum of Total column,
Total column cells will get change based on multiplier input and then I need a sum of Total column

live.datatables.net/fihowere/1/edit

colin · December 2020

      $('td:eq(4)', row).html( total );

It's because you're updating the HTML of the table with the total - not the table's data. DataTables uses cached information, so wouldn't know about the change.

I added a hack,

            this.api().cells(null, 4).invalidate();

to force DataTables to reread the table, but the better solution would be to use cell().data() to update the table.

Here's the updated example,

Colin

jigar311982 · December 2020

@colin , thanks for this,
Somehow my additional plugin for multipler(touchspin) is getting conflict with this,
Can you please rewrite this example with cell.data() instead of my html one?

Also can you help me to understand what is null and 4 in cells argument?

 totalsum = api
      .cells( null, 4, { page: 'current'} )
      .render('display')
      .reduce( function (a, b) {
        return intVal(a) + intVal(b);
      }, 0 );

kthorngren · December 2020

Updated the rowCallback with this:

this.api().cell(row, 4).data( total );

Removed the this.api().cells(null, 4).invalidate(); that Colin used.

http://live.datatables.net/fihowere/5/edit

Also can you help me to understand what is null and 4 in cells argument?

Read the cell() docs and the row-selector and column-selector docs for all the details. The null is the row-selector which the docs state "No selector - Select all rows" so this iterates all rows. The 4 is the column number.

Somehow my additional plugin for multipler(touchspin) is getting conflict with this,

Don't know what that is. If you think its in conflict please update the test case to show the issue.

Kevin

jigar311982 · December 2020

@kthorngren , this works perfectly fine....:-)
Thanks a lot for this super support. Appreciate.

Multiplication of two column and result in third column

Multiplication of two column and result in third column

Answers

Howdy, Stranger!

Categories

DataTables

Multiplication of two column and result in third column

Multiplication of two column and result in third column

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables