70

Calculated Fields

Don Parker

July 8, 2009 at 12:03 AM

Completed

Follow Updates

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.

• Facebook
• Twitter
• LinkedIn

46 comments

• 
  Nephi Sanchez August 16, 2017 at 5:32 PM 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
  
  Stu Aitken July 8, 2009 at 1:55 AM

  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
  
  Don Parker July 8, 2009 at 4:28 AM

  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
  
  Stu Aitken July 8, 2009 at 12:37 PM

  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
  
  Tommy Kiser July 8, 2009 at 3:55 PM

  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
  
  Pliny (John Eremic) July 14, 2009 at 2:29 AM

  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
  
  Stu Aitken July 16, 2009 at 2:47 PM

  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
  
  Stu Aitken July 16, 2009 at 6:07 PM

  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
  
  david maas August 6, 2009 at 12:52 PM

  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
  
  Paul Hudson January 7, 2011 at 11:35 PM

  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
  
  Stephen Chiu January 12, 2011 at 3:54 PM

  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
  
  Eli Rarey October 23, 2012 at 11:16 PM

  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
  
  Astrid Scholte October 25, 2012 at 5:03 AM

  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
  
  Roman Ignatov November 2, 2012 at 1:46 PM

  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
  
  Michal M. January 15, 2013 at 9:55 PM

  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
  
  Astrid Scholte January 16, 2013 at 2:52 AM

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

   

  Kind regards,
  Astrid

• 0
  
  Michal M. January 16, 2013 at 7:46 AM

  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
  
  Ben Hadden March 12, 2014 at 8:18 PM

  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
  
  Permanently deleted user March 17, 2014 at 12:07 PM

  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
  
  Louai Abu-Osba May 6, 2014 at 5:29 PM

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

• 0
  
  David Mason May 6, 2014 at 6:31 PM

  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
  
  Scott A. Shapiro October 13, 2014 at 7:24 PM

  Any update on implementing Calculated Fields...?

• 0
  
  Ben Hadden February 9, 2015 at 9:58 PM

  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:

  

   

  - 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
  
  Michal M. February 10, 2015 at 12:17 AM

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

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

  M.

• 0
  
  Ben Hadden February 10, 2015 at 12:49 AM

  @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
  
  Alexey Borzykh February 10, 2015 at 1:13 PM

  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
  
  Permanently deleted user February 10, 2015 at 5:08 PM

  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
  
  Ben Hadden February 10, 2015 at 11:05 PM

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

• 0
  
  Janis Robertson March 7, 2015 at 12:46 AM

  I agree that even starting out with a simple implementation of this would be very useful. 

• 0
  
  Jane Forsyth June 17, 2015 at 2:23 PM

  Is there a way that you can add in different rates for artists per task and the costing stage which then goes through into production. Then once you add the bids in and they start to log hour the time left is calculated on their rates? This would be very useful