Lookup field to link to record on other table

[nimble]

I have a new repair business. I have two tables related to this question:

One contains People, some of whom own items that they want repaired. The table includes contact info and the repairs they have submitted; the latter is a linked record field.

The other contains Repairs, linking back to the item owners, providing all the details of the repair need, and indicating the current status in the repair process.

I want to include a field in People showing Active Repairs, which are those that match certain statuses representing "incomplete." I've created this as a Lookup field and tried setting the source field a few different ways:

• If I set Repair Request, which is a formula field, I end up with an array of text items matching the names of the Repairs that are not clickable to link to those Repair records.
• If I set Repair ID, which is an autonumber field, I end up with an array of text items matching the Repair ID numbers that are equally not clickable to link to those Repair records.
• If I set Item Name, which links to another table, I end up with an array of items that link to the Item Name records rather than the Repair records.

Is there any way to set up a field on the People table that dynamically updates as the status of an associated Repair changes and links to Repair records that match the acceptable statuses?

[TheTimeSavingCo · Accepted solution]

Ha, I managed to figure out how to do this but the solution seems pretty silly, but it works!  Hopefully someone else has a better idea.  In the meantime, try this:

In "Repairs", create a linked field to itself and populate it by linking each record to itself:


In "People", create a conditional lookup field to only display "Active" repairs:

And that results in this, and we can click in to them:

--
To handle the linking, we'd just trigger an automation every time a new "Repair" record's created to link itself to itself

Link to base

[nimble]

Thanks, @TheTimeSavingCo! I was trying to figure out how to do something like this and you made it work. The biggest hassle was having to manually fill the identity of the 80 repairs already in the table. Fortunately, their names are mostly unique so I only had to type a few characters and press return.

I don't know if there's a preferred method to this but this works fine for my needs.

My bigger frustration with Airtable is that keyboard navigation of lookup/linked fields is limited to arrow keys and, unlike a spreadsheet, I can't press return to go to the next row.