Calculating cells values

Calculating cells values

classic12classic12 Posts: 228Questions: 60Answers: 4

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Allan

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Allan

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    sent private message

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    Hi Allan,

    a screen grab of what is happening.

    www.toolfolks.com/docs/dependencyProblem.mov

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4

    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

  • classic12classic12 Posts: 228Questions: 60Answers: 4
    Answer ✓

    Eventually got there....

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

    Cheers

    Steve Warby

This discussion has been closed.