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?