Running total in grid

From pmusers
Jump to: navigation, search

Formulas in grids can't use fields in previous rows in the grid. This example shows how to use JavaScript to create a running total in a grid which is calculated based on the fields in previous rows in the grid.


DynaForm: Running account grid.json (right click and select Save link as)
Author: Amos Batto (amos@processmaker.com)
Version: 1.0 (2018-07-03)
Tested in: ProcessMaker 3.2.1 Community in Debian 8.4 with PHP 5.6.20 and Firefox 51


In a Dynaform, create the following grid whose ID is "accountGrid":

accountGridInDesigner.png

The "accountGrid" grid has three fields with the IDs "credit", "debit" and "balance". The "balance" field is field in "view" mode which is automatically calculated based on the balance in the previous row, plus the "credit" and minus the "debit" in the current row. It would be calculated in this manner:

Row1: balance1 = (0        + credit1) - debit1
Row2: balance2 = (balance1 + credit2) - debit2
Row3: balance3 = (balance2 + credit3) - debit3
Row4: balance4 = (balance3 + credit4) - debit4
summary = balance4

This kind of calculation can easily be done in a spreadsheet program, but formulas in ProcessMaker grids do not allow it.

In order to implement this functionality, add the following JavaScript code to the DynaForm:

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

var gridId = "accountGrid"; //set to grid's ID

//execute when value in form changes:
$( "#"+$("form").prop("id") ).setOnchange( function(fieldId, newVal, oldVal) {
  //check if a field changed inside the grid:
  var aMatches = fieldId.match(/^\[accountGrid\]\[(\d+)\]\[(\w+)\]$/);

  if (aMatches) {
    recalculateGrid();
  }
});

recalculateGrid(); //execute when Dynaform loads

$("#"+gridId).onDeleteRow( function(oGrid, aRow, rowIndex) {
  recalculateGrid();
});

//function to recalculate the value of the balance in every row of grid
function recalculateGrid() {
  var totalRows = $("#"+gridId).getNumberRows();
  var balance = 0.00;
  var sBalance = "0.00";

  for (var i = 1; i <= totalRows; i++) {
    var sCredit = $("#"+gridId).getValue(i, 1);
    var credit = sCredit == '' ? 0.00 : parseFloat(sCredit);
    var sDebit = $("#"+gridId).getValue(i, 2)
    var debit  = sDebit == '' ? 0.00 : parseFloat(sDebit);
    balance = (balance + credit) - debit;
    sBalance = roundToFixed(balance, 2);
    //strips decimal numbers if equal to 0:
    $("#"+gridId).setValue(sBalance, i, 3); 
    //workaround to make sure that always displays two decimal numbers: 
    $("#form\\["+gridId+"\\]\\["+i+"\\]\\[balance\\]").parent().find("span.label-text").html(sBalance);
  }
  $("#sum-"+gridId+"-balance").val(sBalance);
}

This code defines the roundToFixed() function which is used to set the "balance" to exactly two decimal points. The recalculateGrid() function is called when the DynaForm loads, when a value in the grid is changed by the user, and when the user deletes a row in the grid. Its code loops through the entire grid, recalculating the "balance" for each line. The column numbers are hard coded, where column 1 is the "credit" field, column 2 is the "debit" field, and column 3 is the "balance". These numbers need to be changed if the field order is changed in the grid.

Unfortunately, the grid.setValue() function doesn't display two decimal numbers if they are 0. A value like "34.00" will be displayed as "34". To get around this problem, the balance is inserted directly in the <span> where it is displayed in the grid.

When the user fills out the form, it will will always display the two decimal numbers in the "balance" field, no matter whether the "credit" and "debit" fields have zero, one or four decimal numbers.

accountGridInUse.png