Entering a "Link to another record" when you don't know the record ID

[Craig_Toohey]

Hello there community,

I have an issue I'm not finding an answer to in this wonderfully useful forum. Perhaps I'm just not searching correctly.

I'm using a system of entering Purchase Orders where we have an internal SKU and a supplier SKU. These are one to many. There can be many supplier SKUs for a singular internal SKU. I'm in construction, so for example, the internal SKU is a 16ft 2x4: DL-SPR-#2-2x4-16. An example of a supplier SKU is 20416S.

When I'm receiving invoices, I'm entering the supplier SKU which allows my system to link to an existing internal SKU so that Airtable knows everything about that product (width, thickness, length, weight, etc, grade). However the other way around - if I want to generate a purchase order with the supplier's SKU on there (so they know exactly what I'm asking for, no confusion...) I'm having a hard time conceiving how I can look up the supplier SKU when the user might not necessarily know what that SKU is. So, the user might want to, for example, start typing "2x4"...and then see a list of optional Supplier SKUs to select from. Given that "2x4" is part of the internal SKU and it shows up on the little quick summary card, I thought you'd be able to do this, but apparently not.

In this example, if I type "2", I see the first supplier SKU popping up is this 8ft 2x6 from the local hardware store...

...but as soon as I type "2x6", all options disappear.

This seems like it could be relatively straightforward - when inputting a related record, show search results for data in the related record that isn't necessarily in the Record ID.

One potential solution I think I read about is just to make the record ID default to concatenate both the supplier SKU and the internal SKU. I'm trying to avoid that if possible.

Any ideas? How to give the user the possibility to look up a list of supplier SKUs (without using a lookup field) while entering known information from the internal SKU?

Thanks so much for any insight,

Craig

[kuovonne · Accepted solution]

The forums recently changed software, which is making it harder to find solutions (and harder to provide solutions).

But, in short, you have already found the answer. You must change your primary field to a formula that combines both values. Airtable will only search on primary field values when picking a record as in your screen shot.

It might seem simple to have Airtable search other fields, but it is not. The set of three fields that show up in the preview are not set and can vary. Also, with up to 500 fields per table, possible including multiple long text fields, and up to 100,000 records in the table, trying to search all of that would be very slow.

[Craig_Toohey]

That all makes sense. I guess I was hoping there was maybe a way to search only the quick summary record cards with the Record ID and the first 3 fields (which I understood from another forum are defined by the first view on a table). That'd be cool!

For now I will use the workaround. Thanks!