Calculating cells values

classic12

Hi Guys I have 2 datatables
dtInvoices & dtInvoiceDetails

dtInvoice has the following columns
date - orderNo - net - vat -Total
I have 2 methods of editing dtInvoiceDetails inline & the editor window.
In this table I have columns
quant - sell - nett - lineTotal - vatRate

What is the event so I can calculate the lineTotal when the quant/ sell amounts change ( either by inline or the editor window)

I also have need the sum of lineTotal to add to the dtInvoice table.

I have this so far.

On the dtInvoice click

  $('#dtInvoices').on( 'click', 'tbody td', function () {
        //alert( tableInvoices.cell( this ).data() )
        currentSelectedInvoiceIndex = tableInvoices.row( this ).index() ;
        alert(currentSelectedInvoiceIndex);
} );    

The invoice total is column 2 so I can set the sum to cell(currentSelectedInvoiceIndex,2)

I can't find an example showing this.

So I need

1. Get the onChange event of a row/cell and calculate the line total
2. Then calculate the sum of the column lineTotal
3. Set the total in dtInvoices.

Cheers

Steve Warby

allan

Use a renderer. That will perform the calculation every time the data is requested by DataTables.

Allan

classic12

Hi Allan,

This works.

  { data:  null, title : 'new',
                   render: function ( data, type, row ) {
               return (data.inv_list.quantity*data.inv_list.sellPrice);
           } }

but I have a column from the database 'inv_list.lineTotal' so i want to update this column with the calculated amount.

What is the correct syntax for this please.

I also need to trigger a sum of the column 'inv_list.lineTotal' and update the invoice datatable

The invoice total is column 2 so I can set the sum to cell(currentSelectedInvoiceIndex,2)

What is the syntax for this please?

Cheers

Steve Warby

allan

As in you want to update the total in the database as well? Three options:

1. Have your lineTotal as a field in the Editor form and then it would probably be best to use dependent() to update the values based on inputs in other fields.
2. Use Field->setValue() in a server-side event - preCreate and preEdit.
3. Calculate it on read from the database.

Personally I'd use option three - it is a derived value, so you don't need to have it stored in the database (unless you want to be able to search and sort on it).

Allan

classic12

The data is stored in the database.

I have

       editorInvoiceDetails.dependent( 'inv_list.quantity', function ( val, data ) {
       var quant = Number(editorInvoiceDetails.field( 'inv_list.quantity' ).val());
       var sell = Number(editorInvoiceDetails.field( 'inv_list.sellPrice' ).val());
       var total = quant * sell ;
       console.log('total is ' + total);
      editorInvoiceDetails.set( 'inv_list.lineTotalNett', (total));
} ); 

The calculation does not fire first time. The second time it shows the first result.

What am I not understanding here ?

Cheers

Steve Warby

allan

Not sure - I'm missing something as well. Can you give me a link to the page showing the issue?

Allan

classic12

sent private message

classic12

Hi Allan,

a screen grab of what is happening.

www.toolfolks.com/docs/dependencyProblem.mov

classic12

How do I update the server side.

I have this working

        $("#dtInvoiceDetails").DataTable().destroy(); 
        $('#dtInvoiceDetails').empty();
        tableInvoiceDetails = $('#dtInvoiceDetails').DataTable( {
         "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;
            };
                  var totalNett = api
                .column( 8 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );

                var total = api
                .column( 9 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
        totalNett = totalNett.toFixed(2);
        total = total.toFixed(2);
        totalNettVat = total - totalNett;
        totalNettVat = totalNettVat.toFixed(2);
            // Update footer
            console.log(' Total = ' + total);

       tableInvoices.cell( currentSelectedInvoiceIndex, 5 ).data( totalNett ).draw();
       tableInvoices.cell( currentSelectedInvoiceIndex, 6 ).data( totalNettVat ).draw();
       tableInvoices.cell( currentSelectedInvoiceIndex, 7 ).data( total ).draw();
       
        }

So this updates tableInvoices with what I need.

But the data is not posted to the database. If I click edit then update it works.

What is the syntax for update tableInvoices to server?

Cheers

Steve Warby

allan

DataTables itself won't submit that change to the server. You have two options:

1. Use something like Editor to create an edit request and submit it to the server.
2. Use your own Ajax request which will submit the changed value to the server.

I would argue though that since those values are computed, they shouldn't really be saved on the server since they can just be recomputed when needed. Storing them is storing redundant information.

Allan

classic12

Whats the syntax to do the editor request.

I have an editor created.

tableInvoices - goto edit mode - exit edit mode and post.

The database is already existing and other programmes need the calculated columns.

Cheers

Steve Warby

classic12

Eventually got there....

editorInvoices.edit( currentSelectedInvoiceIndex, false ).submit();

Cheers

Steve Warby