Cannot Trim Lookup from Linked Record

[Ryan_Reich]

The TRIM function doesn’t seem to work when referencing a Lookup field in a Linked Record. Every time I try to trim the data that’s passed from a lookup in a linked record, my formulas return #Error. Here’s the formula at fault:

TRIM({AMID (from link)})&"-"&TRIM({AMID})

[ScottWorld · Accepted solution]

Welcome to the community, @Ryan_Reich!

Lookups are arrays, so they can’t be trimmed because they’re not a text string. But you can turn a lookup into a text string by adding empty text to it like this:

TRIM({AMID (from link)}&"")&"-"&TRIM({AMID})

[Ryan_Reich]

Thanks for the response. I’m confused about why the lookup field is treated as an array when it seems like it’s just copying data from a cell attributed to another record. Shouldn’t it be treating that data the same as the original?

[Kamille_Parks]

Lookup fields are an array because they are pulling data based on a LinkToAnotherRecord field, and those fields are arrays. Even if the record in question is only linked to one record, the value for a LinkToAnotherRecord field is always an array.

[Justin_Barrett]

Welcome to the community, @Ryan_Reich! :grinning_face_with_big_eyes: I made a cheatsheet (a table) that lists all field types and how they’re treated by Airtable when rolled up. Most of the time they’re going to be arrays, but there are a few cases where Airtable treats them—for the purposes of formulas, at least—as single values when only a single record is looked up.

Airtable

Airtable Field Reference - Airtable

Explore the "Airtable Field Reference" view on Airtable.

[Ryan_Reich]

Thanks! This is really helpful!