Cross-reference by date range in another table

[UnassumingUser]

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? 

[TheTimeSavingCo · Accepted solution]

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

[UnassumingUser]

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