Sum fields in multiple grids

From pmusers
Jump to: navigation, search

JavaScript can be used to sum fields located in multiple grids to obtain a grand total. In the following example, there are three grids where the user will enter a score. Javascript code is used to add up the scores in each grid and then insert the total in a "Total Score" field at the bottom of the Dynaform.


Dynaform: Sum fields in Multiple Grids.json (right click and select Save Link As)
Author: Amos Batto <amos[at]processmaker.com>
Version: 1.0 (2019-05-02)
Tested in: ProcessMaker 3.3.8 Community in Debian 9.5 with Firefox 60.5


Create the following Dynaform which contains three grids:

SumFieldsMultipleGrids InDesigner.png

This Dynaform has the following fields:

  • Grid with ID "financialKpaGrid" which contains:
    • Text field with ID "financialDescription"
    • Text field with ID "financialScore"
  • Grid with ID "customerKpaGrid" which contains:
    • Text field with ID "customerDescription"
    • Text field with ID "customerScore"
  • Grid with ID "internalScoreKpaGrid" which contains:
    • Text field with ID "internalDescription"
    • Text field with ID "internalScore"
  • Text field with ID "totalScore"

To sum up the scores entered in each grid, add the following JavaScript to the Dynaform:

//change to match the IDs of your grids and the score fields in the grids:
var re = /^\[(financialKpaGrid|customerKpaGrid|internalProcessKpaGrid)\]\[(\d+)\]\[(financialScore|customerScore|internalScore)\]$/;
var formId  = $("form").prop("id");

function sumTotalScore() {
   var runningTotal = 0;
   var nFinancialRows = $("#financialKpaGrid").getNumberRows();
   var nCustomerRows  = $("#customerKpaGrid").getNumberRows();
   var nInternalRows  = $("#internalProcessKpaGrid").getNumberRows();
  
   for (i = 1; i <= nFinancialRows; i++) {
     //change to match IDs:
     //change parseInt() to parseFloat() if the score is a decimal number:
     var score = parseInt( $("#form\\[financialKpaGrid\\]\\["+i+"\\]\\[financialScore\\]").val() );
     score = isNaN(score) ? 0 : score;
     runningTotal += score;
     
   }
   
   for (i = 1; i <= nCustomerRows; i++) {
     //change to match IDs:
     var score = parseInt( $("#form\\[customerKpaGrid\\]\\["+i+"\\]\\[customerScore\\]").val() );
     score = isNaN(score) ? 0 : score;  
     runningTotal += score;
   }
  
   for (i = 1; i <= nInternalRows; i++) {
     //change to match IDs:
     var score = parseInt( $("#form\\[internalProcessKpaGrid\\]\\["+i+"\\]\\[internalScore\\]").val() );
     score = isNaN(score) ? 0 : score;  
     runningTotal += score;
   }
  
   $("#totalScore").setValue(runningTotal);
}  

//execute when a field changes in the Dynaform:
$("#"+formId).setOnchange( function(fieldId, newVal, oldVal) {  
   var aMatch = fieldId.match(re);

   //if one of the score fields in the grids was changed, then sum the total score:
   if (aMatch) {
       sumTotalScore();
   }    
} );     

sumTotalScore(); //sum score when form loads

//resum the score when a row is deleted:
$("#financialKpaGrid").onDeleteRow( function() {
  sumTotalScore();
} ); 
$("#customerKpaGrid").onDeleteRow( function() {
  sumTotalScore();
} );
$("#internalProcessKpaGrid").onDeleteRow( function() {
  sumTotalScore();
} );

Change the field IDs in the code to match the IDs in your Dynaform.

First, a regular expression is constructed to check if one of the "Score" fields in the grids is changed as explained in the documentation. This regular expression is used by change event handler set by grid.setOnchange(), which is executed when the value in a field is changed in the Dynaform. If a "Score" field is changed, then the function sumTotalScore() will loop through each grid and sum the values in the "Score" fields. parseInt() is used to convert the string entered by the user into a integer and isNaN() is used to assure that the value entered by the user is a valid integer. Finally, the running total is set in the "totalScore" field.

When the Dynaform is used in a case, the "Total Score" field will display the sum of all the "Score" fields in the three grids:

SumFieldsMultipleGrids FilledForm.png