Share

Calculated fields

asterisk Contents

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 that 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 more 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 following example.

Calculated fields

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. 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:

  • All calculated fields are “live,” which means if you update any inputs in your field you see those changes immediately.

  • Once configured, you can choose to sort, group, or summarize on the field. You can also use calculated fields in advanced filter conditions (for example, Page filters, Page formatting rule conditions).

    Note:

    Calculated fields are not filterable from filter widgets in the filter panel.

  • Hover over the column header of a calculated field to see the formula.

    Tooltip

  • Graphing a calculated field is supported. In your graph, you can choose to group or summarize on a calculated field, which allows for some powerful graphing capabilities.

    Graph by calculated field

  • 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).

Math

Name Description Syntax
+ Addition value1 + value2
- Subtraction value1 - value2
* Multiplication value1 * value2
/ Division value1 / value2
MOD Returns the remainder of number1 divided by number2. MOD(number1, number2)

String

Name Description Syntax Example Result
CONCAT Combine strings CONCAT(string1, string2) CONCAT(“Shot”, “Grid”) “ShotGrid”
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

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 and Date and Time

Name Description Syntax Output Type Example Result
TODAY Returns today's date. TODAY() Date TODAY() 2020-05-14
DATE Forms a date. DATE(year, month, day) Date DATE(2023, 05, 13) 2023-05-13
NOW Returns current date and time. NOW() Date and Time NOW() 05/13/24 12:30pm
DATE_TIME Forms a date and time. DATE_TIME(year, month, day, hours, minutes, seconds, milliseconds, timezone_name) Date and Time DATE_TIME(2023, 05, 13, America/NewYork) 2023/04/18 12:00am
NETWORKDAYS Returns the number of work days between start_date and end_date. NETWORKDAYS(start_date, end_date) Duration NETWORKDAYS(DATE(2014, 1, 5), DATE(2014, 1, 15)) 8 days (or hours, according to Site Preferences)
WORKDAY Returns a date that is a number of work days (given by the duration) after the start_date. WORKDAY(start_date, duration) Date WORKDAY(DATE(2014, 1, 5), TO_DURATION(12,days)) 01/21/14
Note:

  • Resulting dates are formatted according to your Site Preferences. See Site Preferences.
  • Resulting duration is in days or hours, according to the Default unit for duration fields in your Site Preferences. See Site Preferences.
  • NETWORKDAYS() and WORKDAY() use work days in their calculations. In Flow Production Tracking, work days are Monday through Friday, with Saturday and Sunday being non-work days. NETWORKDAYS() and WORKDAY() do not support the work schedule feature.

Descriptions of DATE() and DATE_TIME() arguments

The arguments for the DATE() and the DATE_TIME() functions have some constraints, all described in the following table.

Argument Description
year
  • Negative values are invalid.
  • We strongly recommend using the full year with 4 digits.
  • 2000 is added to values < 100.
month
  • If the value is greater than the valid range of months, the function rolls over and adds the remaining months.
  • If the value is < 1, it removes a number months equal to (month +1).
day
  • If the value is greater than the valid range of days, the function rolls over and adds the remaining days.
  • If the value is < 1, it removes a number of days equal to (day +1).
hours
  • If the value is greater than the valid range of hours, the function rolls over and adds the remaining hours.
  • If the value is < 1, it rolls back one day and removes the number of hours.
minutes
  • If the value is greater than the valid range of minutes, the function rolls over and adds the remaining minutes.
  • If the value is < 1 it removes the number of minutes.
seconds
  • If the value is greater than the valid range of seconds, the function rolls over and adds the remaining seconds.
  • If the value is < 1 it removes the number of seconds.
milliseconds
  • If the value is greater than the valid range of milliseconds, the function rolls over and adds the remaining milliseconds.
  • If the value is < 1 it removes the number of milliseconds.
timezone_name
  • The format accepted is based on the Timezone Data for the TZInfo project. See the list of supported time zones.
  • This argument is case sensitive.

Notes about dates

  • When Dates are used in arithmetic functions, you need a full day to advance a date: a date + 0.9 will not advance the date. To move back a day, only a fraction of a day is necessary: a date - 0.1 returns the previous date.
  • When Date and Time are used in arithmetic functions, you don’t need a full day to advance a date. In fact, you can add or subtract fractions of a day which will be correctly calculated in hours, minutes, and seconds.
  • When Date and Date and Time functions are mixed together in arithmetic functions, the date is upcasted to a date and the time set at midnight.
  • DATE() and DATE_TIME() functions round down floating-point numbers.

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 are weeks, days, hours, minutes, seconds, and milliseconds. TO_DURATION(number, [unit]) Duration TO_DURATION(2)

TO_DURATION(2, weeks)

TO_DURATION(0.5, days)
2 days (or hours, according to your Site Preferences)

2 weeks

0.5 days*
TO_DATE Converts a date and time value to a date. TO_DATE(date_time) Date TO_DATE(DATE_TIME(2024, 5, 13, 12, 30, 15, 500, America/Montreal)) 05/13/24

(The formatting depends on your Site Preferences)
TO_WEEKS Converts a duration to a number of weeks. TO_WEEKS(duration) Float 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) Float 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) Float TO_HOURS(TO_DURATION(120, minutes)) 2
TO_MINUTES Converts a duration to a number of minutes. TO_MINUTES(duration) Float TO_MINUTES(TO_DURATION(2, hours)) 120
TO_SECONDS Converts a duration to a number of seconds. TO_SECONDS(duration) Float TO_SECONDS(TO_DURATION(2, minutes)) 120.0
TO_MILLISECONDS Converts a duration to a number of milliseconds. TO_MILLISECONDS(duration) Float TO_MILLISECONDS(TO_DURATION(2, seconds)) 2000.0
TIMECODE_STR Converts a number of frames and a frame rate into a timecode string. Does not support a drop-frame timecode. TIMECODE_STR(number_of_frames, frame_rate) String TIMECODE_STR(125, 30) 00:00:04:05
TO_STATUS Converts a short code to a status icon. TO_STATUS("status short code") Status List TO_STATUS("ip") TO_STATUS
TO_CODE Returns the short code of the status field passed in as an argument. TO_CODE({sg_status_list}) Text TO_CODE("ip") TO_CODE
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

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 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.
  • 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_STATUS converts a status short code to a status icon.
  • The TO_WEEKS , TO_DAYS , TO_HOURS , TO_MINUTES, TO_SECONDS, and TO_MILLISECONDS must use a converted duration field. This duration field must be on the same entity. TO_WEEKS , TO_DAYS , TO_HOURS , TO_MINUTES, TO_SECONDS, and TO_MILLISECONDS will then convert the duration field to a plain float value, in the specified unit.
  • The frame_rate argument of the TIMECODE_STR function is optional. If frame_rate is not specified, then the site's default FPS is used. This is usually 24 fps, but Flow Production Tracking support can set another value. See this article.

Logical functions

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() TRUE
FALSE Returns the logical value FALSE. This is represented by an unchecked checkbox in Flow Production Tracking. FALSE() Checkbox FALSE() 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")
FALSE

TRUE (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}) TRUE
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})
TRUE (not Client Approved) or FALSE (Client Approved)

TRUE (not Delivered) or FALSE (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")
Over Bid Cost

Under Bid Cost

Future or Past

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}) TRUE

Comparison operators

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()
TRUE

FALSE

TRUE or FALSE, depending

Using linked fields

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.

Reference dot syntax

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 formula

Example output:

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.

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 Image Example
Cut length {sg_cut_out} - {sg_cut_in} + 1 Float (10) Cut length
Cut summary CONCAT({sg_head_in}, " [", {sg_cut_in}, " - ", {sg_cut_out}, "] ", {sg_tail_out}) Text Cut summary
Projected shot cost {sg_shot_bid} * {sg_shot_cost} Float Projected shot cost
Shot prefix for reference CONCAT("A_", {code}) Text Shot prefix
Trim off last two characters of a string LEFT({string_field}, LEN({string_field}) - 2) Text Trimming a string
Task is Overdue or On Schedule IF({due_date} < today(), "Overdue", "On Schedule") Text Tasks overdue
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 Overtime
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 late versus on time as statuses
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 Tasks On time versus Overdue as statuses

Additionally, calculated fields are available through the API.

Was this information helpful?