Table calculations are used to perform calculations about a table in an event, profile or database form. Table calculations are only used to perform calculations about data within the table and should not be part of the table. This article covers:
For more information about the types of functions that can be used in table calculations (including aggregating functions), refer to our article on Using Functions.
Types of table calculations
These can be used to calculate numeric values, using aggregate functions, from data entered by a person into columns in a table in the form. For example, a table calculation field could use a formula that returns the average of all values entered into a table column.
Table option calculations
These can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return a result in option format. For example, a table option calculation field could use a formula that returns an option result of "Pass" when the sum of numeric entries into a column is higher than 60 or "Fail" when it does not.
Table text calculations
These can be used to calculate values, using aggregate functions, from data entered by a user into a table in the form and return the results in text format. For example, a table text calculation field could use a formula that returns a result of “All criteria met” if the data entered into an option field column meets certain criteria.
Table date calculations
These can be used to calculate values, using aggregate functions, from date data entered by a user into a table in the form and return the results in date format. For example, a table date calculation field could use a formula that returns the newest date from a column of date entries.
Table duration calculations
These can be used to calculate values, using aggregate functions, from duration data entered by a user into a table in the form and return the results in duration format. For example, a table duration calculation field could use a formula that returns the sum of all duration entries in a column.
Table calculation syntax
In the screenshot above, a table is used to collect information about a strength training session. The field below the table called Total Load is a Table calculation used to calculate the total training load for the session. It use the sum aggregation and reference the relevant field name (Load) to calculate the total value for the column. Note that this calculation is not part of the table.
This is an example of a simple table calculation that combines an aggregate function and a field name reference. However, table calculations can be more complex if necessary. For example, you can create a table calculation that adds all Reps that were done at over 70% of Body Mass (not shown in the screenshot) by selecting sumifpositive as the aggregate type and writing the calculation’s formula as follows:
if(Weight > (0.7 * Body Mass), Reps, -1)
In this example, Body Mass is a field outside the table. The formula will return either the Reps or -1 for each table row depending on whether the condition is met for that row. Then, because we are only adding up the positive values, the rows where the condition was not met will be ignored.
A useful trick is the use of the row function. When used in a calculation within a table, the row function simply returns the row number but by targeting specific rows it opens up a great range of possibilities. For example, you could use the sumifpositive aggregate in a table calculation to discard the Load values in the first two rows and sum all other positive values for Load with the following formula:
if(row() > 2, Load, -1)
In the case of a medical workflow, an injury treatment record might have a table to record each treatment for a particular injury. If there are a large number of table rows within a record, this may generate a very large report which may not allow important information to be easily detected. An alternative option is to use table calculations to return the most recent values for selected fields with the lastenteredvalue aggregation type. A Table date calculation could be used to return the latest treatment date, Table text calculations could return the latest treatment notes and a Table option calculation could return the latest injury status for the person. These table calculations could be selected for the report, rather than the table fields themselves, to provide an up-to-date overview of each injury.
As with option calculations, it is necessary to set the options of the Table option calculation exactly the same as the options of the fields being referenced.
Steps to build a table calculation
- Log into the builder interface.
- Select the Event forms tool.
- Create an event form and add the fields you wish to use, choosing the Table entry mode option for each field.
- Select the type of table calculation needed on the Add question tab and add this field, ensuring the calculation is not part of the table.
- Select an appropriate aggregate function for the calculation from the drop-down list of aggregate functions.
- Write the calculation formula you wish to use in the Calculation tab.
- Click the Preview button and test whether the calculation is working as expected.
- Exit preview mode and either continue working on the form or save the form.