Lookup linked record by name or alias?

1275 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I’m working on designing a form where the end users will need to search for a place name. Some places have alternate names or commonly-used shortened forms of their name that the community is more likely to use instead. When designing a lookup field to the places table, is there a way to look up both by the record’s name (official name) or off of some other table or a field with multiple strings as the alias list?

My alternate option is just to put the list of places as one nasty 500-member single select, but that idea is highly unappealing.

3 Replies 3

Welcome to the community, Chris! :grinning_face_with_big_eyes: It appears there may be a misunderstanding about how Airtable’s lookup fields work. In short, they’re not like search fields, so you can’t use them to locate records by searching for parts of names or keywords. A lookup field relies upon a link field already connecting records in the current table and records in some other table. Via that link, a lookup field can pull data from a single field in that other table into the current table.

For example, say you’re building an invoicing system, and you have a [Clients] table to store client data, and an [Invoices] table where you build your invoices. In [Invoices] you’ve created a {Client} link field that lets you pick a client from the [Clients] table. From that other table you want to bring in the primary contact name, so you create a lookup field, which uses the {Client} field link and grabs what’s in the {Contact Name} field from [Clients].

What you want to achieve technically can be done with a link field. It will only search another table’s records by what’s in the primary field, though. If you want, you could make the primary field a formula type, one that concatenates the official name for a place with any alternate names, perhaps using a format like:

Official Name (Secondary 1, Secondary 2, Secondary 3, ...)

So instead of having a 500-member single-select, you’ll have a 500-record place name table, searchable using a link field. Will that work?

I think that should work. I’ll at least give it a try and (hopefully) remember to update if it worked or not.

— Chris

Update: I just tried this setup and it works just fine.

— Chris