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.
Notes:
- Cells will turn red if there are errors that will cause the formula to fail. There will be messages just below that guide you through how to make a valid formula.
- You cannot divide by zero. Doing so will produce an error.
- Calculated field formulas are limited to 500 characters by default.
Keep in mind:
Calculated fields are not filterable from filter widgets in the filter panel.
Name | Description | Syntax |
---|---|---|
+ | Addition | value1 + value2 |
- | Subtraction | value1 - value2 |
* | Multiplication | value1 * value2 |
/ | Division | value1 / value2 |
Name | Description | Syntax | Example | Result |
---|---|---|---|---|
CONCAT | Combine strings | CONCAT(string1, string2) | CONCAT(“Shot”, “Grid”) |
“Flow Production Tracking” |
UPPER | Uppercase string | UPPER(string) | UPPER(“Flow Production Tracking Software”) |
FLOW PRODUCTION TRACKING SOFTWARE |
LOWER | Lowercase string | LOWER(string) | LOWER(“Flow Production Tracking Software”) |
flow production tracking software |
LEFT | Returns part of the string from the beginning of the specified string | LEFT(string, [number_of_characters]) | LEFT(“ab_0124”, 2) LEFT(“Flow Production Tracking 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(“Flow Production Tracking 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("Flow Production Tracking Software") |
16 |
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. |
Name | Description | Syntax |
---|---|---|
TODAY | Returns today's date. | TODAY() |
DATE | Forms a date. | DATE(2020, 4, 27) |
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 |
TO_STATUS | Converts a short code to a status icon. | TO_STATUS("status short code") | Status List | TO_STATUS("ip") |
|
TO_CODE | Returns the short code of the status field passed in as an argument. | TO_CODE({sg_status_list}) | Text | TO_CODE("ip") |
|
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) |
CEILING | Returns number rounded up | CEILING({a_float_field}) |
Float | CEILING({a_float_field}) CEILING(2.3) |
{a_float_field} rounded up 3 |
FLOOR | Rounds number down | FLOOR({a_float_field}) |
Float | FLOOR({a_float_field}) FLOOR(2.3) |
{a_float_field} rounded down 2 |
number
is the number to format.value
is the numeric value to be converted to a float, currency, or percentage.number_of_places
is the number of decimal places to display.number_of_places
is six. If the number has fewer than number_of_places
significant digits, zeros will be appended. If it has greater than the number_of_places significant digits
, the number will be rounded to the correct number_of_places
, rather than truncated.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.TO_CURRENCY
returns the value converted to a currency field. It will obey your Site Preferences for currency display.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.TO_DURATION
returns the value converted to a duration field. It will obey your Site Preferences for duration display (days, hours, etc.)TO_STATUS
converts a status short code to a status icon. TO_WEEKS
, TO_DAYS
, TO_HOURS
, and TO_MINUTES
must use a converted duration field. This duration field must be on the same entity. TO_WEEKS
, TO_DAYS
, TO_HOURS
, and TO_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.Name | Description | Syntax | Output Type | Example | Result |
---|---|---|---|---|---|
TRUE | Returns the logical value TRUE. This is represented by a checked checkbox in Flow Production Tracking. | TRUE() | Checkbox | TRUE() |
|
FALSE | Returns the logical value FALSE. This is represented by an unchecked checkbox in Flow Production Tracking. | FALSE() | Checkbox | FALSE() |
|
AND | Returns a checked box (TRUE) if all conditions passed in as an argument are TRUE. This can take any number of arguments. | AND({sg_condition1}, [{sg_condition2}, ...]) | Checkbox | AND({sg_false_checkbox}, {sg_true_checkbox}) AND({id} > 0, {code} = "hero_rock") |
(if "hero_rock"—case-sensitive—and if the id field is greater than 0) |
OR | Returns a checked box (TRUE) if any conditions passed in as an argument are TRUE. This can take any number of arguments. | OR(({sg_condition1}, [{sg_condition2}, ...]) | Checkbox | OR({sg_false_checkbox}, {sg_false_checkbox}, {sg_true_checkbox}, {sg_false_checkbox}, {sg_false_checkbox}) |
|
NOT | Reverses the value passed in as an argument and returns an unchecked box when the compared checkbox is checked, and vice versa. | NOT({sg_checkbox_field}) | Checkbox | NOT({client_approved}) NOT({sg_delivered}) |
(not Client Approved) or (Client Approved) (not Delivered) or (Delivered) |
IF | The if function takes three parameters in the form IF(condition, then, otherwise). The first parameter is a condition that must evaluate to either true or false, the second and third parameters are the values returned if the first parameter is true or false. The second and third parameters should be of the same field data types (e.g. text, number, etc.). | IF(condition, then, otherwise) | The same data type as the parameters | IF({sg_cost}>{sg_bid_cost}, {sg_cost}-{sg_bid_cost},to_currency(0)) IF({sg_bid_cost}>{sg_cost}, {sg_bid_cost}-{sg_cost},to_currency(0)) IF({due_date} < today(), "in the past", "in the future") |
|
IS_EMPTY | Returns true if a cell is empty/blank/null, represented by a checked checkbox in Flow Production Tracking. | IS_EMPTY() | Checkbox | IS_EMPTY({due_date}) |
Name | Description | Syntax Example | Output Type | Example | Result |
---|---|---|---|---|---|
< (less than), <= (less than or equal to), >= (greater than or equal to), > (greater than), = (equal to), <> (not equal to) | Compares two expressions and returns a Checkbox type value. The compared values must be of similar field data types (e.g. number fields compared with other number fields, text fields compared with other text fields, etc.), otherwise you may have to cast the variables. Returning a checked box is TRUE, while returning an unchecked box is FALSE. | 1 < 100 "foo" = "bar" {due_date} < today() |
Checkbox | 1 < 100 "foo" = "bar" {due_date} < today() |
or , depending |
Linked fields can be used in calculated fields by specifying the dot syntax for the path to a linked field (example: entity.Asset.code
). This is often called deep linking or using dot syntax.
To reference a list of linked field dot syntaxes available on an entity, reference the Available Linked Fields dropdown in the field configuration dialogue for the Calculated field type.
Then, you can copy and paste the linked fields available into the formula.
Example formula:
CONCAT("Description: ", {entity.Shot.description}, " Cut Info: ", {entity.Shot.sg_head_in}, " [", {entity.Shot.sg_cut_in}, " - ", {entity.Shot.sg_cut_out}, "] ", {entity.Shot.sg_tail_out})
Example output:
Limitations:
- Only the first level of Linked fields (also known as a single hop) are supported in Calculated Fields.
- A linked field cannot be a calculated field type.
You may see different error messages in your calculated fields, either in the formula editor itself or in the results of the calculation.
Below are some examples of formulas for calculated fields.
Description | Formula | Output format | Image Example |
---|---|---|---|
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 | |
Task is Overdue or On Schedule | IF({due_date} < today(), "Overdue", "On Schedule") |
Text | |
Overtime on TimeLogs vs Bids on Tasks | if(and({est_in_mins} > to_duration(0, minutes), {time_logs_sum} > {est_in_mins}), {time_logs_sum} - {est_in_mins}, to_duration(0, minutes)) |
Text | |
Tasks that are late or on time displayed as statuses | to_status(if(and({sg_status_list} <> "fin", {due_date} < today()), "lte", "ote")) |
Status Icons | |
Tasks are "On time", unless they do not have a Due Date specified and should be left empty, and "Overdue" for any Task that is not Final and has a Due Date in the past | to_status(if(is_empty({due_date}), "", if(and({sg_status_list} <> "fin", {due_date} < today()), "Overdue", "On time"))) |
Status Icons |
Additionally, calculated fields are available via the API.