A Lookup Field that also Links to Record


#1

I am looking up a field from another table but it comes in as a text value. I would like it to be clickable like the linked fields so I can jump directly to the record.

Any ideas how to do that?


#3

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.


#4

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.


#5

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


#6

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.


#7

@W_Vann_Hall Thanks for explaining.

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


#8

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.

#9

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’.