Calculated fields allow you to compute a field with values from other fields from the same entity (such as a Shot or an Asset). You can use calculated fields to make it easier and faster to find useful information.
Add a calculated field the same way you add any other field type. You will see the list of available fields for the specific entity you’re using under the “Available Fields” section. If you add additional fields of supported types to your page, they will appear under the list of Available Fields on your Calculated fields.
Make sure you use the format listed under “Available Functions” in your formula, like in the example below.
Type in your formula. If the formula uses the “Float” output type, you can choose the Display Format, which will override the default float format for this particular field.
Note: Cells will turn red if there are errors that will cause the formula to fail.
Please also note you cannot divide by zero. Doing so will produce an error.
Keep in mind:
- All calculated fields are “live,” which means if you update any inputs in your field you will see those changes immediately.
- Once configured, you can choose to sort, group, or summarize on the field. You can also filter on a calculated field.
- Hover over the column header of a calculated field to see the formula.
- Graphing a calculated field is supported. In your graph, you can choose to group or summarize on a calculated field, which allows for some very powerful graphing capabilities.
- A calculated field value change is not recorded with its own Event Log Entry. Calculated fields cannot be configured to appear in an entity Activity Stream, or selected to drive notifications.
- If included in the list of fields in the body of an email notification, calculated field values will always be blank. (Including a calculated field code in the list of fields to display in an email notification body will not result in an error.)
Available functions
Math
Name | Description | Syntax |
---|---|---|
+ | Addition | value1 + value2 |
- | Subtraction | value1 - value2 |
* | Multiplication | value1 * value2 |
/ | Division | value1 / value2 |
String
Name | Description | Syntax | Example | Result |
---|---|---|---|---|
CONCAT | Combine strings | CONCAT(string1, string2) | CONCAT(“Shot”, “gun”) |
“Shotgun” |
UPPER | Uppercase string | UPPER(string) | UPPER(“Shotgun Software”) |
SHOTGUN SOFTWARE |
LOWER | Lowercase string | LOWER(string) | LOWER(“Shotgun Software”) |
shotgun software |
LEFT | Returns part of the string from the beginning of the specified string | LEFT(string, [number_of_characters]) | LEFT(“ab_0124”, 2) LEFT(“Shotgun Software”) LEFT(“ab_0124”, 0) |
“ab” “S” “” |
RIGHT | Returns part of the string from the end of the specified string | RIGHT(string, [number_of_characters]) | RIGHT(“ab_0124”, 4) RIGHT(“Shotgun Software”) RIGHT(“ab_0124”, 0) |
“0124” “e” “” |
MID | Returns part of the string, taken from a segment of the string | MID(string, starting_at, extract_length) | MID("ab_0124", 3, 1) |
“_” |
LEN | Length of a string | LEN(string) | LEN("Shotgun Software") |
16 |
Definitions of string arguments
Name | Description |
---|---|
number_of_characters | The number of characters to return from the left or right side of string. (This is optional, and set to “1” by default.) |
starting_at | The starting point from the left of the string from which to begin extracting. The first character in the string has the index 1. |
extract_length | The length of the segment to use. |
Notes about strings
- 0 is a valid input for number_of_characters and will cause LEFT and RIGHT to return the empty string (“”).
- If the end of string is reached before extract_length characters are encountered, MID returns the characters from starting_at to the end of string.
- To return the contents from starting_at to the end of string, use LEN to calculate the length of the string that will be returned rather than simply specifying a large number for extract_length.
- LEN counts all characters, even spaces and nonprinting characters. In cases where LEN returns unexpected values, ensure that there are no such characters in text.
Date
Name | Description | Syntax |
---|---|---|
TODAY | Returns today's date. | TODAY() |
Notes about dates
-
When dates are used in arithmetic functions, you need a full day to advance a date (a date + 0.9 would not advance the date), but in order to move a day back, only a fraction of a day is necessary (a date - 0.1 would return the previous date).
Type conversion
Name | Description | Syntax | Output Type | Example | Result |
---|---|---|---|---|---|
FIXED | Formats a numeric value as text to display a specific number of decimal places. | FIXED(number, [number_of_places]) | Text | FIXED(3.141592,4) FIXED(3.141592,0) FIXED(3,3) |
3.1416 3 3.000 |
TO_FLOAT | Converts a numeric value (number, currency, percent) to a float. | TO_FLOAT(number) | Float | TO_FLOAT(11) TO_FLOAT({sg_percent}) // {sg_percent} = 40%TO_FLOAT(sg_amount}) // {sg_amount} = $200 |
11.00 0.40 200.00 |
TO_CURRENCY | Converts a numeric value (number, float, percent) to a currency. | TO_CURRENCY(number) | Currency | TO_CURRENCY(1.479531) TO_CURRENCY(10) TO_CURRENCY({sg_percent}) // {sg_percent} = 40% |
$1.48 $10.00 $0.40 |
TO_PERCENT | Converts a numeric value (number, float, currency) to a percent. | TO_PERCENT(number) | Percent | TO_PERCENT(0.4) TO_PERCENT(0.40926) TO_PERCENT(101) |
40% 40% 10,100% |
TO_DURATION | Converts a number to a duration with an optional “duration unit”. Supported “duration units” include weeks, days, hours, and minutes. | TO_DURATION(number, [unit]) | Duration | TO_DURATION(2) TO_DURATION(2, weeks) TO_DURATION(0.5, days) |
2 days (or hours, depending on your Site Preferences) 2 weeks 0.5 days* |
TO_WEEKS | Converts a duration to a number of weeks. | TO_WEEKS(duration) | Duration | TO_WEEKS(TO_DURATION(10, days)) |
2 (if you have a five-day week set in your Site Preferences) |
TO_DAYS | Converts a duration to a number of days. | TO_DAYS(duration) | Duration | TO_DAYS(TO_DURATION(8, hours)) |
1 (if you have an eight-hour day set in your Site Preferences) |
TO_HOURS | Converts a duration to a number of hours. | TO_HOURS(duration) | Duration | TO_HOURS(TO_DURATION(120, minutes)) |
2 |
TO_MINUTES | Converts a duration to a number of minutes. | TO_MINUTES(duration) | Duration | TO_MINUTES(TO_DURATION(2, hours)) |
120 |
DATE | Converts numerical values to a date. | DATE(year, month, day) | Date | DATE(year, month, day) |
DATE(2020, 5, 14) 2020-05-14 (Formatting will depend on your Site Preferences) |
Notes about type conversions
- The
number
is the number to format. - The
value
is the numeric value to be converted to a float, currency, or percentage. - The
number_of_places
is the number of decimal places to display. - The maximum value for
number_of_places
is six. If the number has fewer thannumber_of_places
significant digits, zeros will be appended. If it has greater than thenumber_of_places significant digits
, the number will be rounded to the correctnumber_of_places
, rather than truncated. - The
TO_FLOAT
returns the value converted to a float, with the standard interpretation that 1 = 100%. Since the output type will be a float, you can choose how many decimal places to display in the result. - The
TO_CURRENCY
returns the value converted to a currency field. It will obey your Site Preferences for currency display. - The
TO_PERCENT
returns the value converted to a percentage, with the standard interpretation that 1 = 100%. The value is floored to get to the nearest whole number percent. - *The
TO_DURATION
returns the value converted to a duration field. It will obey your Site Preferences for duration display (days, hours, etc.) - The
TO_WEEKS
,TO_DAYS
,TO_HOURS
, andTO_MINUTES
must use a converted duration field. This duration field must be on the same entity.TO_WEEKS
,TO_DAYS
,TO_HOURS
, andTO_MINUTES
will then convert the duration field to a plain float value, in the specified unit. DATE
arguments passed to this function (year, month, day) are floored–rounded to the integer below—if they are floating point numbers.- Year:
- Values <0 are invalid.
- We strongly suggest using the full year with 4 digits.
- 2000 is added to values < 100.
- Month/Day:
- If the value is under the valid range of months/days we roll over and add months/days.
- If the value is <1 we remove the number of months/days +1.
- Year:
Error messages
You may see different error messages in your calculated fields, either in the formula editor itself or in the results of the calculation.
Example calculations
Below are some examples of formulas for calculated fields.
Description | Formula | Output format |
---|---|---|
Cut length | {sg_cut_out} - {sg_cut_in} + 1 |
Float (10) |
Cut summary | CONCAT({sg_head_in}, " [", {sg_cut_in}, " - ", {sg_cut_out}, "] ", {sg_tail_out}) |
Text |
Projected shot cost | {sg_shot_bid} * {sg_shot_cost} |
Float |
Shot prefix for reference | CONCAT("A_", {code}) |
Text |
Trim off last two characters of a string | LEFT({string_field}, LEN({string_field}) - 2) |
Text |
Additionally, calculated fields are available via the API.