Add a formula to a control

In a Microsoft Office InfoPath 2007 form template, you can use a formula to create a new value for a field and display that value in a control bound to that field. A formula is an XPath expression made up of values, fields or groups, functions, and operators used to calculate and display other values.

In most cases, you will insert a formula by using the Insert Formula dialog box, which is accessed in the properties of the field or control. This dialog box allows you to correctly insert fields and functions into the formula and check whether the formula is correct. If you are familiar with XPath, you can edit the XPath expression for the formula in this dialog box.

In this article

What is a formula?

Add a formula to a control

Edit the XPath expression for a formula

What is a formula?

A formula is an XPath expression made up of values, fields or groups, functions, and operators used to calculate and display other values. Formulas can be used to do the following tasks:

  • Calculate mathematical values from values that you specify when designing the form template or values that users enter into controls when they fill out forms based on your form template..

  • Display specific dates and times.

  • Display the values that users enter into one control in another control.

  • Set the default value of a field or control.

  • Run a rule based on a value calculated by using a formula.

Each XPath expression that is used in a formula is a combination of values, functions, and operators that evaluates to a single value. A formula can contain several expressions. You can think of a formula as a sentence made up of one or more phrases, with each phrase representing one expression in the formula.

The following illustration shows the relationship between a formula and an expression.

A formula is made up of one or more expressions. An expression is any combination of operators, field names, functions, literals, and constants that evaluates to a single value.

A function is an expression that returns a value based on the results of a calculation. The values used in the functions are called arguments. You can use the standard XPath 1.0 functions that are included in InfoPath, as well as some InfoPath-specific functions. Find links to more information about InfoPath functions in the See also section.

Top of Page

Add a formula to a control

  1. Double-click the control in which you want to create the formula.

  2. Click the Data tab.

  3. Click Insert Formula Formula button .

    Note: To create the formula by using the field's properties, right-click the field in the Data Source task pane, and then click Properties on the shortcut menu.

  4. To insert a field or group into the formula, click Insert Field or Group, and then click the field or group in the Select a Field or Group dialog box.

  5. To insert a function into the formula, click Insert Function, and then select the function in the Insert Function dialog box.

    Tip: If the function requires parameters, select the function in the Insert Function dialog box, click OK, and then, in the Formula box in the Control Properties dialog box, double-click the function that you added, and click a field or group. Find links to more information about functions in the See also section.

  6. To insert a value or mathematical operator into the formula, type the value or the symbol for the mathematical operation in the Formula box.

    List of mathematical operations

    Operation

    Symbol

    Add

    +

    Subtract

    -

    Multiply

    *

    Divide

    /

    Note: If your formula uses the division (/) operator, make sure that there is a space before and after the division operator. If the division operator does not have a space before and after it, InfoPath may interpret '/' as a separator for XPath location steps rather than as a division operator.

    Tip: Mathematical formulas usually depend on integer or decimal values as arguments. To avoid blank values in your formula, select the Treat blank values as zero check box in the Advanced category in the Form Options dialog box.

  7. To check the formula for the correct syntax, in the Insert Formula dialog box, click Verify Formula.

    My formula contains errors

    Click Show Details in the Microsoft Office InfoPath dialog box to see the errors in the formula. The following are some suggestions for resolving these errors:

    • If you are using a function in your formula, verify that you are using the correct arguments for the function. Some functions require fields or groups while other functions require specified values as arguments. Find links to more information about functions in the See also section.

    • Delete and retype your formula to make sure that it is correctly typed.

  8. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page

Edit the XPath expression for a formula

If you are familiar with XPath, you can edit the XPath expression for a formula directly in the Insert Formula dialog box. You can also edit the XPath expression by using the control's properties (for text boxes, rich text boxes, and date pickers) or the field's properties.

  1. Double-click the control that contains the expression that you want to edit.

  2. Click the Data tab.

  3. Click Insert Formula Formula button .

    Note: To edit the XPath expression by using the field's properties, right-click the field in the Data Source task pane, and then click Properties on the shortcut menu.

  4. In the Insert Formula dialog box, select the Edit XPath (advanced) check box.

    The formula changes to the XPath expression version of the formula.

  5. In the Formula box, edit the XPath expression for the formula. To include fields, groups, or functions in the expression, click Insert Field or Group or Insert Function.

  6. To check the formula for the correct syntax, in the Insert Formula dialog box, click Verify Formula.

    My formula contains errors

    Click Show Details in the Microsoft Office InfoPath dialog box to see the errors in the formula. The following are some suggestions for resolving these errors:

    • If you are using a function in your formula, verify that you are using the correct arguments for the function. Some functions require fields or groups while other functions require specified values as parameters. Find links to more information about functions in the See also section.

    • Delete and retype your formula to make sure that it is correctly typed.

  7. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×