Calculation fields let you use other fields in a formula to derive a new number.
For example, say you have a counter field called “quantity” and a numeric field called “price”. You could create a calculation field called “Grand Total” with the formula quantity * price.
Overview
Here is a brief video overview of the basics:
Please watch the video to understand the basics of calculation fields. Here are some more advanced examples.
Known Limitations
Many formula are possible with calculation fields, but the following are not currently possible:
- Using the calculation in association with Scoring
- Using calculation fields as a sub-field of counter fields
- Using counter sub-fields as variables in a calculation
- Using a select field’s value as a variable in a calculation
FUNCTIONS
Along with variables and simple math operators (like + and *), you can use functions like round as part of your formula. You can see a complete list of available functions here but the common ones are: abs, ceil, cube, exp, floor, log, log10, log2, max, min, pow, sqrt, square, random, sin, cos, and tan.
Punch Clock
Say you had two Date/Time fields, both set up to track date and time. One called “Punch In”, the other “Punch Out”.
Assign variable names to each field punch_in and punch_out – and now they can be used in a calculation.
Create a new calculation field called "Hours". A date/time field will come in as the number of seconds since 1970. Punch_out - punch_in is calculated as the number of seconds between punch out and punch in. We will need to divide the calculation by 3600, which is the number of seconds in an hour (60*60=3600). If hours is less than 8, it will evaluate to 0. Here is how the formula would look: (punch_out-punch_in)/3600
Days Away From Work
Some reports need to calculate the number of days between two dates. Create two date fields, start and end, add the variable names, then subtract those two fields:
Here’s the formula for that calculation: (EndTime-StartTime)
Overtime Hours
Let’s say you want a special field that shows overtime hours (anything over 8 hours). First you’d have a field where they’d enter total hours. This could be a numeric field, counter, or even a calculation based on a punch clock. In this example, we’ll use a numeric field. Then we’ll add a calculation field that uses a formula to show overtime:
This formula uses the max() function which evaluates which number is larger. If hours is less than 8, it will evaluate to 0. Here is hour the formula would look: max(hours-8,0)
Visibility Settings
Adding to the previous example, say we want a manager to sign off if there are 4 or more overtime hours. We can add a signature field that only shows up when necessary. First create the signature field and set it as required:
Now we'll edit the Visibility of that signature field to only show up if "Overtime Hours" is 4 or more. Edit the signature field and click the Visibility tab. Click "Add a condition", choose "Overtime Hours", set "4" as the minimum value, and click "Continue". Now the signature will only show when necessary: