I’m managing service requests (tickets) for apartments using Airtable and need help creating a more advanced lookup field. Here’s how my base is set up:
Tickets Table
- Each record represents a service request (ticket).
- Each ticket is linked to the apartment it is for, using a linked field to the Apartments table.
- Tickets have a Tags field (multi-select) where multiple tags can be assigned (e.g., Appliances, Plumbing, Internet, HVAC).
Apartments Table
- Each apartment is a record in this table.
- It is linked to all tickets associated with that apartment.
Current Setup
- In the Tickets Table, I have a Lookup Field that retrieves all other tickets associated with the same apartment as the current ticket. For example:
- Apartment A has three tickets:
- Ticket 1 (Tags: Plumbing, HVAC)
- Ticket 2 (Tags: HVAC, Internet)
- Ticket 3 (Tags: Appliances)
- When viewing Ticket 1, I can see a list of all three tickets because they are all linked to Apartment A.
What I Want to Do
- I want to create a field in the Tickets Table that only shows related tickets from the same apartment where any of the tags overlap with the current ticket’s tags. For example:
- If Ticket 1 (Tags: Plumbing, HVAC) is the current ticket, the field should show Ticket 2 (because they share the "HVAC" tag) but not Ticket 3 (because there are no overlapping tags).
- If Ticket 2 (Tags: HVAC, Internet) is the current ticket, the field should show Ticket 1 (because they share the "HVAC" tag) but not Ticket 3.
Question:
Is there a way to achieve this in Airtable, considering that each ticket can have multiple tags? Can this be done using formulas, rollups, or another method? Any guidance would be greatly appreciated!
