How to Auto-Fill Item Details From Another Table in Airtable
If you have a master table of approved items and a requests table where employees submit what they need, the instinct is to have Airtable auto-fill the item details when someone types a matching part number. That's similar to how you'd use VLOOKUP or XLOOKUP in Excel.
Airtable is a database, not a spreadsheet. The correct approach keeps data in one place and uses linked records to display it elsewhere rather than copying values between tables.
The right structure
Item Master is the single source of truth. It has the primary field set to the part number, plus fields for description, vendor, unit cost, and anything else relevant.
Item Requests has a field for the requester to describe what they want, a linked record field called Matched Item that links to the Item Master, and lookup fields that pull description, vendor, and cost from the linked item once it is connected.
Lookup fields update automatically whenever the Matched Item link changes. No data is duplicated. The values live in the Item Master and are displayed in the request through the link.
Because the values come from lookup fields, they stay current on their own. If someone updates the vendor or unit cost in the Item Master later, every linked request automatically reflects the latest information. There's no need to go back and update dozens of request records individually.
Since employees only submit requests, they generally don't need edit access to the Item Master. Restricting who can edit it, whether through locked fields or table level permissions depending on your plan, helps keep the approved item catalog accurate while still letting requesters benefit from the information stored there.
Automating the link when a part number matches
Add an automation that runs when a new record is created in Item Requests. It works in three steps:
- Trigger when a new record is created in Item Requests.
- Find a matching record in Item Master where the Part Number field equals the value entered in the request.
- If a match exists, update the Matched Item linked field in the request with the returned record ID.
Once the linked record is populated, every lookup field updates automatically. No additional automation is needed for the description, vendor, price, or any other information stored in the Item Master.
If no match is found, the automation does nothing. The request stays unlinked and a reviewer can link it manually or create a new Item Master record for the new part.
This approach works best when the field you're matching against contains unique values, such as a part number or SKU. The Find Records step can return more than one record if your matching field isn't unique, and the automation has no way to decide which one is correct on its own. If you expect duplicate matches, you'll need additional logic, such as a review step, to determine which record should be linked.
It's also worth being clear about what the matching actually does. The automation performs an exact match, not a fuzzy one. If an employee types ABC100G instead of ABC-100G, nothing matches, and the request goes unlinked even though a human would recognize it as the same part. If employees frequently enter variations of the same part number, you'll either need to standardize how requests are entered or build a more advanced matching workflow.
Why not use a form with a linked record picker
An alternative is to add a linked record field directly to the request form so employees select from the Item Master list themselves. This works well when the list is short and employees know exactly what they need.
For a lab with hundreds of approved items and employees who may not know exact part numbers, the free-text approach with automation matching is more forgiving. Employees enter the part number or item they need. When the entered value exactly matches an existing record, the automation links it automatically. Otherwise, the request remains unlinked until it's reviewed.
The same structure works whether requests come in through an Airtable form, an Interface, or another automation. What matters is that the request record ends up with a value in the matching field for the automation to search against.
The bigger shift
The important shift is thinking in terms of relationships instead of copied values. Rather than filling the same information into multiple tables, store it once in your Item Master and let linked records and lookup fields display it wherever it's needed. This keeps your data consistent, reduces maintenance, and follows the way Airtable is designed to work.
For more on building lookup and rollup chains across linked tables, see how to populate a linked record with lookup data in Airtable and how to display related data from multiple tables in Airtable.