GoFormz is a powerful tool that can be configured to meet your specific requirements for collecting data, whether from a mobile device in the field or a desktop computer in the office. One of our most popular features is Calculated Fields, which provides anything from simple addition or multiplication to complex conditional logic right in your form.
In their base form, calculations are entered as text strings in the Default Value for a field or cell in a table. There are two basic calculation formats, depending upon whether or not your fields are in a table or they are independent fields. Essentially, the text string syntax is very similar to that of popular spreadsheet programs, but we have to do a few extra things since we support both table and independent field types.
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 Default Value for Field 3 (in the Properties pane for Field 3 in the template editor) you would type the following:
=[Field 1] * [Field 2]
Addition would be: =[Field 1] + [Field 2]
Please note the use of square parentheses to bound the names of the fields.
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]!” Please 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 a sample syntax for a calculation that multiplies the Price in row 1 by the Qty in row 1:
=[Equipment]! * [Equipment]!
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:
=[Equipment]! * [Equipment]!
And so on. Note that the row numbers have changed from “1” to “2”.
Note, in tables you find the Default Value by clicking on the Columns property in the Properties window for the table you are working on, then click the "..." button.
You will see the Columns pop-up menu. Click on the column you want to have the result in and, when you scroll to the bottom of the properties for that column you will see Default Values for each of the rows in that column.
Just paste the calculation string in each Default Value, but make sure the column# and row# are changed so that each row is using the right cells for the calculation.
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.
As mentioned before, calculations are extremely powerful and can be used in a variety of ways within your GoFormz account. To learn more, check out 'Calculated Fields - Advanced'.