European-style numbers (1.234.567,89) in grids

From pmusers
Jump to: navigation, search

ProcessMaker is designed to use the . (dot) as the decimal separator and not have a thousands separator in its numbers. In order for formulas and the grid functions (sum and average) to work correctly, numbers need to be entered in text fields in the format 1234567.89 because this is the format used by JavaScript.

This example shows how to use custom JavaScript to provide the same functionality as formulas and functions in a grid which uses European-style numbers like 1.234.567,89 where the thousands separator is a . (dot) and the decimal separator is , (comma). This example can also be adapted to use other number formats such as 1,234,567.89 where commas are the thousands separator or 1 234 567.89 where spaces are the thousands separator.



Dynaform: Grid with european numbers.json (right click and select Save Link As)
Author: Amos Batto <amos[at]processmaker[dot]com>
Version: 1.0 (2019-03-20)
Tested in: PM 3.3.4 Community in Firefox 60.5 in Debian 9.5



Create the following Dynaform, which contains a grid to calculate the total cost of the purchases:

GridWithMaskedNumbersInDesigner.png

The "purchaseList" grid contains the following fields:

  • Text field with the ID "quantity", which is masked to be an integer in the format 1.234.567,
  • Text field with the ID "price", which is masked to be a decimal number in the format 1.234.567,89,
  • Text field with the ID "shipping", which is masked to be a decimal number in the format 1.234.567,89,
  • Text field with the ID "handling", which is masked to be a decimal number in the format 1.234.567,89,
  • Text field with the ID "subtotal" in "disabled" mode, which will be calculated using JavaScript to implement the formula: (quantity * price) + shipping + handling

JavaScript will also be used to implement the sum function to add up the "subtotal" column and place the result below the column in the format 1.234.567,89 .

Download the file jquery.masknumber.js (right click and select Save Link As) and save it in the public_html folder as explained here.

Then, add jquery.masknumber.js to the External Libs property of the Dynaform:

MaskLibraryFileInExternalLibs.png

Then, add the following JavaScript to the Dynaform:

var gridId = "purchaseList"; //set to ID of grid
var formId = $("form").prop("id");
reGridField = RegExp("^\\["+gridId+"\\]\\[(\\d+)\\]\\[([a-zA-Z0-9_]+)\\]$");

//set the thousands separator in all existing rows when the Dynaform loads:
var nRows = $("#"+gridId).getNumberRows();

for (var i = 1; i <= nRows; i++) {
  $("[id='form["+gridId+"]["+i+"][quantity]']").maskNumber({integer: true, thousands: '.'});
  $("[id='form["+gridId+"]["+i+"][price]']")   .maskNumber({decimal: ',', thousands: '.'});
  $("[id='form["+gridId+"]["+i+"][shipping]']").maskNumber({decimal: ',', thousands: '.'});  
  $("[id='form["+gridId+"]["+i+"][handling]']").maskNumber({decimal: ',', thousands: '.'});  
  
  $("[id='form["+gridId+"]["+i+"][quantity]']").css("text-align", "right");
  $("[id='form["+gridId+"]["+i+"][price]']")   .css("text-align", "right");
  $("[id='form["+gridId+"]["+i+"][shipping]']").css("text-align", "right");  
  $("[id='form["+gridId+"]["+i+"][handling]']").css("text-align", "right");
  $("[id='form["+gridId+"]["+i+"][subtotal]']").css("text-align", "right");
}

//resum the subtotal column when Dynaform loads:
if (nRows) {
  recalculateGrid(nRows); 
}

//set the thousands separator in newly added rows:
$("#"+gridId).onAddRow(function(aNewRow, oGrid, rowIndex) {
  $("[id='form["+gridId+"]["+rowIndex+"][quantity]']").maskNumber({integer: true, thousands: '.'});
  $("[id='form["+gridId+"]["+rowIndex+"][price]']")   .maskNumber({decimal: ',', thousands: '.'});
  $("[id='form["+gridId+"]["+rowIndex+"][shipping]']").maskNumber({decimal: ',', thousands: '.'});  
  $("[id='form["+gridId+"]["+rowIndex+"][handling]']").maskNumber({decimal: ',', thousands: '.'});  
  
  $("[id='form["+gridId+"]["+rowIndex+"][quantity]']").css("text-align", "right");
  $("[id='form["+gridId+"]["+rowIndex+"][price]']")   .css("text-align", "right");
  $("[id='form["+gridId+"]["+rowIndex+"][shipping]']").css("text-align", "right");  
  $("[id='form["+gridId+"]["+rowIndex+"][handling]']").css("text-align", "right");
  $("[id='form["+gridId+"]["+rowIndex+"][subtotal]']").css("text-align", "right");

  recalculateGrid(rowIndex);
});

$("#sum-"+gridId+"-subtotal").css("text-align", "right");


//convert a string like "1.234.567,89" to a float like 1234567.89 
function strToFloat(str) {
  str += ''; //force it to be a string 
  if (str == '') {
    return 0.0;
  }
  return parseFloat( str.replace(/\./g, "").replace(",", ".") );
}

//convert a string like "1.234.567,89" to an integer like 1234567
function strToInt(str) {
  str += ''; //force it to be a string
  if (str == '') {
    return 0;
  }
  return parseInt( str.replace(/\./g, "").replace(",", ".") );
}  

//convert a number like 1234567.89 to a string like "1.234.567,89" 
function numberToStr(num) {
  var parts = num.toString().split(".");
  parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, ".");
  return parts.join(",");
}  

//round floating point number to a specified number of decimal digits
function roundToFixed(_float, _digits){
  var rounder = Math.pow(10, _digits);
  return (Math.round(_float * rounder) / rounder).toFixed(_digits);
}

//sum the subtotal column and place the result below the column
function recalculateGrid(rowNo) {
  var oGrid = $("#"+gridId);
    
  var quantity = strToInt(   oGrid.getValue(rowNo, 1) );
  var price    = strToFloat( oGrid.getValue(rowNo, 2) );
  var shipping = strToFloat( oGrid.getValue(rowNo, 3) );
  var handling = strToFloat( oGrid.getValue(rowNo, 4) );
    
  //set the row's subtotal
  var subtotal = quantity * price + shipping + handling;
  subtotal = numberToStr(roundToFixed(subtotal, 2));
  oGrid.setValue(subtotal, rowNo, 5);
    
  //recalculate the "subtotal" column sum:
  var nRows = $("#"+gridId).getNumberRows();
  var sum = 0.0;
    
  for (var i = 1; i <= nRows; i++) {
    sum += strToFloat( oGrid.getValue(i, 5) );
  }
    
  sum = roundToFixed(sum, 2);
    
  //set the "subtotal" column's summed total:
  $("#sum-"+gridId+"-subtotal").val( numberToStr(sum) );
}

//when a field is changed in the grid, call recalculateGrid() function for the grid row that was changed
$("#"+formId).setOnchange( function(fieldId, newVal, oldVal) {
  var aMatch = fieldId.match(reGridField);
  
  if (aMatch) {
    var rowNo = aMatch[1];
    recalculateGrid(rowNo);
  }
});


When the user fills the form, it will display the subtotals and the total underneath the "subtotal" column in European style numbers:

GridWithMaskedNumbersInUse.png

To use this example with American-style numbers, change the code from:

  $("[id='form["+gridId+"]["+rowIndex+"][fieldId]']").maskNumber({integer: true, thousands: '.'}); //for integers
  $("[id='form["+gridId+"]["+rowIndex+"][fieldId]']").maskNumber({decimal: ',', thousands: '.'});  //for decimal numbers

To:

  $("[id='form["+gridId+"]["+rowIndex+"][fieldId]']").maskNumber({integer: true, thousands: ','}); //for integers
  $("[id='form["+gridId+"]["+rowIndex+"][fieldId]']").maskNumber({decimal: '.', thousands: ','});  //for decimal numbers

Where fieldId is changed to the IDs of fields inside the grid.