It would be amazing if Airtable had the ability for records to AUTOMATICALLY link to records in other tables, once Airtable knows what “match criteria” we’re looking for. Right now, Airtable requires us to manually link every single record by hand.
There are ways to shortcut the linking process, though it depends on your use case. Could you describe what you’re trying to achieve in more detail? Perhaps there’s a way to achieve what you want without the work of fully manual linking.
I think the big problem is that Airtable obscures the concept of a “primary ID key” and a “foreign ID key” when linking tables, which is the way that all other database systems link tables.
Airtable is linking “primary ID keys” and “foreign ID keys” invisibly in the background, but we can’t control them.
So this ends up requiring humans to manually link everything 100% of the time.
The most obvious use-case scenarios that I can think of would be importing data from another database system into Airtable. This would be impossible if the information came into Airtable from different tables. We might be wanting to import a very simple database system to keep track of families that looks something like this:
Table: Parents
Fields: Primary ID Key for Contact, Parent Name, Parent Phone #, Parent Email
Table: Children
Fields: Primary ID Key for Children, Foreign ID for Parents, Child Name, Child Phone #, Child Email
Unfortunately, there is no way for us to simply import these 2 tables into Airtable, and then have those 2 tables automatically linked. We would have to manually scroll through hundreds of parents and then re-link them to all of their children.
Meanwhile, the OPPOSITE scenario is a problem as well! We can’t EXPORT any meaningful data like this out of Airtable, because we would end up with 2 separate tables (“Parents” and “Children”) but no idea how to link them in any other system.
So, if you’ve started your system in Airtable, you’re can’t migrate your system away from Airtable.
Even worse, if you’ve started your system in another program, you can’t bring that system into Airtable.
Now those are just the 2 most obvious scenarios that I could think of which show how primary keys & foreign keys can be used efficiently.
But there are actually unlimited number of other case scenarios as well.
For example, maybe we want one person to be doing data entry into a “stock price history” table, and we want those prices to automatically show up in the linked “stocks” table. It might look something like this:
Table: Stocks
Fields: Primary ID Key for Stock, Stock Name, Stock Symbol
Table: Stock Prices
Fields: Primary ID Key for Price, Foreign ID Key for Stock, Date, Price
In any other database system, users could simply do data entry all day long in the “stock prices” table, and as long as the foreign ID key for the related stock is accurate, then all of those prices will automatically show up in the stocks table — because all of those prices will be automatically linked to the stocks table.
Even better, in any other database system, users could simply IMPORT a gigantic CSV file of “stock prices” into the “stock prices” table, and as long as the foreign keys were correct on each row, then the “stocks” table will automatically get linked to all of the stock prices.
But none of this is possible in Airtable. In Airtable, every single record needs to be manually linked by a human to another record. :frowning:
I totally get that this is how Airtable is keeping databases “simple for the masses”, but I think that it’s going to be a real stumbling block for building more complicated systems for businesses. Or for getting people to migrate their old systems into Airtable. (Of course, that’s probably not Airtable’s primary target audience anyways.)
So my suggestion is this: Please open up the whole entire concept of “Primary ID Key” and “Foreign ID Key” to us, so we can start creating all sorts of highly-advanced database systems in Airtable. :slightly_smiling_face:
I love Airtable, and I want everyone to be using it! :slightly_smiling_face:
So, in summary, my CURRENT stumbling block this week is that I’ve been trying to migrate clients from their old database systems into Airtable, but it’s simply not possible.
For example, I’m currently looking at 15 CSV files (15 exported tables) from a client, and each one of these CSV files has a primary key column & a foreign key column — but there is no way for me to bring this information into Airtable and still keep all the information linked to each other.
I would love to switch everyone to Airtable, but it’s simply not possible to migrate everyone at the moment.
Some of the automatic linking that you say is impossible is actually possible. I’m away from home and wont have time to give an example until some time tomorrow, though, but I’m hoping that you’ll be pleasantly surprised when you see what can be done with the right setup.
I noticed that you posted the same question to Reddit, and a user there provided the answer that I was going to share:
Here’s a solution I shared from about a week ago that includes this technique:
Specifically, the part where I convert the {Emails} field from the email type to a link type is the technique I’m talking about, and which user “Forsaken_Goat” references in the Reddit thread. By using this technique, you should be able to import all of your data, and let Airtable do the linking when you convert the relevant field to a link to another table, as long as the primary field in that other table contains the same data as the field you’re converting from text to links.
Thanks so much! I greatly appreciate it! :slightly_smiling_face:
That Reddit post helped me a lot as well.
This should definitely work with the migration of most of my client’s systems into Airtable, and I will give it a go! :slightly_smiling_face:
Some of the more advanced database systems that I’ve worked on won’t be able to be migrated into Airtable (those are ones where there are multiple keys per table — each key is a different formula based off the primary key), but since the vast majority of the database systems that I’ve worked on only contain a single key per table, your tips should be able to help me migrate at least 80% of the database systems that I’ve worked on into Airtable.
We’re going to be posting data to various (3-4) tables every day. All of these tables have one field in common, the Unique ID. My understanding of the answers above is that this would solve the problem if you have static datasets. But in our case, we’re posting hundreds of rows to each table every day, and the ideal situation would be for these rows to automatically link to each other due to the matching Unique ID in each table.
Any suggestions on how we might proceed? Thank you for your time!
Edit: Reading a bit more on the community forum, which is FANTASTIC, by the way, hits on two “hacks” which would be to either 1) copy the data from the Unique ID field, and paste it into a Link field, or 2) use Zapier to do something similar. Both of these sound doable, would still be interested in if there is a “native” way to solve for this!
Sorry for the delay in getting to this, but my new job doesn’t allow me to spend nearly as much time in here as I used to. I’m glad you were able to get help from @JonathanBowen in the other thread!
