Adding and calculating a running balance column

wannberg

I am creating a ledger with datatables. I want a column that keeps a running balance on each row and updates if you change, add, or delete the value of Income or expense fields. How do I create that balance column and have it update? I could have swore I saw an example on the site, but can't find it again.

For clarification I have an Income column, an expense column, and a balance column. The income and expense column data are pulled from mysql with an ajax and php script. I then want to create a balance column from the previous row and adds or subtracts the income/ expense column from the current row.

wannberg

Not sure if this is the best way to do it, but I was able to edit the PHP file to sort the entries based on date and the timestamp, then create the balance object in the array.
So then after an edit/create/delete I just reload the table.

php file:

   ->process( $_POST )
    ->data();

if ( !isset($_POST['action']) ) {

    $out['data'] = array_orderby($out['data'], 'transactionDate', SORT_ASC, 'dateCreated', SORT_ASC);
    $balance = 0;
    for ( $i=0, $ien=count($out['data']) ; $i<$ien ; $i++ ) {
        $balance = $balance + $out['data'][$i]['income'] - $out['data'][$i]['expense'];
        $out['data'][$i]['balance'] = $balance;
    }

}else{
    $out['row']['balance']=null;
}


echo json_encode($out);

//Sort data for proper output
function array_orderby()
{
    $args = func_get_args();
    $data = array_shift($args);
    foreach ($args as $n => $field) {
        if (is_string($field)) {
            $tmp = array();
            foreach ($data as $key => $row)
                $tmp[$key] = $row[$field];
            $args[$n] = $tmp;
        }
    }
    $args[] = &$data;
    call_user_func_array('array_multisort', $args);
    return array_pop($args);
}

js file:

    editor.on('edit create remove', function(){
        accounting.ajax.reload();
        });

nigel pasco

It can be done with a function when defining the columns:data, like this:

$('#tablename).DataTable( {
(...blah, blah, blah...)
"columns": [
{ "data": "income" },
{ "data": "expense"},
{ "data": null, render: function ( data, type, row ) {
// Sum the income and expense to give total
return '<a href="">$'+data.income+data.expense+'</a>';
} },
]
}];

I hope that helps.
nige

wannberg

Thanks I will try that.

allan

If you are using client-side processing then the method shown in the footer callback example is probably the best way to do it.

Alternatively (and required if you are using server-side processing) you could, as you suggest, add the sum information into the JSON data returned by the PHP script and use the submitComplete event to update the footer. You would probably also need to use initComplete to perform the same action on initial load.

Allan