About formulas and functions
Formulas are made up of functions, references to other fields or groups, static values (such as a number), and mathematical operators. You can use formulas to calculate mathematical values, display dates and times, reference fields in a form, or perform string operations such as combining multiple fields into one field. A formula will always return a single result.
Formulas are stored in fields and are displayed in the controls that are bound to those fields. You can create a formula by using the control's properties (for text boxes, rich text boxes, and date pickers) or the field's properties.
Functions, such as sum and avg, are a crucial component of formulas, and you can use functions wherever you can insert a formula. Most functions require arguments to operate on, and some functions require more than one argument. All functions return a value. For example, in a function that sums the values in a column, the argument is the repeating field to which the controls in the column are bound. The function adds and returns the values of all of those fields. Depending on the function, an argument can be a static value or a field, or a static value or field that is dynamically selected at run time based on another value.
The following is an example of a formula that calculates the average of the values in a repeating field, then calculates the sum of the values in a different repeating field, and then adds the sum and the average together:
avg(field3) + sum(field4)
In this example, there are two functions, avg and sum, each of which requires one argument (field3 and field4, respectively). The formula returns the results of the functions added together.
When you work with formulas, keep the following in mind:
- If you display a formula in a text box, rich text box, or date picker, make the control read-only to prevent users from typing over the result of the formula.
- In the control's or field's properties, define whether to update the results of a formula whenever the values in the formula change or whether to show only the initial result of a formula.
- When arguments are used in functions, all of the values in a repeating field will be evaluated. For example, to sum a column in a repeating table, sum the field to which the column is bound: sum(field1).
- Functions can be nested within other functions, in which case the result of one function provides the argument for another function.
- Because mathematical formulas usually depend on integer or decimal values as arguments, make sure that the Treat blank values as zero check box is selected on the General tab in the Form Options dialog box.
- Formulas in Microsoft Office InfoPath 2003 are based on XPath expressions. If you are familiar with XPath, you can edit the XPath expression directly.
- Formulas refer to fields and groups in the form, from the context of where the fields and groups are located. A field referencing itself uses a period (.) instead of its own name. For example, a formula that uses the Name function to return its own field name looks like this: name(.)
The following tables list the functions that are available in InfoPath.
Date and time functions
| Function | Description | Example |
|---|---|---|
| now | Returns the current date and time. No argument is required. | now() |
| today | Returns the current date. No argument is required. | today() |
Field functions
| Function | Description | Example |
|---|---|---|
| name | Returns the name of the specified field or group, as well as its namespace prefix. If the argument is a set of fields or groups, it returns the name and prefix of the first field or group. | name(field1) |
| position | Returns the index (or position) of the field or group with respect to the other fields and groups in the same group. | position() |
| count | Counts the number of instances of the specified field or group. | count (field1) |
| id | Returns the fields or groups associated with the specified ID. This function is not available in the Insert Function dialog box; it must be typed in the Formula box in the Insert Formula dialog box. | id(field1) |
| last | Returns the value of the last occurrence of the field in the current context. | field1[last()] |
| local-name | Returns the name of the specified field or group. If the argument is a set of fields or groups, it returns the name of the first field or group. | local-name(field1) |
| namespace-uri | Returns the full namespace of the specified field or group. If the argument is a set of fields or groups, it returns the namespace of the first field or group. | namespace-uri(field1) |
Math functions
| Function | Description | Example |
|---|---|---|
| avg | Calculates the average value of all of the numerical values in the argument. | avg(field1) |
| Boolean | Returns true if the argument is not null, is a positive or negative number, or is a non-empty string. Otherwise, returns false. Returns false if the argument is null, is zero or an NaN value, or is an empty string. | boolean(field1) |
| ceiling | Returns the smallest integer that is not less than the argument (rounding up). | ceiling(field1) |
| eval | Returns the values of a set of fields. The first argument defines the set of fields; the second argument defines the expression to calculate for the set of fields. Usually, the eval function is nested within a function that operates on a set of fields, such as sum or avg. | sum(eval(group2, "field1 * field2")) |
| false | Returns false. | false() |
| floor | Returns the largest integer that is not greater than the argument (rounding down). | floor(field1) |
| min | Returns the smallest number in the argument. | min(field1) |
| not | If the argument is false or null, returns true. If the argument is true or not null, returns false. | not(field1) |
| number | Converts the argument into a number. | number(field1) |
| nz | Returns the value of the specified field. If the field is blank, it returns zero (0). | nz(field1) |
| round | Rounds the argument to the nearest integer. | round(field1) |
| sum | Calculates the sum of the arguments. | sum(field1) |
| true | Returns true. | true() |
| max | Returns the largest number in the argument. | max(field1) |
Text functions
| Function | Description | Example |
|---|---|---|
| contains | If the first argument contains the second argument, returns true. Otherwise, it returns false. | contains(field1, "city") |
| concat | Combines the value of fields with other fields or text strings. | concat(field1," of ",field2) |
| normalize-space | Removes the white space from the argument. | normalize-space(field1) |
| starts-with | If the first argument starts with the second argument, returns true. Otherwise, it returns false. | starts-with(field1, "B") |
| string | Converts the argument to a text string. | string(field1) |
| string-length | Returns the number of characters in the argument. | string-length(field1) |
| substring | Returns a specific part of a text string. The first argument defines the text string, the second argument defines the starting position, and the third argument defines how many characters to include. | substring(field1, 3,5) |
| substring-after | Returns a specific part of a text string. The first argument defines the text string, and the second argument defines what to look for in the text string. The function returns the text that follows the first occurrence of the second argument. | substring(field1, "country") |
| substring-before | Returns a specific part of a text string. The first argument defines the text string, and the second argument defines what to look for in the text string. The function returns the text that precedes the first occurrence of the second argument. | substring-before(field1, "the end") |
| translate | Returns a converted text string. The first argument defines the text string, the second argument defines the characters to change in the text string, and the third argument defines what to change the characters to. The characters in the second argument are converted to the characters in the third argument's matching position. | translate(field1, "()","[]") |
Note The information in this topic may not apply if you are working with a form designed using Microsoft Office InfoPath 2003 without the service pack installed. Learn more