Reformat numbers in grid to sum column

From pmusers
Jump to: navigation, search

ProcessMaker cannot correctly sum a grid column, if it doesn't use the proper number format, so strings like $837.91, €263, 9,823.05 and <374.03 will not be summed correctly. JavaScript can be added to the DynaForm to strip out non-numeric characters like $, , , (comma) and < in order to be able to sum the grid column. This is especially useful if a mask has been added to the grid field to insert thousand separators like commas, periods or spaces.



DynaForm: Reformat numbers to sum grid column.json (right click and select Save link as)
Author: Amos Batto (amos@processmaker.com)
Version: 1.0 (2018-05-08)
Tested in: ProcessMaker 3.2.1 Community in Debian 8.4 with Firefox 51


Create the following DynaForm which has a grid with an "amount" field which needs to be summed:

GridWithFieldToSumInDesigner.png

Then, add the following JavaScript to the DynaForm:

var gridId = "orderList";    //set to the ID of the grid
var fieldToSumId = "amount"; //set to the ID of the grid field to sum
var fieldToSumColNo = 2;     //set to the column number of the grid field to sum

var formId = $("form").prop("id");
var reFieldToSumId = new RegExp("\\["+gridId+"\\]\\[\\d+\\]\\["+fieldToSumId+"\\]");

function roundToFixed(_float, _digits){
  var rounder = Math.pow(10, _digits);
  return (Math.round(_float * rounder) / rounder).toFixed(_digits);
}

function stripNonNumeric(str){
   str += ""; //force str to be a string
   var rgx = /^\d|\.|-$/;
   var out = "";
   for (var i = 0; i < str.length; i++) {
      if (rgx.test(str.charAt(i))) {
         if (!((str.charAt(i) == '.' && out.indexOf('.') != -1) ||
            (str.charAt(i) == '-' && out.length != 0 )))
         {
            out += str.charAt(i);
         }
      }
   }
   return out;
}

function sumGridColumn(gridId, gridFieldId, gridFieldColNo) {
  var oGrid = $("#"+gridId);
  var rowCount = oGrid.getNumberRows();
  var sum = 0.00;
    
  for (var i = 1; i <= rowCount; i++) {
    var val = oGrid.getValue(i, gridFieldColNo);
    var amt = val.trim() == '' ? 0 : parseFloat(stripNonNumeric(val));
      
    if (isNaN(amt)) {
      alert("Bad Number '"+val+"' in row "+i+".");
    }
    else {
      sum += parseFloat(amt);
    }  
  }
  
  sum = roundToFixed(sum, 2);
  //set the column's sum at the bottom of the grid:
  $("#sum-"+gridId+"-"+gridFieldId).val(sum); 
}

//when a value in the form is changed, check if it is the grid field to sum:
$("#"+formId).setOnchange( function(fieldId, newVal, oldVal) {
  //if the field to sum in the grid was changed, then sum its grid column
  if (fieldId.match(reFieldToSumId)) {
    sumGridColumn(gridId, fieldToSumId, fieldToSumColNo);
  }  
});

//sum the grid's column when a row is deleted in the grid:
$("#"+gridId).onDeleteRow( function(oGrid, aRow, rowIndex) {
  //Set timer to sum the grid column after the row has finished being deleted:
  setTimeout(function(){ sumGridColumn(gridId, fieldtoSumId, fieldToSumColNo); }, 100);
});

Set the gridId, fieldToSumId and fieldToSumColNo variables to match the grid and grid field to sum in your DynaForm.

When the user fills in the grid, the JavaScript will strip out the non-numeric characters in the "amount" grid field so that it can correctly sum the column:

summingGridColumnNonNumeric.png

Note: If needing to sum a grid field which uses numbers in the European style 99.999.999,99, where the period is the thousands separator and the comma is the decimal sign, then change, the above code from:

var amt = val.trim() == '' ? 0 : parseFloat(stripNonNumeric(val));

To:

var amt = val.replace(".", ""); //strip out periods
amt = amt.replace(",", ".");    //convert comma to period 
amt = val.trim() == '' ? 0 : parseFloat(stripNonNumeric(val));