70

Calculated Fields

Ben: I'm editing this idea to have it reflect our latest thinking. 

In order to move forward on this feature, we'll need to pick a list of the most popular uses cases for calculated fields and how our clients need to use these fields. Please reply to this thread with these details:

  1. Examples of calculated fields you would make. Typically, this is phased as [Entity.Field 1] [some operator] [Entity.Field 2] in [unit]. For example, "Task.Bid times Task.Rate in dollars".
  2. What you need to do with the calculated field value: filter? sort? group? access via API?

Here is the list we've heard so far from support tickets:

  • Shot Bid minus Shot Actual in days
  • Shot Bid multiplied by Shot Cost in dollars
  • Task Bid time Task Cost in pounds (totaled at the Shot level)
  • Task Duration divided by # of People in Assigned To in days (Duration in Man Days)
  • Task Duration multiplied by Person’s Rate in dollars
  • Shot Estimated End minus Shot Estimated Start in days
  • Time Logged Duration multiplied by Person’s Rate in dollars
  • MocapTake Frame Out - Frame In in frames
  • MocapTake Frame Duration divided by 24 in seconds
  • Shot Cut Duration Divided by Shot Total Duration in frames
  • Task End Date minus Today in days

I'll update this ticket as we evolve the spec and have designs.

48 comments

  • Avatar
    Nephi Sanchez Official comment

    Hi Everyone,

     

    Just wanted to let everyone know that we are working on a v1 of calculated fields.  Stand by - more updates to follow!

     

    cheers,

    Nephi

     

  • 1
    Avatar
    Stu Aitken

    just wondering - how does the current relationship between duration, start time and end time work?

    (sorry at home so can't check if it DOES update)

    could the same mechanism be used (if it does)?

  • 0
    Avatar
    Don Parker

    Hey Stu, those task fields work with triggers in our code, and yes they do update in the UI interactively.  This example is simple and fast because the updates are happening on fields on the same record (Start, End, Duration on Task).  This project would be much more straight forward if we could limit scope of calculated fields to files w/in a singe record. 

    However, most of the examples I think up and hear about are for updating a field based on a bunch of linked records.  For example, you might want field on Sequence called "Shot Count" that is a sum of all Shots linked to the Sequence.  That's pretty simple too actually, even if you wanted to update that query to only look for Shots that have a status that is not "omitted". 

    It gets more complex if you wanted a field on Sequence called "Bid Days" that summarizes the total bid days on all Tasks on all Shots in the Sequence.  Then any time a bid is updated on a task, or any time a shot is added or removed from a sequence, or any time a shot is created or deleted, we need to re-run the calculation.  That's not too bad either, but what if you then have a field on the Project called "Bid Days" that summarizes all the Bid Days fields on all Sequences in the Project.  That means any change to a Task bid, or any change to a Shot's status or any change to a Task's connection to a Shot or a Shot's connection to a Sequence or a Sequence's connection to a Project would require a recalculation.  And then what if you change a bunch of Tasks at once?  Or one after the other, essentially re-triggering a big update all the way up to the Project with each change.  That's a lot of math.  We know about this because our current Task summaries have taught us some lessons.  Ideally there would be some smarts in here to execute and manage those calculations really efficiently (and we might need to do this in the db instead of the code to make it fast enough, which is one reason we're moving towards supporting a single db).

    When talking to Matt about this, he said it's easy to build this and have it be slow.  Making it fast is the hard part.  So that is currently simmering in some heads over here, and we'll invest in doing a r&d "spike" on it soon to flesh out more information.

    But I should ask you all.  Would there be value in first supporting the case where the summary fields are only adding up values based on fields on the same record?  Perhaps we start there to work out the various UI bits and then work our way up to support the most complicates use cases?

  • 0
    Avatar
    Stu Aitken

    the crucial one for me would be:

    on task entities: est time remaining (task calculated field)  = bid days (a task property) - logged days (is this brought in via linked records on timelogs? I suspect it is)

    this is the absolute core of how we track wether tasks are looking like they are on target or are in fact over or likely to be over (even under sometimes!)]

    so much so I was kinda surprised that shotgun didn't do this already

    in our current axis excel+msproject+the-odd-script+bits-of-paper tracking 'solution'  we track and regularly update (on each task) :

    • bid time (or estimated time - this does not change after a bid is green lit)
    • actual time logged so far
    • a revised estimate to completion
    • a revised total time (logged time + revised estimated time to completion)
    • projected (or actual if its gone over already) variance =  bid time - revised total time

     

    to get back to your question, I think there are places where calculated fields that work exclusively within the fields of one entity would be useful but, if my assumptions are correct,  that would not really give us what we are looking for here?

  • 0
    Avatar
    Tommy Kiser

    For us the 'step one' of allowing calculated fields only within the scope of a single record *would* be valuable.  There's a couple of things we're doing in scripts now that would be better as dynamically updating calculations, like frame count fields (end frame - start frame) and conversions (a field that shows data from a framecount field multiplied by 24 to convert it to seconds).  I assume this would work in the phase one functionality you are talking about - maybe you choose a field or explicit value, operation, another field or explicit value, etc.  Or you use some kind of formula language to define the calculation for the field:

    #sg_framecount * 24

    (#sg_endframe - #sg_startframe) / 24

    etc.

  • 0
    Avatar
    Pliny (John Eremic)

    I'll have to echo Tommy's sentiment here. Although I still think that the "hard way" will be needful in the long run, å more narrow scope would help me out in the short run as well.  I could for example calculate rough actuals or projections by adding a "rate" field to the time log entries.  That's something I can't do right now.

  • 0
    Avatar
    Stu Aitken

    have been thinking about this some more - is the following helpful at all? - I imagine this may be something you guys have already thought of :)

    if its easier to just calculate between properties restricted to the entities being searched on the main view:

    for any calculated field that references a related (ie linked) entity type property, a placeholder value 'container' field is generated as part of the initial page view generation that corresponds to the related value involved in the calculation

    the main page query returns all the entities on the page and 'looks up' the related property involved in any calculated field for each entity as it does so and stores it in the temp 'container field' (ie  a variable field essentially)

    that way there is now a defined (even if temporarily) value for this that belongs directly to each entity on the page

    any changes to the proprties for entities on this page that also affect the calculation can now be calculated using the lookup - the lookup itself can't change while the user is still on this page view becuase you would not be directly modifying the related entities properties...

     

    does this make sense?

  • 0
    Avatar
    Stu Aitken

    actually re-reading Don's post I realise that this doesn't add anything at all - I was assuming that you wouldn't be editing anything off the current view when of course other people may be doing so...

     

    oh well :)

  • 0
    Avatar
    david maas

    For me, definitely - I have projects of relatively modest scale, and untypical of what most productions face, in that I use rough projections to consult student projects in the conceptual phases. Being able to the kinds of calculations listed above: second to frame conversions, scheduling deviations, etc. is important, and with the same functionality, I construct a "production concept" spreadsheet to assist in student consultations that generates rough targets (animation, production, render)  - a wonderful reality check for the students early in the process. Simple stuff...

    I'm trying to replicate this, fitting it to match Shotgun's own characteristics, so this would be  a great function, even if it is replaced later by a system plugged into the db.

  • 0
    Avatar
    Paul Hudson

    Wasn't deep enough into Shotgun when then thread was active... so excuse me for being late to the party.

    So we got the Query Fields which are super helpful in many cases, but currently I am running into brick walls when I can't access these Query Fields via Links (i.e. from other tables).  Is there any idea on a way to do that?  Would that be easier than the Option 2, trigger/storing values route discussed above.  Or would it just lead to dramatic slowdowns?

    Thanks,

    Paul 

  • 0
    Avatar
    Stephen Chiu

    Paul,

    Regarding the ability to access and reveal Query fields from a linked entity (Example: while in a Task page, reveal a Query field called "Latest Version Thumbnail" that exists in the linked Shots page), we do plan on supporting this! We have a ticket for this now (SG #13160), and I will keep the forum updated on its progress.

    In the meantime, a good workaround is to create another Query field on the current entity page (Example: Create a Query field on the Task page for the latest Version thumbnail where the linked Shot's Tasks field includes the current Task). This works well for links like this where there's a one-to-many relationship, and doesn't work for links with many-to-many relationships as it could return more than one linked result, and it wouldn't know which value to return. More details on entities are here:

    https://support.shotgunsoftware.com/entries/81662-linking-entities-together-with-entity-fields

    Cheers,

    Stephen 

  • 0
    Avatar
    Eli Rarey

    Just checking in to see what the latest development in calculated fields is.  It looks like Stu Aitken's request has been taken care of with the Time Logged Over/Under field that is hard-coded.

    For me, what I would want most from calculated fields would be simply to be able to recreate a similar field on my own, but of course customizing which fields are pulled from.  In other words, though it would be great to be able to pull values from various linked entities, what I need most is just to be able to take 2 fields from the same record and do a simple calculation on them, such as generate a percent or find the difference.

    The use case example would be recreating the Time Logged - % of Bid and Time Logged Over/Under fields using a field other than Bid, as we have multiple bid-related duration fields on our task entities.

  • 0
    Avatar
    Astrid Scholte

    Hi Eli,

    Thank you for your message! We're currently gathering information from Clients on how they would want to use calculated fields in the UI so that we can best setup this feature in the near future. If you could please pass on any other use-cases for our development team that would be great!

    Thanks so much for your valued feedback!

    Kind regards,
    Astrid

  • 0
    Avatar
    Roman Ignatov

    Hi Astrid,

    Here is one of the case where calculated field could be useful.

    I'm tracking, lets say, chain of events in the pipeline, i.e. some actions are timestamped in dedicated fields in Assets, Scenes, Shots and custom entities.

    I have to make sure that the timestamps are in line and to highlight/filter those that are out of sync when the consequent action becomes outdated if someone updates preceding action.

    There is no way to compare values of two fields in the formatter, filter, query, etc. Query condition allows to check the field against a constant value only.

    If I had a calculated field I could get the difference between two fields and handle it in standard query condition.

    Although it would be better to be able to refer a current record field in query condition. At least it's possible to use 'current entity' (id 0) in the query condition box.

     

    Cheers,

    Roman

     

    Cheers,

    Roman

  • 0
    Avatar
    Michal M.

    My suggestion is attached.

    Explanation of symbols used in picture:

    "e" next to the column name stands for "expression"- and means the column is used somewhere on the sheet in expression (similar visual clue to Nuke)

    "recalc button"= "+-*/" sign means this column is calculating some expressions-showing the results. It works as a button as well- serving for recalculation of this column. It is either gray (as in this example) or yellow- that means it is not recalculated (the same principle as "not saved" page in SG)

    "!" in the corner of a cell means, that this cell is actually not calculated - needs recalculation

    And the options (in preferences) for the time point in which the expressions are evaluated would be this:

    -ALLWAYS - on cell change event (as you hit Enter,or switch to another cell) -this means you don't need to use the update button every time on each column. This option would by most computationally intensive.

    -on RELOAD / PAGE OPEN (only when you open the page or you hit F5 or refresh button in browser)- in this case you need to use "recalc button" (SG refresh button has no effect- the column indicator(=recalc button) stays yellow)

    -SG refresh (2 circular arrows button in SG) - recalculations happen when you come to the page, and when you hit SG refresh button

    -on DEMAND only- recalculation happens only in specific columns and only when the "recalc button" in each column is pressed.

     

    I know it may sound complicated, I am no programmer :-)

    Just a suggestion, what would be nice theoretically.

    Thanx

    Michal

  • 0
    Avatar
    Astrid Scholte

    Thanks Michal and Roman! We really appreciate your feedback and I'll pass this onto our Product Manager.

     

    Kind regards,
    Astrid

  • 0
    Avatar
    Michal M.

    Thank you, I believe many people would appreciate this possibility- even if it is only a simple  + and - operation with timecode. (We would than need to set a preference of framerate in fps somewhere)

    Maybe for the calculations it could be a whole new option in "configure field"- let's say "equation". And there would be possibility (radial button) to choose operation (+, -, and so on). And 2 pulldown menus with all the columns, that would be possible to use in calculation. (now the issue of the type of number comes in to play. And possibly the need to add a check box, that would say- "I want to use this column in equations". But not necessarily)

    So this way the calculations would not be cell based, but column based thing. Of course I don't know, what people want to calculate. TC calculation is the obvious one.

    Maybe >,<, = (inequities?) would be another useful thing- resulting in true/false result. That can be than used in queries as well.

    So this is just another suggestion or possibility, that just occurred to me now.

  • 0
    Avatar
    Tony Aiello

    Here's another simple yet high-payoff use-case for Calculated Fields.  We'd like to create a Scheduled Time Remaining field, which would simply be the # of days between the specified End (Due) Date of a Task and today.  It seems that the Time Logged - Over/Under Bid field could be used, but that requires 1) that the Budget field on Tasks be filled in, which it doesn't get automatically and 2) that folks make consistent use of Time Logs, which doesn't happen often.  It'd be nice to be able to calculate the countdown of days remaining, no budget field nor time logs required.  Certainly doable with the API and custom reports, but would be a lot nicer right on a standard Tasks page without having to write a daily cron to make that calcuation and update such a field on every in-progress Task.(Off on a tangent, hovering-over a 'Time Logged - Over/Under Bid' cell provides the tool-tip help "This is a calculated field comparing 'Budget' and 'Time Logged'.  So the field itself should be called 'Time Logged - Over/Under Budget', not 'Bid'... easy enough to rename per-server...)For now we can at least flag such occurrences with Conditional Formatting (Status is not Complete and End Date is before Today), but the actual overage itself would be good to capture for a production report 'daily dashboard' as well as a show's post-mortem.

  • 0
    Avatar
    Ben Hadden

    Hey guys, I updated the description on this to help others interested in this feature add their use cases. If you haven't already, please add yours as well. We'll be tackling the design on this one based on client uses cases, so the more we have, the better this feature will be.

    Cheers,
    Ben

  • 0
    Avatar
    Andrew Lawrence

    A really useful one for me would be to automatically calculate the duration of a scan in frames (can also be used for cut or comp duration) This would be done by simply subtracting the first frame of the scan from the last frame of the scan and then adding 1

    An example would be

    • A scan runs from frame 1001 to 1200
    • Scan duration = Scan Out - Scan In + 1
    • Scan duration = 1200 - 1001 + 1
    • Scan duration  = 200 frames

    Possibly unrelated to this thread but building upon this concept, it would be really great to validate these fields with rules such as ("Scan Out" should always be greater than "Scan In") and number validation such as flagging an error if the number entered is not 4-digits long. 

    Hope this helps, thanks for all the support on getting this feature implemented

    Cheers,
    Andrew

     

  • 0
    Avatar
    Louai Abu-Osba

    I'd love to be able to do string manipulations using strings from other fields.

  • 0
    Avatar
    David Mason

    It would be great if column summaries gave you an option to change how it summarises with a few simple options like SUM, COUNT, MEAN . . . would be a super help.

  • 0
    Avatar
    Scott A. Shapiro

    Any update on implementing Calculated Fields...?

  • 0
    Avatar
    Ben Hadden

    Hey everyone,

    I have an update on our progress with calculated fields. For those of you who didn't see this, we actually took on calculated fields as a hackathon project (read more here: http://blog.shotgunsoftware.com/2015/02/2014-hackathon.html). It was a lot of fun, and we learned a lot. We actually challenged ourselves to implement and release this feature in 3 days, which forced us to think efficiently about what we'd build. Here's a few things we learned:

    - Keeping the UI simple was great. We opted for a simple text-only implementation where users could use known functions to build formulas. We supported only fields on the current entity where you'd build the field (except in special cases), and it looked something like this:

    Calculated_Fields.png

     

    - We experimented with both number and text calculated fields. Numbers were more straightforward (letting users do addition, subtraction, averages, etc). Text was complicated in that we would have to do more validation on the input. Also, we worried that people would want to start using Calculated Text fields for the name of an entity, which would mean updating things like search, display names, etc to support this. If we released calculated fields, we'd probably start with numbers and then graduate into text in a later release.

    - We found a way to implement this where performance felt fast. When updating a field used in a formula, the user sees any related Calculated fields update almost instantly, which is awesome. However, this is where we have the most work left to do. We need to make sure there isn't a way to bog down the system by updating lots of fields on records with calculated fields. We were testing on relatively small datasets, but many of our clients will build these fields on entities like Version where there are 100s of 1000s of records in the table. We also made it possible to use Calculated fields in other Calculated fields, but didn't get the chance to trickle down their updates (e.g. CalculatedFieldB updates when updating a field used in CalculatedFieldA, which is used in CalculatedFieldB).

    It would be great to hear from those of you wanting this feature if you'd be satisfied with a lean v1 which was limited to the following:

    - Simple UI (as shown above)

    - Only math to start (text coming later)

    - Only number fields on the current entity, excluding other calculated fields and query fields

    I'm removing the "Planned" label from this feature until we officially start it. We have a few projects we'll be kicking off before we dive back into this, but when we do, I'll be sure to update this thread.

  • 0
    Avatar
    Michal M.

    From my point of view, anything is better then nothing :-)

    So YES to the limited implementation first, doing the rest later.

    M.

  • 0
    Avatar
    Tony Aiello

    +1 YES to initial limited implementation!  Simple and math-only covers most of our current use-cases, and I'd vote for access to single-hop-linked fields as the next step after first release.

    However, question: would these behave like query fields in the API -- that is, not API-available?  That would be sad.  It would be awesome to have these calculated server-side upon an API query.  So I'd change my next-step-vote for that, even more than single-hop-linked fields in the calculation.

  • 0
    Avatar
    Ben Hadden

    @Michal, thanks for the validation!

    @Tony, we did implemented these server-side, so yes, they'd be available to the API. Also available to sorting, grouping, filtering, etc.

  • 0
    Avatar
    Alexey Borzykh

    Count me in for the first version implementation which would (totally agree with Tony here) cover most of the use cases. And +1 for a single-hop-link feature as a next update.

  • 0
    Avatar
    Andrew Lawrence

    Thanks fantastic news, +1 from me, basic math operations would sort many of my initial requests. For v1 would you be limited to one operation per calculated field i.e. (A+B) or can you string operations together in a single field i.e. ((A+B) - C)?

  • 0
    Avatar
    Ben Hadden

    @Andrew, you'd be able to string together operations. It turned out to be relatively the same about of work, so we'll support that in v1.

    Keep the questions coming everyone! This really helps us prepare a lean, simple v1 that would be useful to you all.

Please sign in to leave a comment.