Need a column that totals the data in each row

rmartin93

I've got a table with the following columns:

• Checkbox (allows users to select a row)
• Description
• Budget
• Invoice 1
• Invoice 2
• Invoice 3
• Invoice 4
• Invoice 5
• Invoice Total

I need the Invoice Total column to be a total of Invoices 1 - 5.

I've got something working that creates a total row in the footer, but I see no documentation on how to make an additional column that totals like this.

Here's my JS

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    // Get the table data
    var clientId = $("#clientId").val();    
    var myUrl = site_url + "costAnalysis/getAnalysis/" + clientId;
    $.ajax({
        url: myUrl
    }).done(function(data) {
        var tableData = JSON.parse(data);
        console.log(data);
        // Make the dataTables editor function
        editor = new $.fn.dataTable.Editor({
            data: tableData,
            table: "#example",
            idSrc:  'Trade_Class_ID',
            fields: [ {
                    label: "Description",
                    name: "Description",
                    type: "readonly"
                }, {
                    label: "Budget",
                    name: "Budget"
                }, {
                    label: "Invoice 1",
                    name: "Invoice1"
                }, {
                    label: "Invoice 2",
                    name: "Invoice2"
                }, {
                    label: "Invoice 3",
                    name: "Invoice3"
                }, {
                    label: "Invoice 4",
                    name: "Invoice4",                
                }, {
                    label: "Invoice 5",
                    name: "Invoice5"
                }, {
                    lable: "Invoice Total",
                    name: "InvoiceTotal",
                    type: "readonly"
                }, {
                    lable: "Trade Class ID",
                    name: "Trade_Class_ID",
                    type: "hidden"
                }
            ]
        });
        // When they change a cell, update the table
        editor.on( 'edit', function ( e, type ) {
            // Type is 'main', 'bubble' or 'inline'
            var payload = editor.get();
            payload.clientId = $("#clientId").val();            
            console.log(payload);
            $.ajax({
                url: site_url + "costAnalysis/updateRow",
                data: payload,
                type: "POST"
            });
        });
        // Create the dataTable
        var table = $('#example').DataTable( {
            dom: "Bfrtip",
            data: tableData,
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },
                { data: "Description" },
                { data: "Budget",   render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "Invoice1", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "Invoice2", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "Invoice3", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "Invoice4", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "Invoice5", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) },
                { data: "InvoiceTotal", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
            ],
            columnDefs: [
                {
                    targets: [2, 3, 4, 5, 6, 7, 8],
                    className:'dt-body-right'
                }
            ],
            keys: {
                columns: ':not(:first-child)',
                editor:  editor
            },
            select: {
                style:    'os',
                selector: 'td:first-child',
                blurable: true
            },
            buttons: [
                { extend: "edit",   editor: editor }
            ], 
            // Totals for footer
            "footerCallback": function ( row, data, start, end, display ) {
                var api = this.api(), data;
     
                // Remove the formatting to get integer data for summation
                var intVal = function ( i ) {
                    return typeof i === 'string' ?
                        i.replace(/[\$,]/g, '')*1 :
                        typeof i === 'number' ?
                            i : 0;
                };
     
                // Budget total
                budgetTotal = api
                    .column( 2 )
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 );
     
                // Update footer
                $( api.column( 2 ).footer() ).html(
                    '$'+budgetTotal
                );

                // Invoice total
                invoiceTotal = api
                    .column( 8 )
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 );
     
                // Update footer
                $( api.column( 8 ).footer() ).html(
                    '$'+invoiceTotal
                );
            }            
        } );        
    });

} );

Here's the HTML

<!-- Content -->
<div id="main" class="extraPadding">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
            <input type="hidden" id="clientId" value="<?=$Client_ID?>">            
                <h4>Cost Analysis for...</h4>
                <table id="example" class="display" cellspacing="0" width="100%">
                    <thead>
                        <tr>
                            <th></th>
                            <th class="text-left">Description</th>
                            <th class="text-right">Budget</th>
                            <th class="text-right">Invoice 1</th>
                            <th class="text-right">Invoice 2</th>
                            <th class="text-right">Invoice 3</th>
                            <th class="text-right">Invoice 4</th>
                            <th class="text-right">Invoice 5</th>
                            <th class="text-right">Invoice Total</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th colspan="2" class="text-right"></th>
                            <th class="text-right"></th>
                            <th class="text-right"></th>
                            <th class="text-right"></th>
                            <th class="text-right"></th>  
                            <th class="text-right"></th>  
                            <th class="text-right"></th>   
                            <th class="text-right"></th>                                                    
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
</div>

kthorngren

You can use columns.render for this. Here is an example that shows how to access data in other columns.
https://datatables.net/examples/advanced_init/column_render.html

Kevin

rmartin93

kthrongren

First, I don't see a way to "reply" to you. I just see this Leave a Comment box.

Second, that doesn't look like what I'm looking for. The Invoice Total is the total of Invoices 1 - 5 on page load, but when I update an invoice, the total column doesn't update.

I'm looking for a way to refresh the data after I edit it.

So, ideally, I think I would refresh the Invoice Total Column somewhere in this function....

// When they change a cell, update the table
        editor.on( 'edit', function ( e, type ) {
            // Type is 'main', 'bubble' or 'inline'
            var payload = editor.get();
            payload.clientId = $("#clientId").val();           
            console.log(payload);
            $.ajax({
                url: site_url + "costAnalysis/updateRow",
                data: payload,
                type: "POST"
            });
        });

minobu

@rmartin93 you can use the "@" symbol to mention people.

colin

I'd say there are two ways to go.

One is to use rowCallback - this will draw the total cell on each page draw. So may be inefficient depending on your row count count and page size.

The other option is as you say to use an event. I would use submitSuccess, as this is after the update has completed successfully. You would then modify the total cell's data with cell().data()

Colin