Form element: Formula

The formula field allows you to use simple or compound formulas to create calculations in forms. Formula fields auto-calculate when a user enters data (like a quantity or price) in fields referenced by a formula.

Formula fields can be used in forms created for purchase requests, quotes, expense reports, swag orders, and more.

Formula reference fields

Any form fields whose values you want to use in a formula need to be added to the form before you can add a formula. Formulas can reference values from numeric, currency, or other formula fields in the form.

Give your reference fields unique titles to make them easy to identify when adding fields to a formula. eg. Total A, Total B, and Total C can be differentiated, rather than three fields all titled Total.

Add a formula field

1. Go into Edit mode on the Form that you want to add the Formula field to.
2. Add any numeric, currency or formula fields that you want to use in your formula.
3. Click on the Formula element under Select elements on the left and drag and drop the Formula element where you want it to appear in the form.
4. Click on the Formula element title or field to edit Formula options.
5. In the Formula pop-up window, add Formula element details.
   • Field name: Enter a title to display above the formula field.
   • Help text: Enter a brief description or explanation to help users understand the field.
   • Formula: Enter a formula using field names and operators. Type [ to see a dropdown list of available numeric, currency or formula fields in the form that you can reference in your formula.
   • Prefix: Enter text to display before the formula result. Eg. $, ¥, €
   • Suffix: Enter text to display after the formula result. Eg. CAD, %, l, oz.
   • Decimal places: Click on the dropdown and select how many decimal places to display, from 0 to 3.
   • Number display: Click on the dropdown and select the number format to use.
6. If your intranet has multiple languages, select another language in the language dropdown in the bottom left. Add a Field name for the language you have selected. Repeat for multiple languages.
7. Click Save.

Example

In the image above, formula fields are being used to calculate the Subtotal, Tax and Total values based on the Quantity entered by the user. Column totals are also displayed at the bottom of each column (optional).

The image below shows the Formula field setup for the Subtotal column shown above.

Add numeric values to dropdown or radio button options

Numeric values can be assigned to dropdown and radio button choices in forms so that they can be used in formulas. This allows the choice a user selects to determine what value is used in a formula that includes that field.

For instance, in the T-shirt order example above, T-shirts made of different materials could cost different amounts. A different numeric value (the T-shirt price) could be assigned for each Material option specified in the dropdown. When a user selects a material from the dropdown choices, the value for that material will be used in the formula.

To assign numeric values to dropdown or radio button choices:

1. While editing a form, add a Radio button or Dropdown element, or find an existing one.
2. Click on the Radio button or Dropdown element in the form to edit it.
3. Add labels for the Radio button or Dropdown Choices if there aren't labels. If you want to make one Choice the default, select the radio button beside it.
4. Toggle the dropdown Use in formula to ON. Reference value fields will display for each Choice.
   
   
    
5. For each Choice, add a numeric value in the Reference value field.
6. Click Save.

With numeric reference values assigned to the different choices, those values will be used if a Dropdown or Radio button element is included in a formula.

Add Formula field to a table

When you add a Formula field to a column in a table, every row added to the table will apply the same formula to that column.

Column total

When you add a Formula field to a table, the additional Formula element option Show total appears in the Formula pop-up window. Select the checkbox Show total if you want column values to be added together and the total displayed at the bottom of the column.

Column totals can be used as fields in formulas outside of the table they are in. When you type [ in a Formula field to see the dropdown list of available fields, column total fields will display as [Column name: Total].

eg. If a column name is Tax, then the column total field will be [Tax: Total].

To learn more about tables in forms, see Insert a table into a Form.

What happens if a formula changes after entries are submitted?

If a formula field is changed after form entries have already been submitted, the original formula field values will stay the same unless a form entry is edited and saved. If a submitted entry is edited and saved after a formula has changed, the formula values in the entry will be recalculated according to the new formula.

When viewing a form entry after a formula has been changed, a warning message will appear "This formula has changed and will be recalculated on save." After editing and saving the entry, this message no longer appears, and the recalculated values display in the entry.