Skip to main content

Lookup linked record by name or alias?

C

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

    Justin_Barrett
    Forum|alt.badge.img+20

    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?

    C
    • Chris_Matlak
    • Author
    • New Participant
    • 2 replies
    • July 14, 2019
    Justin_Barrett wrote:

    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

    C
    • Chris_Matlak
    • Author
    • New Participant
    • 2 replies
    • July 14, 2019
    Justin_Barrett wrote:

    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?


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

    — Chris

    Reply


    Most helpful members this week

    Terms & Conditions