Calculated fields

  1. Shotgun Support
  2. Shotgun Admin Guide
  3. Site configuration

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.

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.

Please also note you cannot divide by zero. Doing so will produce an error.

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

Once you have configured your field, you can choose to sort, group, or summarize on the field. You can also filter on a calculated field. Additionally, you can hover over the column header of a calculated field to see the formula.

Tooltip

Additionally, you can graph on a calculated field. In your graph, you can choose to group or summarize on a calculated field, which allows for some very powerful graphing capabilities.

Graph by calculated field

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.

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

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

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.

Article Last Updated::
Follow

18 Comments

Date Votes
  • 0
    Avatar
    Dennis --DJ-- Hauck

    OMG, this is Awesome!  I've wanted these in the web UI for years.  We always built event daemon plugins to do these basic functions, which seemed like overkill.  Great add team, thanks!

  • 8
    Avatar
    Jeremie Passerin

    Would be nice to also have Max(), Min() method

  • 0
    Avatar
    Jacob Medendorp

    Just a small request - it would be nice if in the available fields section, it also listed the field type.

    Thanks for this feature!

  • 1
    Avatar
    Ziah Fogel

    Thanks for the feedback, everyone! We've actually already got one of these prepared for the next release - listing the available field type in the UI. Working on some duration-related things too - so more is on the way! :)

     

  • 1
    Avatar
    Juanmaria Garcia

    Very good! At last.

    Percentage format would help, also

     

  • 1
    Avatar
    Benoit Leveau

    It's a great addition!

    We'll need a rounding function so we can compute overscan resolutions. Currently, using a formula like:

    CONCAT({sg_overscan}*{sg_width},"x",{sg_overscan}*{sg_height})

    gives a result of: 

    2115.584x1194.148

    and we'd like to show it as:

    2116x1194

    Also, very minor glitch in the UI: in the list of Availale Functions, the 3rd one is listed as "+    Multiplication".

     

  • 0
    Avatar
    Gavin Greenwalt

    {sg_sequence} isn't supported though? :(  That's the field I really wanted to do calculated fields for.  

    e.g.    Project_Sequence_Shot

  • 0
    Avatar
    Marcel Tie

    Great stuff!

    Would be nice, if the date fields on task entity could be available as well so that we can get more calculated duration (in days).

    Further, the thoughts from Tony are really considerable in my point of view!

    Cheers.

  • 2
    Avatar
    Nils Lerin

    +1 for adding support to query fields. Would help us immensely 

  • 1
    Avatar
    Lorenzo Basurto

    Other little request, it would be great to be able to evaluate Checkboxes at least as booleans, 0 1

     

  • 1
    Avatar
    Julien Brasseur

    Could we have a length or sum function to get the number of entities of a field ? Something like LEN({sg_actors})

  • 0
    Avatar
    Francois Touvet

    Hi, I can see there are some Regular Expression support in the Calculated Fields:

    Is it functional yet? Is there a documentation somewhere?

  • 0
    Avatar
    Dennis --DJ-- Hauck

    It would be great to have "timecode" fields included in calculations.  

  • 0
    Avatar
    Tobias Pfeiffer

    Can I add line breaks when concatenating strings?

    \r and \n seem not to work

  • 0
    Avatar
    Kazutomo Kudo

    Would like to use Date datatype field in the calculated fields. And something like TIMEDELTA function that Tony suggested would be fantastic.

    Thanks!

  • 2
    Avatar
    Sigtor Kildal

    Adding support to query fields?

    Please!

  • 0
    Avatar
    Julien Virgile

    Hello.

    Calculated fields are neat.

    But it would be even greater if:

     - We could add simple operations on dates (the number of preposterous and lagging eventloops that calculate the delta between various dates would be dramatically reduced). Can't the date be considered as an integer ? Like in Excel & google Sheet ?

     - We could make calulatedf fields across entities, when their linked by a single entity field. (as it works with grouping & sorting) For instance: we can group versions by their linked asset types... I would be nice to be able to make a Version calculated field that processes one of it's entities fields. Notably: i artists publsh versions that have a duration different to that of their linked shot, a calculated field would be a lot faster than an evenloop...

     

    Obi One Shotgun, Do we have hope ?

  • 1
    Avatar
    Simon Heath

    Can I do calculations using queries?

Please sign in to leave a comment.