Create an absence report form using rules
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:
- 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.
- Open the Data Source pane.
- 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.
- 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.
- Double-click on the hoursTaken field to open its Properties dialog box.
- 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
Add a second Rule that has the Condition:
position > 1
Set a field's value: cumulative = number(/my:myFields/my:group1/my:group2[/my:myFields/my:position - 1]/my:cumulative) + .
Add a third Rule that has the Condition:
position <= 1
Set a field's value: cumulative = .
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
- 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.
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 of S.Y.M. Wong-A-Ton. 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, S.Y.M. Wong-A-Ton.
Related InfoPath Articles:
- InfoPath Basics: Understanding Rules in InfoPath
- Using the AVG function in InfoPath
- InfoPath functions with examples
- InfoPath 2010 functions within functions in a formula
- InfoPath 2013 LEFT and RIGHT functions