I’m trying to automate period assignment in Airtable and want to check whether I’m missing something or if this just isn’t possible without scripting.
I have a Periods table where each record has a Start Date and End Date for a 13-period fiscal year. In other tables like Orders, Packaging, and Transfers, each record has a single Date. What I want is for a record to automatically link to the correct Period when it’s created or when its Date is updated, based on whether that Date falls between the Period’s Start Date and End Date.
I haven’t been able to find a native way to do this. Automations don’t seem to allow comparing one date field to another date field, Find records can’t evaluate date ranges using the triggering record’s Date, and rollups and filters don’t allow referencing parent record fields. Because of that, the only options I can see are manually selecting the Period or using a script.
Has anyone in the community has found a native way to automate date range based period linking, or if scripting really is the only reliable approach here.
I’m also a bit surprised there isn’t a way to create something like a Months table and roll up data from other tables by month without manually assigning a month to every record, which feels like a pretty common reporting need.
Thanks in advance for any insight.
