Data Matching and Referencing between sheets

[Erik_Anderson]

Hi,
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.

PLEASE HELP

Regards
Erik

[kuovonne]

Airtable currently will not automatically create links for you when a new form is created.

Currently, the only time Airtable will automatically create links is when a field type is changed to a linked record from a different type.

[ScottWorld]

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.

[Erik_Anderson]

I 100% agree.

At first I loved the product and it’s super easy to set up. But it’s missing some really important features that would help data manipulation and use.

This and the ability to link to sheets in other bases. This would be hugely useful.

[ScottWorld]

Erik_Anderson:

This and the ability to link to sheets in other bases. This would be hugely useful.

OMG, I totally forgot about that one. That one is HUGE HUGE HUGE.

Can you please type up these requests in the Product Suggestions forum? The more unique voices they hear over there, the more likely they are to take these suggestions seriously! :slightly_smiling_face:

[Kaleb_Davenport]

Has the product feature already been suggested or should I add it now?

[amber_h]

Is there no way to incorporate Zapier or Ifttt to do the manual copy and paste needed to link records? Thank you!

[ScottWorld]

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.).

You could also combine JotForm and On2Air Forms to accomplish this as well.

And, of course, you could also write your own JavaScript in Airtable to do this as well, although that would probably be overkill since the other solutions above don’t require any coding at all.

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.

[Aleksander_Pala]

I came from Google.

So, if you are searching how to do automatic linking - like me - just use the automation option when a field is updated.