You may need to collect repeating data such as a list of items on your form, or details of students in your class at school. This will typically require the use of a Table field in your form. The Table field in Formplus lets your respondents' input repeating data on your form. You can learn more about the table field in this article
You can carry out calculations in a Table field if you have added fields that support calculation in your Table field. The fields that allow for calculation in a Table field includes - Ratings field - (Star Rating, Scale Rating, Heart Rating), Number Field, Single select field, Checkboxes and Radio Field. You can learn more about form calculation here. Fields allowed to display calculation results for calculation in a Table are - Short text Field and a Number field. This means to display the result of a calculation done in a Table field in another column within the table field, the result display can only be in columns with the Number field type or Short text field type. It is also possible to display the result of a calculation done in a Table field to other supported result fields (Short Text, Number, Labelled Text, Payment fields) outside the table field.
There are different types of calculations that can be performed on your form when collecting repeating data with the table field. We explain some of them in the sections below;
1. Calculating the sum of inputs in a table field column
This is to get the total sum of all values inputted in a table field column. This calculation can be done on all types of calculation supported field in a Table field.
To calculate the sum of a column:
- Select a calculation supported field in a Table field, each of these fields will display the validation section as shown below.
- In the option to ‘Select a calculation to use for this column’, click on the dropdown to display the Sum and Average options. Select the Sum option to get the sum total of inputs in each row in the column.
- A column with the sum option enabled will display “SUM” below its rows on the form and on the responses page/spreadsheet for the form. As each row is being added, the sum displays the total sum of each input in the rows. As shown below:
If you (or your respondents) only need to know the sum of a column without using it further in the form, then you are all done setting up calculating the sum of a column and you can proceed to save your changes. However, if you will need to use the sum for further calculations or other purposes in your form, proceed to the next paragraph.
The sum of a column can be used within other column calculations in the table field, this is better explained in the Calculation between two or more columns in a table field’ section of this article. The sum can also be passed onto another field in your form to be used within other calculations. To pass the sum of a calculation to another field outside the table field.
Open the Calculation modal by clicking on the ‘Add Calculation’ button from the Calculation tab in the table field edit window or by clicking on the ‘Add Calculations’ field from the Advanced Input section of the form fields.
In the fields (‘Select a field to calculate’) section of the calculation modal, you should see the table field you are editing listed, click on it to display the columns then select the column that has the Sum to add it to the Calculator. On the mobile form builder, you will find the column listed in the ‘Select a field to calculate’ dropdown in the format ‘Name of table field - Column name’, for example, ‘Student Details - Score’, click on the column name in the dropdown to add it to the Calculator.
In the Calculator section, you will notice the column has already been added to the calculator input, if you wish, you can add any additional calculation you will like to perform with the column sum to the calculator input, if not, proceed to select the field you will like to display the sum of the column in. Remember that
- Save the calculation you just added and save the changes you have made to the table (if you didn’t save previously). Remember to also save your form if (or when) you are done setting up the form.
2. Calculating the Average of inputs in a Table field column
The steps to get the average of inputs in a column is similar to that of getting the sum of inputs in a column as described above. To get an average of the inputs in a column of your Table field, rather than selecting the SUM option in the Validation section of the column, you should select the Average option from the ‘Select a calculation to use for this column’ dropdown validation option.
Here is an example - In a school, the headteacher has requested for the average score gotten by certain students in all subjects they have taken. If you have entered the score of a student in different subjects and you will like to get the average score of the student in all subjects. You can save a lot of time and enable the average option under the validation section of the score column, provided that the Score column is a calculation supported field.
3. Calculation between two or more columns in a Table field
Aside from the sum and average calculation which can be done on a single column, you can also perform arithmetic operations between columns in a Table field and display the result of the calculation in another Table field column. This is more like performing arithmetic operations between a column cell and another column cell then displaying the result of the arithmetic operation in a different column cell. The column displaying the result should have its field type be as any of the results supported fields in a table, i.e. Short Text and Number field shown here:
If you will still like to perform calculations with the result such as getting the sum or average of the result column, you should make the result column a number field.
Using our previous example of an order form, you will also like your customers to select quantity for the items they have chosen e.g 3 skirts, now you have gone ahead and added a Quantity column and Total Price column in the Table field of your form, because you will like to display the total price of the item selected, e.g total price for 3 skirts. So that the customer can see the total price for the preferred quantity of each selected item.
To perform calculation between columns in a table field:
- First, ensure the column field types are supported fields that can be used in a calculation as stated at the beginning of this article. As a quick reference: Star Rating, Scale Rating, Heart Rating, Number Field, Single Select, Checkboxes and Radio Field, are the supported field types for table field calculations.
- Open the Calculation modal by clicking on the ‘Add Calculation’ button from the Calculation tab in the table field edit window or by clicking on the ‘Add Calculations’ field from the Advanced Input section of the form fields.
- In the fields (‘Select a field to calculate’) section of the calculation modal where all calculation supported fields in your form are listed, you will find the table field in question listed, click on it to display its columns then select the column to add it to the Calculator input. On the mobile form builder, you will find the column listed in the ‘Select a field to calculate’ dropdown in the format ‘Name of table field - Column name’, for example, ‘Order Items - Quantity’, click on the column name in the dropdown to add it to the Calculator.
- Proceed with adding the arithmetic operation you will like and selecting the column(s) you will like to use. In the example we gave above, this will mean you multiply the Item column with the Quantity column and display the result in the Total price column as shown below.
- When you are done adding the calculations as you want, save the calculations. You can also get the sum or average of the column used in displaying the result (In our example, the total price column) as explained in the previous sections above.
4. Calculation between a field in a Table and a field outside a Table
The result gotten from a column in a table field can be used to perform calculations with other calculation supported fields (outside a Table) in your form.
Please note that you can not pass the result of a calculation in a column to another field outside a Table field if you have not enabled the sum or average option for that column. Selecting the sum or average for the column will let the system know to add up or divide all values inputted in the column. The sum or average of the column (depending on what is selected) is the value used in the calculation with the other field(s). Continuing with the Order form example, your customer has completely filled out the form and you will like to include shipping costs before making payments on the form.
The shipping costs can then be added to the sum gotten from the Total price column and then passed onto a payment field which is a field outside a Table field.
5. Get the number of rows in a table field
You can get the total number of rows in a table field automatically by using the ‘Count’ option in the table field calculation. In the fields (‘Select a field to calculate’) section of the calculation modal where all the calculation supported fields in your form are listed, you will notice an option when you click on any table field listed (or scroll through the dropdown options if you are on the mobile builder) labeled ‘COUNT( )’.
Click or select the ‘Count’ option to add it to the calculation input then select a field outside the table to display the result of the count.
As expected, the Count option counts the number of rows that have been added on the Table field. Using our previous school example,if the headteacher will like to know the number of subjects taken by each student, rather than count each row to get this, he can use the count option to know how many rows have been added as each row is for each subject entered and this information can be used in other calculation outside the Table field.
Please note that when adding a count option in the calculation input modal, you cannot add other fields or arithmetic operations to the calculation input, you will need to pass the result of the Count solely to another field (by selecting the field in the Display Result in section). If you will like to use the value of the Count in another calculation, ensure you use a number field to display the result of the count and you can then use that result field in another calculation.