Field Type: value generated automatically and/or by query

Let me describe what I'm trying to do first then back my way into a feature request.

My company differentiates between Projects and Jobs.  A project is an umbrella with basically two statuses, open and closed.  Underneath that we have jobs with specific scopes, and typical statuses such as Bidding, Awarded, On Hold, Active etc.

Projects are assigned simple handles instead of inscrutable strings of numbers.  Our project for the feature film Fair Game, for example is called ... fairgame.  Jobs underneath are assgined job codes that are simple concatenations of the Project Handle and a 3-digit, leading-zero, auto-incrementing number: job fairgame001 is camera rentals and production services; fairgame002 is dailies and data management; fairgame003 is Digital Intermediate.

I'd like to maintain this system inside Shotgun without having to manually enter job codes or remember what the next digit should be.

I realize, too, that if I were being super-database man, I could simply store the project code as an integer (1,2,3...) and link the jobs table as a many-to-many with projects.  Then I could simply let a form or other view page _display_ the job as "fairgame003" via a query.  But I think that a small amount of redundancy can be worth it; I don't want have to create a query every time I display a job code on a page.  (Unless the SG GUI ends up doing all of this transparently.)

I've coded this functionality into my current home-brew job tracking system, which I built 5 years ago and am dying to EOL.  So I know what I am asking for is at least a minor pain in the ass.

Obviously, what I'm describing also needs to be abstracted a little higher.   I'm curious how the SG guys might approach this abstraction.

For example, we could create a Field Type called "Query" or "Autogen" or the like, then use either a type of query builder or query syntax (yikes) to build "$projectname . &autoincrement(&jobcount->[$projectname,3])" -- which would result in fairgame004 as my next job code.

Of course I'd want this to be flexible enough to create the names of the child entity "batch" which would be called (fairgame_batch001), or any subsequent grandchild entities (fairgame_batch001_output001), etc.

I have a genuine need for a Field Type that users cannot edit but are simply assigned based on on ore more parameters (parent entity names, auto-incrementing numbers, etc.).

Thoughts on this are welcome.


  • 0
    Peter Devlin

    We have a similar approach to aspects of our job creation system.I could find a use for auto-calculated field values.

  • 0
    Stu Aitken

    what peter is referring to here is something slightly simpler - ie our projects have an ID code (A####) , the project name itself (projectname) , then a long name that defines the primary project directories (IDCode_clientname_projectname), a short name used in various places like email headers ([PROJ]), renderfarm submissions various bits of asset managing pipeline code

    the ID code part is simply an incremental integer with an A tacked on the front

    in this instance however I'm not sure Shotgun is the right place to define this - at the moment our project environments are setup via python code and a properietary UI that also sets the project IDcode and we would need to decide that shotgun was the right place to define this to really use an auto incrementing feature here (then ourt project setup scripts could presumably lift this from shotgun via the API)

    apart from this though there are a couple of obvious places where it would be VERY useful indeed to do able to create  'filemaker style' calculated fields where we could compute something from existing fields and store it/display it - the biggy that springs to mind is working out overages by doing [logged time - bid time] for instance

    I guess that does throw up a can of worms around how any such fields get updated when the underlying data changes...

    regardless this is soemthing we would love to see

  • 0
    Pliny (John Eremic)

    At Offhollywood, job codes are also generated in proprietary (Perl) code. However, the app is 5.5 years old and too cumbersome to support with only one person (me), which is why we're looking to transition as many features as possible to SG and EOL our internal software.

    Job codes are the tip of the iceberg however since other entities such as production media batches, transfer work orders, &c. all have auto-incrementing IDs as well.

    Right now we have to manually enter and track these numbers.  As a stopgap, I could manually enter "jobcodeXXX" as long as SG was auto-generating the number "XXX".

    As a second step, it would be great to define our naming conventions in SG so that SG would automatically name a new batch of RED media as "jobname_batchXXX" (for example).  That would require a field auto-incrementing on at least a per-project basis (but preferably something more flexible, i.e. auto-incrementing on a per-parent-entity basis, with eligible parent entities including custom entities).

    And because it's worth it to sweat the little stuff: leading zeros would be important to me here, too.