Create an absence report form using rules

Applies to: InfoPath 2003

Use rules to implement logic for calculating leave balances.



You want to create an InfoPath form that can register and report on hours of leave entitlement, hours of leave taken, cumulative hours of leave taken, and hours of leave left (balance) without using code.


Use Rules to implement logic for calculating leave balances.


You can accomplish this functionality as follows:

  1. Design an InfoPath form as shown in figure 1 with one Text Box control called entitlement and a Repeating Table with five fields: one Date Picker control called date and four Text Box controls called hoursTaken, cumulative, balance, and balanceDays, respectively. Your InfoPath form should resemble the form in figure 1.

    Figure 1. The InfoPath form template in design mode.
  2. Open the Data Source pane.
  3. Add a Field (element) called position of type Whole Number (integer) under the myFields node. This field will not be made visible on the form, but will only be used behind the scene.
  4. The Main data source of your form should now resemble the following figure:

    Figure 2. The structure of the Main data source of the form template.
  5. Double-click on the hoursTaken field to open its Properties dialog box.
  6. Click on the Rules... button and add a Rule with the following Action:
    Set a field's value: position = count(ancestor::*[self::my:group2]/preceding-sibling::*) + 1
    By counting all of the preceding siblings of the my:group2 node of the current context node, you can derive the position of the my:group2 node of the current context node in the repeating table. The calculated position is then saved in the position field to be used later in other rules.
  7. Add a second Rule that has the Condition:
    position > 1
    and the Action:
    Set a field's value: cumulative = number(/my:myFields/my:group1/my:group2[/my:myFields/my:position - 1]/my:cumulative) + .
    This rule calculates the current cumulative value by adding the current hours taken to the cumulative value in the previous row.
  8. Add a third Rule that has the Condition:
    position <= 1
    and the Action:
    Set a field's value: cumulative = .
    This rule sets the cumulative value equal to the amount of hours taken if the context node is currently in the first row of the repeating table.
  9. Add a fourth Rule that has the following two Actions:
    Set a field's value: balance = ../../../my:entitlement - ../my:cumulative
    Set a field's value: balanceDays = ../my:balance div 7.5
    Where 7.5 is the amount of hours for one day of work.
  10. Make the cumulative, balance, and balanceDays fields Read-only by double-clicking on each, going to the Display tab on their Properties dialog box, and checking the Read-only check box.

You should now have a fully functional absence report form. Please note that if the hours taken value of previously filled in rows are changed afterwards, recalculation of rows that come before or after that row will not take place. For a total recalculation of all rows you will have to write code.


Related InfoPath Articles:


Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services