Grouping with filtering options

[jamesharding]

Hi community!

I have an airtable that tracks VFX shots as they come in from vendors.

One sheet (delivery) tracks every shot as it comes in, including the same shot with a different version number due to fixes or changes

On a separate sheet (topsheet) I have a master list of all the VFX shots in the film

On the delivery sheet, I link each record to the master vfx record on the topsheet. That way I can see every version of a VFX shot delivered and track comments

The issue I have is that the producer likes to see a list of every shot that is either 'not started', 'has issues' and 'passed quality check'. The issue is, because multiple deliveries are linked to the master shot, the master shot can be both 'has issues' and 'passed quality check' as the old versions are linked. Is there a way to group the 'topsheet' so that one group is all shots 'not started', all shots that have issue but none that have passed qc, and a group that is any shot that has at least one 'passed qc'

Thanks

James

[TheTimeSavingCo]

Could you provide screenshots of what your view looks like currently and what you'd like them to look like instead, as well as all the data from either table that would be used for said view please?

Would love to help but finding it difficult to visualize what you're looking for!

[jamesharding]

Hi Adam,

Of course I can do screenshots!

Screenshot 1 shows the delivery table where every VFX shot is registered when delivered. The sample shows the shot delivered as 'v1' which we marked had some noise in the hair. They then delivered it again a month later as 'v2' and it was fixed. Column 2 is where I link each record to the top sheet, so that all the VFX of the same content is linked together. The 'online status' column is where we track if a delivered shot has 'issues' or has 'passed'

Screenshot 2 shows the topsheet table where each unique VFX shot is listed. In the example, you can see I have linked both V1 and V2 to the record and a lookup function shows me the status of the shot. However, I can't tell airtable that the successive V2 has rendered the initial 'issue' redundant, it shows both status of the record.

Screenshot 3 shows how the issue gets really complicated when a vfx shot has multiple versions each with a different status.

Ideally, either the latest version of the VFX shot is the status or 'pass' always overrides an 'issue'

Thanks

James

[TheTimeSavingCo · Accepted solution]

Hi James, thanks for the screenshots and the details!

Yeah, this is a pretty common issue users face, and thankfully there's a known workaround, albeit slightly convoluted

I've put it together here for you to check out, and you can duplicate it to view the formulas I used

And here's some screenshots of it in action:

The idea is to create a "Created time" field in your "Deliveries" table, and then create a rollup field in your "VFX Shots" table on that field, with the formula "MAX(values)".  This'll return the most recent created date for all the deliveries linked to that shot

You'll then create a lookup field in your "Deliveries" table to grab that most recent date, and then use a formula field with "IS_SAME()" to check that record's created time with the lookup field value.  This will identify the most recent delivery for that VFX Shot and is called "Is Latest?" in the screenshots above

And finally, we create a lookup field in the "VFX Shots" table with a conditional to only show records which have an "Is Latest?" value of 1, thereby showing us only the values from the most recent delivery

Sounds complicated, but it's pretty easy to set up and you should be able to do so!

[jamesharding]

Thank you so so much for going into all that effort, I have got it working successfully and now have a much better idea of rollup 🙂 Have a great week!