Calculated fields

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 field

Type in your formula, and then choose whether to use “Float” or “Text” as the Output Format. If you choose “Float,” you can also choose the Display Format, which will override the default float format for this particular field.

Note: Currently, calculated fields only support basic arithmetic and string functions. Cells will turn orange to warn you about a possible mismatch in the output type. Cells will turn red if there are errors that will cause the formula to fail. If you hover over the red cell, you will see a description of the error in the tooltip.

Please 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. For more information on formatting expressions, please see http://www.rexegg.com/regex-quickstart.html.

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 over over the column 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 on calculated field

Example calculations

Below are some examples of formulas for calculated fields.

Field Name 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 ref CONCAT("A_", {code}) Text

Additionally, calculated fields are available via the API.

Follow

12 Comments

  • 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!

  • 7
    Avatar
    Tony Aiello

    Woohoo, seconded!  So thrilled for these.  Here are some ideas for future function additions based on other existing use-cases we have that we're either doing with daemon plugins or by hand:

    1. time deltas!  e.g. on a Task with a {due_date}, calculate a date N days into the future from it.  Excel doesn't seem to have great syntax for this, so maybe a python inspiration TIMEDELTA({due_date},21) could return a date 3 weeks into the future from a Task's due_date.  Alternately, functions to encode the units of the delta e.g. sg_future_date = {due_date} + WEEKS(3).  These should still obey WorkScheduleRules, so if a calculated future_date lands on a non-working day, I'd expect it to get offset to the next real working day.

    2. string slicing ala Excel's text functions (since it seems like you're going for Excel, with the name "CONCAT"):  how about LEFT(string,N), RIGHT(string,N), MID(string,P,N):  Use-case: calculate a HumanUser's initials e.g. CONCAT(LEFT({firstname},1), LEFT({lastname},1))

    3. list selectors!  for example a syntax like {task_assignees}[0] or {task_assignees.0} or whatever could return that 0'th entry from the multi-entity assignees field on a task, thus returning a single-entity field and making it flow-through-able!  Literally 90% of the time, we're assigning tasks to just a single person or group.  (29,125 tasks with only 1 assignee out of 32,667 with assignees)

    4. support for Query Fields as inputs.  Note that doing so could be one way to solve the long-standing feature request for being able to sort + group + filter by Query Fields: create a calculated field that just returns the value of a query field then sort / group / filter by that calculated field.

    5. error-handling.  provide a place to enter a "default" field to return if a calculation cannot be evaluated, like the except: clause for a DivisionByZero error.

    Edited by Tony Aiello
  • 6
    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! :)

     

  • 0
    Avatar
    Juanmaria Garcia

    Very good! At last.

    Percentage format would help, also

     

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

  • 0
    Avatar
    Tony Aiello

    Here are a couple more string-processing formula requests!

    • DECODE('hex') -- convert hex-encoded strings e.g. session_uuids back to ASCII text
    • STRIP() -- remove whitespace characters from the beginning and end of strings

    This is so we could have a calculated field that "decrypts" our encoded os.environ['USER'] as per https://support.shotgunsoftware.com/hc/en-us/community/posts/115004295453

  • 0
    Avatar
    Nils Lerin

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

  • 0
    Avatar
    lozo vfx

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

     

Please sign in to leave a comment.