Help

Cross-reference by date range in another table

Solved
Jump to Solution
1677 2
cancel
Showing results for 
Search instead for 
Did you mean: 
UnassumingUser
4 - Data Explorer
4 - Data Explorer

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
TheTimeSavingCo
18 - Pluto
18 - Pluto

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
TheTimeSavingCo
18 - Pluto
18 - Pluto

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!