I am creating a table which lists our shipments of containers.
I need a column of data to pull data from another sheet, if a 3rd column’s contents match the data on the other sheet.
For example. On sheet 1, in Column 1, I enter a serial number. In column 2 of sheet on is a Lot Number.
Now on sheet 2, I have a table as well as a form view. On the Form view, a person on our ops team enters a serial number and creates a new record. Once that record is created, I want column 2 of sheet to automatically: a) check to see if the serial number entered in the form (which created said record) is listed in column 1 of sheet 1; and b) if it is listed there, to populate column 2 of sheet 2 with the relevant data in column 2 of sheet one.
it’s basically a lookup function but is reliant on several different data points.
I have tried the link function within Airtable but it doesn’t seem to be able to handle this. If it can, it’s not at all straightforward.
Not possible in Airtable. This is a feature of more robust relational database systems, and Airtable is more of a “lightweight” relational database system.
Airtable doesn’t do automatic linking nor automatic matching of records between tables. All linking & matching must be explicitly defined by the user every single time — meaning that users must manually link records themselves.
Furthermore, when you DO manually link records, you can’t choose which fields you want to link to which fields. Everything in your current table always links back to the primary field in the other table. Always.
In my personal opinion, these are 2 of the most needed features in Airtable to really make it become super powerful.
If you feel the same way, please add this as a Product Suggestion in the Product Suggestion Forum.
There are several different ways to do this.
You still can’t do this natively with the Airtable forms on their own, but you can do this AFTER a form is submitted by using an Airtable automation. On the form, you can have the user type in the information into a normal text/number field, and then you can create an automation to “copy & paste” the data from the text/number field into a linked record field.
Alternatively, you can do this 100% natively with the MiniExtensions form, which is an excellent & advanced form that is specifically designed for Airtable.
You can also use other form software (or even Airtable’s native form itself), and then automate the process with Zapier or Integromat. Integromat is much preferred over Zapier, because it has 100% full Airtable support (whereas Zapier does not), it is a much more feature-rich platform, it has more capabilities than Zapier, it is less expensive than Zapier, and is has no limitations where Zapier has many limitations (for example, Zapier has limits on scheduling intervals, limits on number of conditionals, etc.).
Note that I am a professional Airtable consultant and a Registered Integromat Partner, and the Integromat link contains my personal referral code. If you’d like to hire an Airtable consultant to help you setup any of these things, feel free to contact me through my website at scottworld.com.