Automatically add reference when start date falls within date range

[dwilliamskk]

Hi all,

I'm somewhat new to air table automations and I'm trying to figure out a way to complete this task:

I have two tables

• Revenue Plan (applicable fields: Week Starts [Date], Week Ends [Date], Week [Formula])
• Promotion Calendar - PM (applicable fields: Date Start [Date], Revenue Week [Reference])

When the user fills in a record with a Date Start on the Promotion Calendar table, I'm wanting it to automatically add a reference to the record in the Revenue Plan table whose range of dates encompasses the date entered.

For example: if the start date in Promotion Calendar is entered as 8/10/2023, it should link the record in Revenue Plan that contains the Date Start: 8/6/2023 and Date End: 8/12/2023.

What would be the simplest way to accomplish this task?

[pressGO_design]

Leaving aside the question of what the trigger is, I would suggest using an automation that finds the record in the Revenue Plan table that matches the conditions you laid out above and then updates the record in the Promotion Calendar table with the RecordID of the record it found.