Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Cross-reference by date range in another table

Solved
Jump to Solution
196 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello all, 

I am trying to figure out if it is possible to a column in Table1 show matching names from Table2 based on those names falling into a date range specified on Table 1. I'll try to explain further:

Table 2 will have the data:

Entity | Start Date | End Date

And Table 1 will have 4 columns:

Person  |  Start Date  | End Date | Matching Entity from Table2

For example, where Person 1 on Table 1 has a date range that overlaps with more than 1 Entity from Table 2, the "Matching Entity" column on Table 1 will list all Entities whose date ranges overlap with Person 1 (E.g. - "Entity1, Entity3, Entity 6")

Does anyone know if there is some way of accomplishing this with Airtable? 

1 Solution

Accepted Solutions

Yeap, you could make a queue system to run an automation that would link the record from Table 1 to all the records in Table 2

You'd add lookup fields in Table 2 to display the `Start Date` and `End Date` value of the linked `Table 1` record, and use a formula field to figure out whether there's overlap

Then you'd use another automation to find all the records where the formula indicated there's overlap and link them to the record from `Table 1`

Script option's always on the table as well if you're on a Pro account

See Solution in Thread

2 Replies 2

Yeap, you could make a queue system to run an automation that would link the record from Table 1 to all the records in Table 2

You'd add lookup fields in Table 2 to display the `Start Date` and `End Date` value of the linked `Table 1` record, and use a formula field to figure out whether there's overlap

Then you'd use another automation to find all the records where the formula indicated there's overlap and link them to the record from `Table 1`

Script option's always on the table as well if you're on a Pro account

Thank you for your suggestion! It is a great idea and I will definitely consider it. Thank you for your help!