Formula in grid field based on column summary

From pmusers
Jump to: navigation, search

Formulas in grid fields can only access the other fields within the same grid row. This example shows how to make a calculation in a grid field which is based on a value in a grid column summary, which is the sum or average of the entire grid column. This example also shows how to force formulas in grid fields and column summaries to be set to a fixed number of decimal digits.


DynaForm: Formula in grid based on column summary.json (right click and select Save link as)
Author: Amos Batto <amos[at]processmaker[dot]com>
Version: 1.0 (2019-04-02)
Tested in: ProcessMaker 3.3.6 Community in Debian 9.5 with Firefox 60


The following Dynaform is used to create a list of budget items in a grid:

GridWithFormulaBasedOnSummaryInDesigner.png

The "budgetItems" grid contains the following fields:

  • A text field with the ID "item",
  • A text field with the ID "quantity" which has a validate property of ^\d*$ to only accept integers,
  • A text field with the ID "price" which has a validate property of ^\d*(\.\d{1,2})?$ to only accept integers and numbers with 1 or 2 decimal digits,
  • A text field with the ID "subtotal" whose value is calculated with the formula quantity * price and a function of sum. JavaScript will be used to round the value in this field to two decimal digits.
  • A text field with the ID "percentOfTotal" whose value will be calculated with JavaScript as rowSubtotal / total, where the total is the sum of the "subtotal" column. JavaScript will be used to round the value in this field to two decimal digits.

To make the Dynaform calculate the value of the "percentOfTotal" field each time the user changes a value in the "budgetItems" grid, add the following JavaScript to the Dynaform:

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

var gridId = "budgetItems";  //set to grid's ID
var subtotalId = 'subtotal'; //set to the ID of the "subtotal" grid field
var subtotalColNo = 4;       //set to the column number of the "subtotal" grid field
var percentOfTotalColNo = 5; //set to the column number of the "percentOfTotal" grid field


var formId = $("form").prop("id");
var reGridFieldChanged = RegExp("^\\["+gridId+"\\]\\[(\\d+)\\]\\[(\\w+)\\]$");

//execute when value in form changes:
$("#"+formId).setOnchange( function(fieldId, newVal, oldVal) {
  
  //if a field changed inside the grid, then recalculate the row's "percentOfTotal" field:
  var aMatches = fieldId.match(reGridFieldChanged);

  if (aMatches) {
    var rowNo = aMatches[1];
    var gridFieldId = aMatches[2];
    
    recalculateGrid();
  }
});

recalculateGrid(); //execute when Dynaform loads

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


//function to recalculate the percentOfTotal in every row of grid
function recalculateGrid() {
  var oGrid = $("#"+gridId);
  var aGridVals = oGrid.getValue();
  var totalRows = oGrid.getNumberRows();
  var total = parseFloat(oGrid.getSummary(subtotalId));
  
  for (var i = 1; i <= totalRows; i++) {
    
    //subtract 1 because counting starts from 0, not 1.
    var rowSubtotal = aGridVals[i-1][subtotalColNo-1];
    
    var percent = roundToFixed(rowSubtotal / total * 100, 1) + "%";
    oGrid.setValue(percent, i, percentOfTotalColNo);
    
    //overwrite the "subtotal" value to have two decimal digits:
    oGrid.setValue( roundToFixed(rowSubtotal, 2), i, subtotalColNo);
  }
  
  //make sure that the "subtotal" summary value has two decimal digits:
  var totalRounded = roundToFixed(total, 2);
  $("#sum-"+gridId+"-"+subtotalId).val(totalRounded);
}

When the Dynaform loads and when the user enters a value in the grid, the value of the "percentOfTotal" field is recalculated in every grid row.

FilledGridWithFormulaBasedOnSummary.png

Also notice that the value of the subtotal field always has 2 decimal digits, regardless of whether the price has 0, 1, 2 or 4 decimal digits. The "price" field flags a price with 4 decimal digits as a bad price, but the grid still correctly calculates its percentage of the total.