​​How to calculate fields in a Repeater control based on criteria?

Last Update: November 15, 2017

This use case describes how to calculate fields’ values in a repeater based on criteria in another field in the same row in a repeater.
For example, suppose you have a repeater to save employee leaves, and you want to calculate the total days taken for leave type = Sick and calculate the total days taken for leave type = Annual, here are the steps on how to do that:

  1. Open the form in design mode.
  2. Design a form that looks likes the following:
    How to calculate fields in a Repeater control based on criteria.jpg
  3. Select txtRowNum control and add a new rule to generate repeater’s row numbers. Please refer to case "How to generate rows numbers automatically in a Repeater control".
  4. Select datStartDate control and add a new rule to calculate the number of days between two dates. Please refer to case "How to calculate the number of days between two dates excluding weekends & holidays".
  5. Add the following rule to drpLeaveType, datStartDate and datEndDate controls:
  6. In the Rules pane, click on the Add icon to add a new rule, the Rule Manager dialog will open.
    How to auto-shifting Panel control in a form2.jpg
  7. Add a new rule at Date1 control as follows:
    A. Change the Rule Name as desired.
    B. Change the Rule Type to Action.
    C. Change the Event Type to onChange. Leave "Execute if event triggered only on this control" unchecked.
    D. In the Action section, type the following script:
         var numberOfDays=getRepeaterData(txtDaysTaken).split(','); //Save the days taken column in the repeater to array.
    var typeOfVacation=getRepeaterData(drpLeaveType).split(','); //Save the leave type column in in the repeater to array.
    var sickCount=0
    var anuuanlCount=0
    for(var i=0;i<typeOfVacation.length;i++){
    if(typeOfVacation[i]=='Sick')
    sickCount+=Number(numberOfDays[i])
    else if(typeOfVacation[i]=='Annual')
    anuuanlCount+=Number(numberOfDays[i])
    }
    setValue(txtTotalSickLeaves,sickCount)
    setValue(txtTotalAnnualLeaves,anuuanlCount)

    E. Click on the Save button to save the rule.
    Note: You can use Assistance Panel to help you adding functions and related parameters, form variables and form controls to Conditions and/or Actions sections.
  8. Test the form by clicking on the Preview button in the View group.