Skip to main content
Solved

Cross-reference by date range in another table

  • December 9, 2022
  • 2 replies
  • 41 views
U
Forum|alt.badge.img+2

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? 

Best answer by TheTimeSavingCo

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

    2 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31
    • TheTimeSavingCo
    • Brainy
    • 6460 replies
    • Answer
    • December 12, 2022

    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

    Adam, Airtable Consultant - thetimesaving.company
    U
    Forum|alt.badge.img+2
    • UnassumingUser
    • Author
    • New Participant
    • 1 reply
    • December 13, 2022

    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!


    Terms & ConditionsAccessibility statement