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 (field: An element or attribute in the data source that can contain data. If the field is an element, it can contain attribute fields. Fields store the data that is entered into controls.) and are displayed in the controls that are bound (bind: To connect a control to a field or group in the data source so that data entered into the control is saved. When a control is unbound, it is not connected to a field or group, and so data entered into the control will not be saved.) 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 (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) 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(.)

 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.

 
 
Applies to:
InfoPath 2003