Formula for isolating metadata that differs from one another

[Ali_Shannon]

Hi there! Is there a formula for isolating the last available single-select from each group?

I’m looking to only show the Shows that are marked as On-Air but also only want to see their currently airing season. I have a single-select for On-Air or Off-Air, and a single-select field for Seasons, but let’s say Jersey Shore has 6, and Teen Mom has 10 seasons, I only want to see Season 6 of Jersey Shore and Season 10 of Teen Mom. Is that possible?

Thank you in advance!

[Jeremy_Oglesby]

Hi @Ali_Shannon

This sounds like something that could probably be done with a rollup field - but I’m not sure I understand the context of where you want to see this data. Is it in a linked table that you are trying to display only particular shows?

[Ali_Shannon]

I have a base (Long Form) that holds all the episodic metadata (Show Title, Seasons, Episode Title, etc) and another base (Short Form) that holds the videos associated to that show (Highlights, Promos, etc).

From the Long Form base, I’ve marked which Shows are On-Air, and from that list, that On-Air view is synced with the Short Form base. I haven’t figured out how to show which season of the On-Air show is “current”, but I’d like that to display in the Short Form base.

Let me know if that provides some clarity!

[Jeremy_Oglesby]

In your Long Form base, do you have multiple related tables, where:

• An episode as a single record in the Episodes table, and is linked to a…
• season, which is a single record in the Seasons table, and is linked to a…
• show, which is a single record in the Shows table

?

If not – I would suggest doing so :slightly_smiling_face: – it would make the process of finding the “current” season much easier.

[Ali_Shannon]

Yes I do! Not sure of the next steps, but thinking shortform should then be synced with the Shows table that houses the Show, Seasons, and Episodes?

[Jeremy_Oglesby · Accepted solution]

No, I think all the work can be done in the Long Form base, prior to syncing.

I think in your Shows table, you’ll just need a Rollup field that looks at your Seasons linked records, and the field with the Season Number in it. It should use a Rollup formula something like this:

IF({On-Air}, MAX(values))

Now your Show knows the number of the “current season”, but only if the show is On-Air.

Next, over in your Seasons table, make a Lookup field that looks up the “Current Season Number” from the linked Show record.

Finally, make a Formula field that checks if the “Season Number” for this record matches the “Current Season Number” that was looked up, and if so, produces a 1:

IF(
   {Season Number} = {Current Season Number},
   1
)

Now you should be able to use that field to create filtered Views of only On-Air, Current Seasons, and that view can be selectively synced, or that field can be filtered on in your Short Form base.

[Ali_Shannon]

This worked! Really appreciate you walking me through this one Jeremy, this also taught me a lot about the power of the Lookup and Rollup fields, and saves me a lot of time in the long run, thanks so much!