How to display a running total of an amount entered into the table?

Glyndwr · August 2020

http://live.datatables.net/dalogaci/1/edit

I have an amount of money to be dispersed. I want to provide a running total of the amount entered into the table please so I can warn when the total to be dispersed has been reached or passed.

Kind regards,

Glyn

colin · August 2020

You would use column().data() whenever the value is changed, and just add up the values in the input elements. I suspect that would work.

Colin

Glyndwr · August 2020

Thanks Colin,
This is what I did:

var sum = 0.0;
// this gets each node (cell) in the final column:
memDispTable.columns(4).nodes().to$()[0].forEach(function (item) {
// see if the display value is a number (i.e. not blank):
var amt = parseFloat($('input', item ).val());
if (!isNaN(amt)) {
sum += amt;
}

});
// round and display to 2 decimal places:
sum = (Math.round((sum + Number.EPSILON) * 100) / 100).toFixed(2);
$('#showDispRem').val(sum);

if (parseFloat($('#showDispRem').val()) > parseFloat($('#showDisp').val())){
    $("#showDispRem").css("background-color","#ff9999");
}

Glyndwr · August 2020

How would I populate another column with a calculated amount please? Foe example populate column 6 with column 4 divided by 2. I tried:

  memDispTable.columns(6).$('input').val(memDispTable.columns(4).$('input')/2);

In my original:

   var sum = 0.0;
// this gets each node (cell) in the final column:
memDispTable.columns(4).nodes().to$()[0].forEach(function (item) {
    // see if the display value is a number (i.e. not blank):
    var amt = parseFloat($('input', item ).val());
    //console.log(amt);
    if (!isNaN(amt)) {
        sum += amt;
                   // Populate column 6 with amt/2
    }
});
// round and display to 2 decimal places:
sum = (Math.round((sum + Number.EPSILON) * 100) / 100).toFixed(2);
$('#showDispRem').val(sum);

if (parseFloat($('#showDispRem').val()) > parseFloat($('#showDisp').val())){
    $("#showDispRem").css("background-color","#ff9999");
}

colin · August 2020

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

Glyndwr · August 2020

http://live.datatables.net/kinikaje/1/edit

I have tied to get something like my code in the above.

I am iterating through rows of a DataTable to pickup the name of each person and the total that has been populated into the Total column (i.e., is a calculated field based on Amount entered and hours if they are entered, if no hours are entered then it is the same as the Amount entered). When I iterate through each row the Total column is not being returned for the correct row. An when I sort the Total is applied to a different row in the iteration (the amount is still on the correct row i.e., it stays with the correct person and does not move to a different person on the page).

This is my code as it stand now:

function memDispTable() {
//Show DataTable

if ( $.fn.dataTable.isDataTable( '#memDispTable' ) ) {
    memDispTable = $('#memDispTable').DataTable();
}
else {
    memDispTable = $('#memDispTable').DataTable( {

        "info":     false,
        "order": [[ 1, "asc" ], [ 2, "asc" ], [ 2, "asc" ]],
        dom: 'Bfrtip',
//          buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
        buttons: [
                  {
                      extend: 'copy',
                      exportOptions: {
                          columns: [  1, 2, 3, 4, 5, 6, 7 ]
                      }
                  },
                  {
                      extend: 'csv',
                      exportOptions: {
                          columns: [ 1, 2, 3, 4, 5, 6, 7  ]
                      }
                  },
                  {
                      extend: 'excel',
                      exportOptions: {
                          columns: [ 1, 2, 3, 4, 5, 6, 7  ]
                      }
                  },
                  {
                      extend: 'pdf',
                      exportOptions: {
                          columns: [ 1, 2, 3, 4, 5, 6, 7  ]
                      }
                  },
                  {
                      extend: 'print',
                      exportOptions: {
                          columns: [ 1, 2, 3, 4, 5, 6, 7  ]
                      }
                  },
              ],
        columns: [
              {data: 'cdId',
                  visible: false,
                  searchable: false},
              {data: 'section',
                    defaultContent: ""},
              {data: 'scoutNumber',
                    defaultContent: ""},
              {data: 'cdSurname',
                    defaultContent: ""},
              {data: 'cdFirstName',
                    defaultContent: ""},
              {data: null,
                  className: "center",
                  defaultContent: '<input class="ilValue" type="number" min="0" max="99999.99" step=".01" placeholder="0.00" onchange="doSum()">'
              },
              {data: null,
                  className: "center",
                  defaultContent: '<input class="ilValue" type="time" placeholder="0:00" onchange="doSum()">'

              },
              {data: null,
                  className: "center",
                  defaultContent: '<input class="ilValue" type="number" min="0" max="99999.99" step=".01" placeholder="0.00" disabled>'
              },
         ],
         columnDefs: [
             {'width': '80px', 'targets': [2, 5, 6, 7]},
         ],
    });


    $('#memDispTable tbody').on( 'click', '#disperseBtn', function () {
        $('#showSelectToForm').hide();
        $('#dispersementForm').show();

        $('#ajaxGetUserServletResponse1').text('');
        $('#ajaxGetUserServletResponse2').text('');
        $('#ajaxGetUserServletResponse1').text('');
        $('#ajaxGetUserServletResponse2').text('');
        $('#ajaxGetUserServletResponse5').text('');
        $('#ajaxGetUserServletResponse6').text('');
        $('#ajaxGetUserServletResponse7').text('');
        $('#ajaxGetUserServletResponse8').text('');
        $('#ajaxGetUserServletResponse9').text('');
        $('#ajaxGetUserServletResponse10').text('');
        $('#ajaxGetUserServletResponse11').text('');
        $('#ajaxGetUserServletResponse12').text('');

        var data = memDispTable.row( $(this).parents('tr') ).data();
        sessionStorage.setItem('ssToTiecId', data.tiecId);
//          
        $("#showRecCategory").val(data.tcCategory);
        $("#showRecIEName").val(data.tiecName);
        $("#showRecIEAmount").val(data.total);

    });
}
$('.table').css({"width":"100%"});

//Get Income and Expenditure Type List
$.ajax({
    type: "POST",
    url: "TrMemDispListView",
    cache: false,
    data : {
        ssAccountLevel : sessionStorage.getItem('ssAccountLevel'),
        ssAccountID : sessionStorage.getItem('ssAccountID'),
        ssGroupID : sessionStorage.getItem('ssGroupID'),
    },
    //Loading spinner
    beforeSend: function() {
        $('#loader2').show();
    },
    complete: function(){
        $('#loader2').hide();
    },
})
.fail (function(jqXHR, textStatus, errorThrown) {
//  alert(jqXHR.responseText);
    if(jqXHR.responseText.includes('No members')){
        $('#ajaxGetUserServletResponse6').text('No members.');
    }else{
        $('#ajaxGetUserServletResponse6').text('Error getting members.');
    }
})
.done(function(responseJson1){
    // JSON response to populate the activities table
    dataType: "json";

//      alert(JSON.stringify(responseJson1a));
    memDispTable.clear();
    memDispTable.rows.add(responseJson1).draw();

    $('#dispersMemberForm').show();
})
}

function postDispersements() {
//Disperse the amounts to each person

//Iterate through each row
memDispTable.rows().data().each( function ( rowData, index ) {

    var scoutNo = rowData.scoutNumber;
    var surname = rowData.cdSurname;
    var firstName = rowData.cdFirstName;

    var amtNode = memDispTable.cells(index, 7).nodes().to$()[0];//Total column
    var amt = parseFloat($('input', amtNode ).val());

    if (isNaN(amt)) {
      amt = 0.0;
    }
    amt = (Math.floor((amt + Number.EPSILON) * 100) / 100).toFixed(2);

    //There are a large number of records so only log the rows with an amount
    if (amt > 0) {
        console.log("scoutNo: " + scoutNo + " surname: " + surname + " firstName: " + firstName + " amt: " + amt);
    }
});
}

colin · August 2020

Thanks for the test case, can you give steps on what to do, and what you would expect to happen,

Colin

How to display a running total of an amount entered into the table?

How to display a running total of an amount entered into the table?

Answers

Howdy, Stranger!

Categories

DataTables

How to display a running total of an amount entered into the table?

How to display a running total of an amount entered into the table?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables