Access fields from linked record in formula without creating Lookup fields

[David_Skinner]

I’m imagining being able to access the data in a linked record in a similar way to how you access child properties of objects in many programming languages.

Example

You have an ORDERS table and a PRODUCTS table. PRODUCTS contains Item Name, Item Code and Price columns. The ORDERS table has a Linked Record field called Products. You could then access the Products properties within a formula field in the ORDERS table like this:

Products.{Item Name}
Products.{Item Code}
Products.Price

[Kamille_Parks]

agreed! Having many fields that are only used to “import” data into the current table can be tedious to manage when creating views. Plus, having a bunch of columns simply takes up a lot of space, which is why I end up concatenating values together a lot.

[W_Vann_Hall]

This won’t satisfy @David_Skinner’s request, I want to recommend once more the use of aggregation formulas when possible. (See Item 3 in this reply for more information.) While they don’t allow for more than one linked field to be referenced at a time,¹ they do eliminate the need for separate lookup fields simply to pull a single field’s value from a linked record to the current one.

Essentially, rather than defining a formula field that references a separate lookup field, you define a rollup field that rolls up the referenced field you otherwise would have defined to be looked up. Then, whenever you need to refer to that field, you use the keyword ‘values’ instead. For instance, if I have a lookup field {LinkedString} that looks up single-line text field {String} from a linked record in the [LinkedTable] table for use in the formula UPPER({LinkedString}&''), I would instead define a rollup field that rolls up {String} from [LinkedTable] with the aggregation formula UPPER(values&'').

Again, not as complete a solution as being able to dot-reference fields from a linked record, but one that does allow one to do away with having to use multiple lookups simply to access remote, referenced values; even better, it’s available now.

(Caveats: Fields that return multiple values are as difficult to handle in this manner as they would be using a lookup field. You also can’t do that thing where you use a lookup field to look up a linked record field and have the retrieved values remain clickable links [that is, you can’t do so with a rollup field], And any time you’d have to force a looked up array to a string by appending ‘&''’, you’ll probably have to do the same, using ‘values&''’.)

---

1. If I need to pull several non-array values, I’ll often create a portmanteau field in the linked record — for instance, something like

   01:value1|02:value2|03:value3|

   and break out individual components in my aggregation formula.