Table calculations are used to to perform calculations about a table in a form. You ONLY use them to perform calculations about data within a table. They are NOT used as part of the actual table. If you need more information about tables, see the lesson about setting up a Table in a form.
Table calculations are used exclusively to calculate numbers, text and options calculations for columns of data for a specific field type.
The example here shows that table calculations are being used to calculate the number of surgeries as well as the total days in hospital.
Table calculations are very important because they need to be designed to accommodate a variable number of rows of data. The max, mean and average calculations and even the normal calculations will NOT allow you to calculate data in a table correctly. The following steps outline common uses of table calculations.
You can generate Numeric, Option and Text calculations for table columns
Table Calculation: allow you to perform a numeric calculation about field columns (with any number of rows)
This example shows that a table has been built to capture profile data about hospitalisations for an athlete . The hospitalisations table has been set up using a date, single line of text, yes no and a number field (days in hospital).
For this example we want to add up the number of days in hospital.
Click on the Table calculation (because we are performing an equation for a field in a table that is a number)
Set up the Advanced Properties and then select the type of calculation you want to perform using the Aggregrate
The aggregrate is simply the calculation that you want to perform on a specific table field. For this example we want to calculate the total days in hospital so we need to select the "sum" aggregrate.
There a numerous numeric calculations that you can perform on a table row, and if your have any queries about the specific aggregrate functions, please ask us to include more in this manual by leaving a comment in the comments section of this lesson.
Next, ensure that the field (table column) is included in the calculation area (as shown in the image in the step below)
Choose the field that you want the calculation to be performed on
Check the calculation captures the data that you want in the preview page and save any changes.
To run a calculation on the number of surgeries that the athlete has, the Surgery Performed field (as yes no field) in the actual table was set up with scored options
A Table Calculation was then added to the form that used the aggregrate Sum for the field Surgery Performed
The Preview of those table calculations shows that the field Total Surgeries is being summed where Yes =1 and No =0
Table Text calculation, building on the hospitalisations example
Set up the field settings and ensure that the question settings are normal for the table text calculation
Select the field that you want to run the text calculation for and add it or type it into the calculation area
Select the Aggregrate options for a table text calculation
You will have the following options to use on the text field from your table (these are displayed in the image in the step below).
count: will return the number of rows that have been selected (even if there is not data in some of those rows).
customtextsummarise: will summarise the rows of data into one line with no "," in between each response
text summarise will summarise all of the rows of data and place a , in between the each response
first value will take the first value in the table (even if it is blank)
last value will take the last value in the table (even if it is blank
last entered value will take the last entered value on the table column, which may pull data from another row which is not the last one
For this example we want to set up summarise with a "," between each reason so we would use the text summarise aggregrate. However, all of the different options for a text field have been added to show you an example of the fields you can use to get the specific information you need from each table you create.
An example of all of the different types of table text calculations performed on the Reason for Hospitalisation Field
Table Option Calculation's are important for showing different states or returning option based feedback for option fields in a table
To Add an Option calculation about a selection based field in a table click on "Table Option Calculation"
Name the field and ensure its question settings are on normal
Copy the options from the existing option field that you are using in your calculation and click update for the options
Set up the Advanced Properties
Now set the option calculation, which will pull the aggregrate of the lastenteredvalue for the field Injury Status
The preview of the form shows that as the injury status of the athlete progress the availability field (option table calculation for last entered value) updates
Additional Table Calculation (numeric) examples: injury duration and days unavailable
Table calculation using an "If then" equation
The Total duration of injury is also a table calculation using a different data duration field that calculates the last entered value for a different date duration
This table calculation is set to show the last entered value in the Column Date Duration2 ( that calculates out the date difference between the date of first symptoms of the injury, until the date of assessment), but it will only calculate this when the Injury Status field is = "Able to work, not injured/ill".
Regardless of what injury states the athlete has been in, the injury duration table calculation will only calculate when the state is equal to "Able to work, not injured/ill" and it will return the last value for this in the table, as an athlete may have on-going treatment when when they are not injured.
Table Calculations: Example from a resistance training form
The different table calculations used in the resistance training form
More uses for Table Text calculations to show important data for a form, and to be used as a summary field.
Table text calculations can be used in conjunction with table calculations to summarise data from a form and use it as a summary in the sidebar. The image here shows for a bloods form, a Summary field (table text calculation) has been set up to give users a brief overview of the blood test and the result for each measure taken.
To set this up on the builder in the form, a text calculation was added to the table, and then that was used in the table text calculation
The Blood results field in the table was set up as a text calculation (to concatenate (Blood Test, ",",Result)), and this field is set to hidden and default show in tables false so that it doesn't appear on the enter data page or the athlete history page. It is ONLY used to calculate the Summary field.
The Summary Table text calculation was set up to text summarise the Blood Results field. This field's advanced properties were set as to summary field to show through onto the Sidebar (as shown in the image in the picture above).