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 above video to understand the basics of calculation fields. Here are some more advanced examples.
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”. Then edit the formula like this:
A date/time field will come in as the number of seconds since 1970. So punch_out - punch_in is the number of seconds between punch in and punch out. Then we divide by 60 * 60 which is the number of seconds in an hour. The calculation will now report the number of hours:
DAYS AWAY FROM WORK
Some reports need to calculate the number of days between two dates. So create two date fields, start and end, then subtract those two fields:
Here’s how that would end up looking:
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 to which ever number is larger. So if total_hours is less than 8, it will evaluate to 0. Here’s how it would look:
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. So Edit the signature field and click the visibility tab. Now add a condition, choose “Overtime Hours”, set “4” as the minimum value, and press Continue. Now the signature will only show when necessary:
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