Need To subtract and sum in total value in amount filed.

Hasibul Hasan

Hi i am working in a project for university project. I need some help in data table. basically I am developed an account transection details in account section. so i need to add generate a account Ledger. so 2 problem i face in here that i cant solve.

1. I am using date filter.
   
   i need to calculate amount cell. debit and credit. if credit it will add if debit it will subtract.

2. if i am filter by date it should show me previous total in amount

Can any one help me?

kthorngren

Sounds like you are talking about the code you have in the footerCallback. Have you looked at this example?

The example uses the reduce() API. If you want to subtract then change the return to subtract, like this return intVal(a) - intVal(b);.

if i am filter by date it should show me previous total in amount

Not sure what you mean by this. It would be best to see what you are doing. Minimally please post your relevant Datatables code. Better is a link to your page or a test case showing the issues so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Hasibul Hasan

@kthorngren footerCallback calculate in footer section of total amount but i need to calculate Credit and debit for each cell and store in amount section.

Hasibul Hasan

@kthorngren
in this table you see
amount is empty

it should show
credit Debit Amount
2000 0.00 2000
0.00 1000 1000

here my logic for credit amount+credit
and if debit amount - debit

kthorngren

I see. Use columns.render in the Amount column to calculate from the other two columns. Even though its not numbers this example will show you how.

If you still need help please provide a simple test case so we can take a look at what you are doing.

Kevin

Hasibul Hasan

i think you did not understand me.

when i search by account number and also date to-from. this amount should be calculate like red mark.

suppose like

if(credit){
amount = amount + credit
}
else{
amount = amount-debit
}

also is it possible get previous total amount balance. like bank statement

Hasibul Hasan

` <script>

    $(document).ready(function() {
        //Date Filter Start
        var minDate, maxDate;
        $.fn.dataTable.ext.search.push(
            function( settings, data, dataIndex ) {
                var min = minDate.val();
                var max = maxDate.val();
                var date = new Date( data[5] );
                if (
                    ( min === null && max === null ) ||
                    ( min === null && date <= max ) ||
                    ( min <= date   && max === null ) ||
                    ( min <= date   && date <= max )
                ) {
                    return true;
                }
                return false;
            }
        );
        minDate = new DateTime($('#min'), {
            format: 'MMMM Do YYYY'
        });
        maxDate = new DateTime($('#max'), {
            format: 'MMMM Do YYYY'
        });
        var editor;
       var table= $('#BankTransaction_transection').DataTable({
        columnDefs: [
                        {
                            "targets": [ 1 ],
                            "visible": false,
                            "searchable": true
                        },
                        {
                            "targets": [ 0 ],
                            "visible": false,
                            "searchable": false
                        },
                        ],
        "footerCallback": function ( row, data, start, end, display ) {
        var api = this.api();

        // 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;
        };

        credit = api
            .column( 5, { search: "applied" } )
            .data()
            .reduce( function (a, b) {
                return intVal(a) + intVal(b);
            }, 0 );

        // Update footer
        $( api.column( 5 ).footer() ).html(
            credit
        );
        debit = api
            .column( 6, { search: "applied" } )
            .data()
            .reduce( function (a, b) {
                return intVal(a) + intVal(b);
            }, 0 );

        // Update footer
        $( api.column( 6 ).footer() ).html(
            debit
        );
        total = api
            .column( 7, { search: "applied" } )
            .data()
            .reduce( function (a, b) {
                return credit - debit;
            }, 0 );

        // Update footer
        $( api.column( 7 ).footer() ).html(
            total
        );
    },
            initComplete: function() {
                //Drop Down Account Number
                var column = this.api().column(1);
                var select = $('<select class="form-control"><option value="">All Account</option></select>')
                    .appendTo($('#account_number').empty())
                    .on('change', function() {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
                        column.search(val ? '^' + val + '$' : '', true, false).draw();
                    });
                    column.data().unique().sort().each(function(d, j) {
                        select.append('<option value="' + d + '">' + d + '</option>');
                    });

            }


        });
        //Date Filter
        $('#min, #max').on('change', function () {
            table.draw();
        });

    });

</script>`

kthorngren

You can use rows().every() to loop through all the rows. If needed you can use the selector-modifier to control the order the rows are processed. You tan keep a running total and update the amount column, in the loop, using `-api row().data().

Do this in initComplete if the totals need to be done once at table initialization. Or you can use drawCallback to update the totals for each table draw, ie, sort, search or page. Or you can use the loop in the events order or search if you don't need to update for each draw.

Again with a simple test case, like at http://live.datatables.net/ , we can provide more direct help. You can create an example of your data using Javascript like this example.

Kevin

Hasibul Hasan

@kthorngren

thanks for your help i find another solution and it works. i use temporary variable that store my total amount in every transection. thanks again for your help

kthorngren

Glad you got it working.

Kevin