When writing a calculation, there are two basic formats in which you will follow, depending upon whether you are referencing a table field or an independent field. You will notice that the overall syntax and format of each are very similar to that of popular spreadsheet programs, but we do include a few extra components in order to support each field type.
If your calculations involve independent fields, the calculation syntax is pretty simple. Let's say you had a field, Field 3 that was equal to Field 1 times Field 2, where Field 1, Field 2, and Field 3 are the names you have set for the three fields in your form template. In the Calculation Builder for the Default Value of Field 3 (found in the Properties pane for Field 3 in the Template Editor) you would type the following:
Addition would be:
Note: All field names are represented by [square parentheses] in a calculation. However, table field names are represented using a ! after the [ ] (see below).
Calculations in Tables
If calculations involve fields in a table, the syntax is a little different because we need to define both the table and the cells in the table.
Where in Excel you might multiply B5 by B7, for example, we define the cells as [column#][row#]. We also need to define the table in the calculation string since you might want to multiply a cell in one table by a cell in another table. Tables are defined as “[Table Name]!”
Note: The use of the “!” to denote that this is a table and NOT an independent field.
So, let's say Price was in column 1, Qty was in column 2 and Total was in column 3 of a 10-row table called Equipment. Here is sample syntax for a calculation that multiplies the Price in row 1 by the Qty in row 1:
You would place this string in Default Value 1 in the Total column, which represents the value in row 1 for Total. For row 2 you would place the following in Default Value 2 in the Total column:
And so on.
Note: The row numbers have changed from “1” to “2”.
To find the Default Value property field for Table Columns, simply select the grey bar above the column you would like to edit and scroll down on the Properties Panel to the right of your template.
Alternatively, you can select the table and then select the column you would like to edit at the bottom of the Properties panel when viewing the primary property options for the table.
Writing calculations in Workflows is different from writing calculations in the Template Editor.
Workflows contain steps (including a trigger and actions), so you’ll need to include the step containing the field you want to reference in addition to the field you want to reference.
In a calculation the syntax is formatted like this:
For example, if you wanted to reference a Customer Name field from a trigger step (like Form Completed or Public Form Submitted), you’d enter:
Similarly, if you wanted to reference a Form Name field from an Export Form to PDF step, you’d enter:
An additional difference is that IsIngroup calculations and database functions cannot be used in Workflow calculations.
Summing a column, like a Total column is a little different, but not much. Let’s assume you have a single, independent field that calculates a grand total by summing all the values in the 10 rows of the Total column. You would set the Default Value for that grand total field as follows:
This calculation illustrates that you are summing the values in column 3 of the Equipment table from rows 1 to 10.
There are great examples of all these calculation types in the sample templates that are available in your account. Check out the Sample Work Order and explore the default values in the Parts Used table and the Total Cost field to see examples of the calculations discussed above, or the Hours table to see how you can manage calculations based on time values.