You can use calculation fields in Smartabase to perform calculations using the data captured in forms. There are some calculation fields that don't require you to write the equation yourself (e.g. when creating a linked calculation, you pick the relevant field from a list), but in the calculation types with more flexibility, you can use the Calculation tab to write equations which will calculate the result you're interested in. You can use operators and built-in functions to simplify the process of writing equations.
Equations are based on a similar model to Microsoft Excel: you can reference fields within forms and use some standard notation or built-in functions to return a new value. It's important to understand what data you expect from a calculation and choose the appropriate calculation and functions, otherwise the calculation may fail or not work as expected.
Choosing the appropriate calculation field
- If your calculation should result in a string of text, an option or data in duration form, then you should use a text, option or duration calculation (respectively).
- If the data that you want to include in your calculation comes from a table in the form, you must use a table calculation.
- If you need to reference historical data, you must use one of the historical calculations.
- If your calculation should display data from a different form (i.e. from another event form or a profile form), you need to use one of the linked calculations.
Operators and order of operations
You can use standard operators to perform general calculations.
- + for addition
- - for subtraction
- * for multiplication
- / for division
As with Excel, and other programming languages, use the standard order of operations to perform calculations so that the following precedence exists:
- Exponents and roots
- Multiplication and division
- Addition and subtraction
Brackets can also be used to split up a calculation; operators within brackets are performed before operators outside of brackets. For example:
3 + 4 * 2
This would return 11, since the multiplication occurs before the addition. However, the example below would return 14, since the addition within the brackets occurs before multiplication.
(3 + 4) * 2
Functions
In addition to the standard operators, Smartabase also has many built-in functions that can be used. You can choose basic functions like sum, min and max as well as more advanced functions like log or sqrt. For more information, refer to our article on Using Functions.
Remember that when you reference a field in a calculation, the field name is both case sensitive and space sensitive.
Ordering calculations
When building a form, fields and calculations need to be ordered according to the sequence in which they should be populated. For example, a calculation which averages two number fields should be displayed after the number fields.
Calculation syntax
For a simple example using the calculation field, if you wanted to convert Height to centimeters from meters and then add this to the Weight, you could write the formula used here:
(Height * 100) + Weight
A common calculation used in Smartabase is an if statement, which gives you the ability to return different values based upon specific conditions that should be met. This follows the standard notation for if/else notation:
if(Logical test, Value if true, Value if false)
As an example, if you wanted to perform a weighting calculation that multiplied an athlete’s workload by 10 when the Session Type was Match but just return the unweighted Workload otherwise, you could write the following equation:
if(Session Type = “Match”, Workload * 10, Workload)