A Lookup Field that also Links to Record

A Lookup gets values from fields of an already Linked Record, you can click on those linked records.

Also, if the field that is Looked Up is indeed of Linked Record type, you could also click on that, because the original field is a Linked Record.

Thanks @Elias_Gomez_Sainz

The linked records are in a different column. The field that is looked up is a formula.

It is essentially a subset of the linked records where only the records that meet a specific condition are looked up.

I don’t understand, can you explain in detail or leave an screenshot?

Short answer is, you can’t. Unfortunately, that’s the long answer, as well.

Once you do anything with that linked record — use it in a formula, roll it up to another table, whatever – it ceases to be a linked record and merely becomes text. This applies to even the simplest, non-transformative, reference: Create a new formula field, with a formula that consists of simply the name of the linked-record field. You’ll notice the formula field is a different color than the original linked-record field, and you cannot follow it as a link.

I know of no solution or workaround; you’ll need to restructure your data model to find some other way to reach your goal.

@W_Vann_Hall Thanks for explaining.

Perhaps there is a way to filter on a lookup field based on another field value?

Well, kinda — depending on where that ‘other field’ is located.

If it’s in the target table¹ you can create a formula field that performs some level of pre-filtering and then do a rollup or lookup against it. For instance, say you want to return the value of {Field 1}, but only in cases where {Field 2} contains a value. You could create a formula field, {Field 3}, with the formula IF({Field 2},{Field 1}), and then perform your lookup or rollup on {Field 3}.

Similarly, if the other field is located in the current table, you probably can create an aggregation formula that would allow a selective rollup of values. That’s a little less certain, though, as aggregation formulas are currently an un[der]documented feature of rollup fields — but one, I am told, almost certainly to be maintained as Airtable progresses.²

Neither of those approaches, obviously, gives you what you were initially seeking — namely, a way to ‘step’ through a progression of linked records — but possibly one will fill the business operational need you hoped to address through that linked-record chain.

1. We really need a common vocabulary describing aspects of Airtable design. For my part, I use ‘drill through’ to describe the action of selecting a value in a linked-record field and having the referenced record open in Airtable’s ‘expanded record’ view. (I hope what I mean by ‘referenced record’ is clear from context.) The table from which a lookup or rollup field draws its values I call the ‘target’ table; I’m not really satisfied by that term, but there are problems with the alternatives I’ve considered, ‘destination’ or ‘remote.’
2. I find I rarely use lookup fields any more, as I can [seemingly] accomplish the same thing with a rollup field with an aggregation formula of just the keyword values. That way, if I need to add more processing to the field, I can simply modify the aggregation formula instead of having to change the field type.

Thanks

I believe thats what I did and like you mentioned it does not allow the drilldown.

I am not familiar with the aggregration formula but it doesnt seem relevant hear as the field being filtered against is in the ‘target table’.

We need this ability. This needs to be added to the mix of future functionality.

@Michael_Horwitz what was your eventual work around?

There’s no good way yet. What I personally would like is the Rollup filtering functionality available when I use a Lookup field on a Linked Record field in another table. I absolutely love that I can use Linked Record fields in Lookups, but I utterly hate that I cannot apply a filter or even remove duplicates to the same Linked Records.

See this thread for related thoughts: How can I make a lookup field show only unique results?

I'm very late to the party but since I was looking for a solution to that exact same problem and couldn't find any, here is how I solved it :

- in the target table (the one being looked up), I created a new linked-records column called "self-reference". This column has the table targeting itself, with a dynamic filter applied so that each record can only target itself (in my case, i have a unique_id for each record, so I used "WHERE unique_id IS unique_id". This gave me a column where each record basically stores a link to itself.

- in the original table (the one I want to look up from), I created a lookup to the target table, with the appropriate filter(s) on any relevant field I want to filter by, and I set the "self-reference" field as the field being looked up and voilà.