32

Filter by comparing columns

There are often moments when it would be quite handy to be able to set up a filter where you compared the values of two columns, e.g. "Column 1" "does not equal" "Column 2". This becomes particularly powerful when you can compare linked fields - e.g. you could find all the places your triggers have gone wrong by finding places where a field on a task doesn't match a field on its linked shot (if that makes sense?)

17 comments

  • 0
    Avatar
    Mike Romey

    We do some financial data and comparing fields would help our accounting peps a lot.  For example if the Actuals Hours are higher than the Bid Hours, heads need to roll. 

    -Romey

     

  • 0
    Avatar
    Don Parker

    Interesting idea.  Would this be something you'd want to do in the UI?  Or is this an API thing?  What do you think the UI should look like?  Maybe:  [field 1] [does not equal the field] [field 2] ?

  • 0
    Avatar
    Andy Geers

    Yes, definitely in the UI. Your suggestion is exactly how I imagined it: a new comparison type "does not equal the field" or "equals the field", and then you get the same field dropdown on the right hand side as you get on the left.

  • 0
    Avatar
    Mike Romey

    Yup, aggreed.  Spot on!

    -Romey

  • 0
    Avatar
    Michael Beal

    This is something we would like as well. 

     

  • 0
    Avatar
    Patrick Boucher

    This would be extremely useful for us as well.

    The lvalue of a filter defines the valid operator list. This operator list could be extended to have "field" variants ("equals field", "contains field", etc... - or a new "field" checkbox) that turns the rvalue from an explicit textfield to a field dropdown (like the lvalue).

  • 0
    Avatar
    Mark Visser

    Another real-world use case for this came up today. Notes attached to a version/task/shot are often useful to downstream tasks. It's hard to show them in context, though.

    For example, say in a review session for a lighting version it is decided to fix something in comp. The coordinator creates a note (by default attached to version/lighting task/shot). For the comp artist to see the note, the coordinator must explicitly link the comp task. It's not easy to do in the heat of the moment, so it is often forgotten.

    Instead, we want to include notes where (Links -> Shot -> Shot Code == Current Task -> Links -> Shot -> Shot Code) on the *task*. That way the comp artist can see all relevant notes in context without having to bounce back and forth between the shot and task level.

    When (eventually) Pipeline Steps become first-class citizens of the entity world, we could limit that to only notes on upstream tasks to keep the signal-to-noise ratio down.

  • 0
    Avatar
    Kimberly LeBrane

    This would be very helpful. Especially the ability to compare dates and hours. Also would offer a good workaround for not being able to link versions to other versions.

  • 0
    Avatar
    kevin campbell

    another 'me too'

  • 0
    Avatar
    Dení C. Gloria

    This would be really useful for us too.

  • 0
    Avatar
    Stéphane Hoarau

    Yes, please !

    +1

  • 0
    Avatar
    Chris Jarvis

    This would be so helpful! Especially if you could compare dates!

    turn_row_red = entity['due_date'], 'is after', entity['planned_due_date']

    +1!

  • 0
    Avatar
    Tony Aiello

    Weighing in on this one (again).  Being able to filter by a field on the "Current <Entity>" virtual entity would be amazingly helpful.  One use-case would be cumulative totals.  Here's what I mean:

    In order to do production reporting on a weekly basis, I've created a Week entity (using a CustomEntity).  Each Week has a Start Date field, populated as the Monday date of each Week.  We then create a set of Weeks that cover the production duration of the Project.  I've implemented various triggers using Patrick Boucher's excellent shotgun event daemon that do things like:

    - stamp the "scheduled Due Week" on Shots (based on the due-date of the final animation Task linked to the Shot)

    - stamp the Due Week on Tasks, based (again) on the due-date of the Task

    - stamp the Approval Week on Versions, based on a Status-change event of the Version from in-progress to Fully-Approved

    These allow me to "bucketize" Shots and Tasks and Versions via Query Fields on the Week entities.  For example I have a Query Field on the Week entity for Shots Scheduled by Week that works via the filter condition of Shot > Last Animation Task > Due Week -- is -- Current Week.  This works remarkably well (defined as being both fast and accurate.)

    But it's also desirable to have a CUMULATIVE Shots-per-Week count.  E.g. if the per-week counts are 2 shots in week 1, 3 shots in week 2, 5 shots in week 3, the cumulatives would be 2, 5, 10 for those same 3 weeks.  The best way to achieve that would be to create another very similar query field with an additional OR'd condition Shot > Last Animation Task > Due Week > Start Date -- less than -- Current Week > Start Date.  In this case "best" is defined as fast + accurate + requiring as little work on my part as possible.  ;-)

    For Shots, you can achieve this in another way, but you won't like it:  create a multi-entity field called Shots Cumulative on the Week entity, and run a cron on some periodic basis that does the same .summarize() call that the Shots Complete Query Field does but then sums up each result per week and stamps those Shot lists back onto each Week's Shots field, sequentially per week.  This then yields a reverse field Weeks <-> Shots Cumulative that can be used in place of the is-Current-Week filter condition.  The downside is the seemingly-simple batch update for stamping all those Shot lists causes a LOT of internal table explosions and gymnastics and is thus both slow and inefficient (although still accurate) -- in other words, eventually the furthest-out Week entity will have ALL the Shots for the Project stamped in its Weeks Cumulative field!  Ugh.

    Now consider that you probably have an order of magnitude more Tasks and Versions per Shot.  Based on the Shots Cumulative performance implications, I can't advise creating Tasks Cumulative and Versions Cumulative fields...  So you can't use this approach to week-accumulate Tasks and Versions.

    That simple date filter comparison on the Start Date field of the virtual "Current Week" would avoid all that complexity of big-ass tables + fields + triggers + crons oh my.

     

  • 0
    Avatar
    Eleroy

    Is there any word at all if some feature like this is planned?

    I also need to filter entities based on dates by comparing multiple dates. (Example: If shot due date > sequence due date, etc..)

  • 1
    Avatar
    Blaze Wallber

    I would like to see this in my facility so that I can format rows according to entity comparison. For example if the 'Next Delivery' date is set after the 'End' date, then the row should turn red.

  • 0
    Avatar
    Tony Aiello

    The key to this is getting Calculated Fields to support Date and Datetime fields.  Then you could have a calculated field that subtracted your sequence due date from your shot due date, and return the result in days.  Then do your filtering on the calculated field.

    For the coloring-case, you'd take the same approach, which is a highly-related request https://support.shotgunsoftware.com/hc/en-us/community/posts/209480228 .

    Come on Shotgun, Calculated Fields already support math operations on Task Durations... let's get Dates at least!  Everyone here, please comment on https://support.shotgunsoftware.com/hc/en-us/articles/115000919233-Calculated-fields and demand Date support in Calculated Fields!  :-)

  • 0
    Avatar
    Blaze Wallber

    Thanks for linking these tickets Tony - voted!

Please sign in to leave a comment.