Automation to link plays to seasons (Theatre)

[Mary_Giles]

I am creating a theatrical production database so we can link things like productions to volunteers and current season workflows. Since I’m putting in records from our past, I would like the new production record automatically linked to whatever season that production is in based on when the opening date is.

Is there any way to create an automation that updates records conditionally?
i.e. Trigger- a record with an filled “Opening Date” field and an empty “Season” field; Action- update linked “Season” field based on “Opening Date” being within the season start and end dates.

[Justin_Barrett]

Welcome to the community, @Mary_Giles! :grinning_face_with_big_eyes: This should be doable, but a few more details will help to determine exactly how to pull it off:

1. How are your “Season” records named? Are they just numerical (e.g. 2018, 2019, 2020, etc), or is there more to the name?
2. How are your seasons divided chronologically? Does each season exist within a single calendar year, or does it span adjacent years?

[Mary_Giles]

Thanks for the reply!

We number our seasons and they span adjacent years starting September 1 and ending August 31. For example, our Season 0 is from 9/1/1953 to 8/31/1954 and our Season 69 is from 9/1/2022 to 8/31/2023.

In my season table, I have the primary field as the season number (68), a date field for Start Date (9/1/2021), a date field for End Date (8/31/2022), and a linked field to my Productions table.

[Justin_Barrett]

Sorry for the delay. Long story short, Airtable formulas don’t currently have the power to do what you want, but a script can do it quite easily. I can picture this working via an automation that runs a script on each new record (after certain conditions are met) to link a production to its associated season record. If you’d like to discuss options for setting this up, message me directly.