How to dynamically mulitply 2 cols and render in last col

webvision

I would like to mulitply columns Unit and Unit Price and render the result in the Total column.
Like you would see on an invoice.

Here is my code...

Javascript:

editor = new $.fn.dataTable.Editor( {
        ajax: "/report_modules/pf2/act/PF2_detail.php?cid=1&fid=1",
        table: "#InvoiceDetail",
        fields: [ {
                label: "QTY:",
                name: "d_qty"
            }, {
                label: "Description:",
                name: "d_description"
            }, {
                label: "Units:",
                name: "d_charge_units"
            }, {
                label: "Unit Price:",
                name: "d_unit_price"
            }, {
                label: "Total:",
                name: "d_total_extension"
            }
        ]
    } ); 
    editor
        .on( 'onOpen', function () {
            // Listen for a tab key event
            $(document).on( 'keydown.editor', function ( e ) {
                if ( e.keyCode === 9 ) {
                    e.preventDefault();
 
                    // Find the cell that is currently being edited
                    var cell = $('div.DTE').parent();
 
                    if ( e.shiftKey && cell.prev().length && cell.prev().index() !== 0 ) {
                        // One cell to the left (skipping the first column)
                        cell.prev().click();
                    }
                    else if ( e.shiftKey ) {
                        // Up to the previous row
                        cell.parent().prev().children().last(0).click();
                    }
                    else if ( cell.next().length ) {
                        // One cell to the right
                        cell.next().click();
                    }
                    else {
                        // Down to the next row
                        cell.parent().next().children().eq(1).click();
                    }
                }
            } );
        } )
        .on( 'onClose', function () {
            $(document).off( 'keydown.editor' );
        } );
        $('#InvoiceDetail').on( 'click', 'tbody td:not(:first-child)', function (e) {
            editor.inline( this, {
                submitOnBlur: true
            } );
        } );
    var oTable = $('#InvoiceDetail').DataTable( {
        dom: "Trtip",
        bSort: false,
        paging: false,
        ajax: "/report_modules/pf2/act/PF2_detail.php?cid=1&fid=1",
        columns: [
            { data: "d_qty" },
            { data: "d_description" },
            { data: "d_charge_units" },
            { data: "d_unit_price", render: $.fn.dataTable.render.number( ',', '.', 0, '$' )  },
            { data: "d_total_extension", render: $.fn.dataTable.render.number( ',', '.', 0, '$' )  }
        ],
        tableTools: {           
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: editor },1
                { sExtends: "editor_remove", editor: editor },
                {
                    sExtends: 'select_single',
                    sButtonText: 'PDF',
                    sButtonID: 'view_image',
                    fnClick: function ( button, config ) {
                      var order = oTable.order(); 
                      var sortorder = order[0][0];
                      var sortdir = order[0][1];    
                      document.location.href=('download.cfm?fid=1'+'&sort='+sortorder+'&dir='+sortdir);
                    }
                }
            ]
        }
    } );
} );

HTML:

<table id="InvoiceDetail" class="display responsive" cellspacing="0" width="100%">
     <thead>
          <tr>
               <th>QTY</th>
               <th>Description</th>
               <th>Charge Units</th>            
               <th>Unit Price</th>
               <th>Total Extensions</th>
           </tr>
      </thead>
       
      <tfoot>
          <tr>
              <th>QTY</th>
              <th>Description</th>
              <th>Charge Units</th>            
              <th>Unit Price</th>
              <th>Total Extensions</th>
         </tr>
    </tfoot>
</table>

allan

Hi,

Thanks for all the code. At the moment it looks like d_total_extension is coming from the database and being edited (to then be stored in the database) is that correct? Or is that to be 100% calculated from the units and price columns?

Assuming the latter (and you drop it from the Editor fields!) what you could do is:

{
  data: null,
  render: function ( data, type, row ) {
    return row.d_charge_units * row.d_unit_price;
  }
}

Now, if you implement the above you will see that the Total number is not formatted like the Price column will be. However, I wanted to demonstrate how to use columns.render for the basic calculation.

Now let's add the formatting to it - we use the same formatter function again, but in this case, we execute it directly, since we already have a renderer function (a little complicated - but lets see the code):

{
  data: null,
  render: function ( data, type, row ) {
    var total = row.d_charge_units * row.d_unit_price;
    return $.fn.dataTable.render.number( ',', '.', 0, '$' ).display( total );
  }
}

This works because $.fn.dataTable.render.number() returns an object with a display property that is a function, that will construct a formatted number (total) based on the parameters given.

It could be optimised by storing the result of $.fn.dataTable.render.number( ',', '.', 0, '$' ).display into a variable and then calling that. Its not much of an optimisation, but it might help a bit of you have a large table.

Hope this helps!

Regards,
Allan