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 aClients] table to store client data, and an nInvoices] table where you build your invoices. In nInvoices] you’ve created a {Client} link field that lets you pick a client from the tClients] 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?
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 aClients] table to store client data, and an nInvoices] table where you build your invoices. In nInvoices] you’ve created a {Client} link field that lets you pick a client from the tClients] 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
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 aClients] table to store client data, and an nInvoices] table where you build your invoices. In nInvoices] you’ve created a {Client} link field that lets you pick a client from the tClients] 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?
Update: I just tried this setup and it works just fine.
— Chris
