Jul 11, 2019 07:16 PM
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.
Jul 11, 2019 07:45 PM
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?
Jul 14, 2019 11:37 AM
I think that should work. I’ll at least give it a try and (hopefully) remember to update if it worked or not.
— Chris
Jul 14, 2019 12:09 PM
Update: I just tried this setup and it works just fine.
— Chris