InfoPath 2007 - Conditional SUM

REF: http://blogit.create.pt/blogs/miguelisidoro/archive/2008/08/02/InfoPath-2007-_2D00_-Conditional-SUM.aspx


This blog post will show you how to create an expression box in an InfoPath 2007 form whose value is based on the result of an conditional sum. Consider the following InfoPath form template:



The previous image shows a simple expense report. To support the introduction of the expense information, a Repeating Table control is used with three columns:

Expense - Expense Description. A simple Text Box control;
Value - Expense value. A simple Text Box control;
Expense Type - Drop-down List Box control that allows 4 expense types: Food, Land Travel, Air Travel and Parking.
Below the repeating table there are 5 expression boxes that show the total amount of the expense report and the total amount for each expense type. The total amount expression box is based on a simple sum expression and each of the expense type expression boxes are based on conditional sums filtered by the value of the expense type Drop-down List Box control. The expressions used for each expense type are the following:

Expression Box Expression XPath Expression
Expense Total sum(expensevalue) sum(my:accounting/my:expensevalue)
Food Expense Total sum(accounting[expensetype = "Food"]/expensevalue) sum(my:accounting[my:expensetype = "Food"]/my:expensevalue)
Land Travel Expense Total sum(accounting[expensetype = "Land Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Land Travel"]/my:expensevalue)
Air Travel Expense Total sum(accounting[expensetype = "Air Travel"]/expensevalue) sum(my:accounting[my:expensetype = "Air Travel"]/my:expensevalue)
Parking Expense Total sum(accounting[expensetype = "Parking"]/expensevalue) sum(my:accounting[my:expensetype = "Parking"]/my:expensevalue)

As the previous table shows, the conditional sum expressions for each expense type are relatively simple and pretty straightforward for those who are familiarized with XPath, since XPath syntax is used for each expression. This comes as no surprise since the underlying data of the InfoPath form is stored in XML. The following image shows an expense report example filled with some sample values:



Posted: Saturday, August 02, 2008 3:52 PM by misidoro

Calculate the difference between two date picker controls in InfoPath using rules and formulas - no code!

REF: http://www.bizsupportonline.net/infopath2007/calculate-date-difference-infopath-rules-formulas.htm


Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.

Problem
You have an InfoPath form template with two date picker controls and you would like to calculate the difference between the two date picker controls without writing code.

Solution
Use rules, conditions, and the number(), floor(), and substring() functions in formulas to calculate the difference between two date picker controls in InfoPath.

Discussion
You can accomplish this functionality as follows:

Design an InfoPath form template as shown in figure 1 with two Date Picker controls named startDate and endDate, and one Text Box control named difference.

Figure 1. InfoPath form template in Design mode.

The Main data source of the InfoPath form template should resemble the following figure:

Figure 2. The Main data source of the InfoPath form template.
Add the following Rule to the startDate field:
Action: Set a field's value
Field: difference
Value:

(number(substring(../my:endDate, 9, 2)) + floor((153 * (number(substring(../my:endDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:endDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:endDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:endDate, 6, 2))) div 12))) div 400) - 32045) - (number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045)

with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the startDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0

with the following Conditions on the Rule:
startDate is blank or
endDate is blank

Add the following Rule to the endDate field:
Action: Set a field's value
Field: difference
Value:

(number(substring(., 9, 2)) + floor((153 * (number(substring(., 6, 2)) + 12 * (floor((14 - number(substring(., 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) * 365 + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 4) - floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 100) + floor((number(substring(., 1, 4)) + 4800 - (floor((14 - number(substring(., 6, 2))) div 12))) div 400) - 32045) - (number(substring(../my:startDate, 9, 2)) + floor((153 * (number(substring(../my:startDate, 6, 2)) + 12 * (floor((14 - number(substring(../my:startDate, 6, 2))) div 12)) - 3) + 2) div 5) + (number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) * 365 + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 4) - floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 100) + floor((number(substring(../my:startDate, 1, 4)) + 4800 - (floor((14 - number(substring(../my:startDate, 6, 2))) div 12))) div 400) - 32045)

with the following Conditions on the Rule:
startDate is not blank and
endDate is not blank
Add a second Rule to the endDate field with the following settings:
Action: Set a field's value
Field: difference
Value: 0

with the following Conditions on the Rule:
startDate is blank or
endDate is blank
Add the following Rule to the difference field:
Action: Set a field's value
Field: .
Value: 0

with the following Condition on the Rule:
difference does not match pattern Custom Pattern: -{0,1}\d+
You should now have a fully functional InfoPath form that will calculate the difference between the dates soon after you have entered valid dates. This solution also works for InfoPath 2003 form templates and InfoPath 2007 browser-enabled form templates.