Link to a particular record when multiple options exist

[Warren_MacPhee]

When creating a new record in table A with a linked field to table B via a csv upload, how can I select which record in table B is selected for the link? Airtable seems to only select the first record it finds, regardless whether I’ve set the lookup field criteria to only ‘limit record selection to a View’ and sorted that view so my desired record (to link to) appears first.

Use Case: Table A is my list of items (SKU’s) purchased by various Purchase orders. Table B is my list of sales transactions. Each SKU sold (Sales table) needs to link to which Purchase Order (PO table) it was from. When a new sales record is created, the SKU field links to the PO table but since many products have been re-purchased multiple times over the years, there are multiple matching candidate records to link to. Airtable seems to only select & link to just the first matching record it finds (which is usually the oldest) and I want it to link to the most recent record. Limiting the linked field to only use records from specific view and the sorting that view Newest-to-Oldest doesn’t solve the issue.

Maybe there is another way to solve this issue than the way I’m approaching it?

[Kamille_Parks]

Generally for CSV imports, convert the relevant field to a Link to Another Record Type in the table that is the “many” part of a many-to-one relationship, not the other way around, first.

Can you share your base here? It sounds like you have three tables and I need to know a SKU is linked to both a Sale and a Purchase Order.

[Warren_MacPhee]

Thanks for the response. I’d prefer to not share the base. To clarify though, I only have 2 tables involved - a PO table and a Sales table. In my Sales table (which will have many sales transactions that will link to 1 PO) I have a field set to “Link to Another Record” (limited to a View) type and it links to the SKU in my PO table. In my PO table, I have field set as “Link to Another Record” (multiple record linking enabled) linking the SKU (in the Sales table) that was sold. I csv load new sales transactions by SKU into the Sales table where the SKU data is loaded into this linked feld. The issue is that Airtable then looks to the PO table as per the Linking (defined by the field type), finds multiple entries in the PO table for this SKU and just picks the first record to link to. I want it to pick the last record to link to which will be (as per my sorted View in the PO table) the latest PO in which that SKU was actually bought. Any suggestions?

[Kamille_Parks]

If [Sales] has a Link field to [PO] and its showing {SKU}s, that means {SKU} is the primary field of [PO]. The fact that Airtable has to guess which record in [PO] your referencing by adding a {SKU} means you have duplicates. While Airtable doesn’t enforce this, it is best practice to never have duplicate values in the primary field of a table to avoid this exact issue.

Are you trying to make {SKU} identify which product was sold as well as which purchase order a sale belongs to? If that’s the case it simply won’t work; a unique identifier can only uniquely identify one thing. You probably do need three tables. One for SKUs, one for Sales, and one for Purchase Orders.\

Here’s an example base I made while back. ([Items] = [SKU] and [PO Line Items] = [Sales])
Public Link:

Airtable

Holli - Airtable

Explore the "Holli" base on Airtable.

[Warren_MacPhee]

Thanks Kamille - I’ve reviewed the base you shared and using it’s structure, my question would be: when a new record is created in the PO Line Items table, how can you influence which record in the Purchase Order table is picked to link to (without manually reviewing the options & picking one). I would want Airtable to pick the most recent applicable record in Purchase Orders to link to and I’m not sure how having the 3rd table for Items enables this?

[Kamille_Parks]

It won’t link to the most recent record automatically. A good workaround is to just go to the PO table, copy the latest entry’s name, and then paste it into the the relevant PO Line Item record.

[Warren_MacPhee]

Ok thanks, I’ll give that a try.