Automatic linking of tables based on any field

[ScottWorld]

Two of the major limitations of Airtable when creating a link from one table (“Table A”) to another table (“Table B”) is that:

1. We can ONLY link to the primary field in the other table (“Table B”). To truly unleash massive amounts of power & flexibility & customizability in any database system, we need the ability to MANUALLY CHOOSE which field in “Table A” should be linked to which field in “Table B”.

2. Linking is always done MANUALLY instead of AUTOMATICALLY. Unlike other database systems, Airtable doesn’t automatically scan for matching values in linked fields — a human must manually intervene by manually linking records.

Obviously, these changes would graduate Airtable from the realm of “beginner-level database program” into a more “advanced-level database program”, but it would enable us to create all sorts of advanced database solutions that we can’t create now. It would also remove lots of migration headaches when importing/migrating data into Airtable from other advanced database apps.

[kuovonne]

Actually, when you create a link from one table to another, you create a link the entire record, not just the primary field, even though only the primary field is displayed in the linked record field. You can access all the other fields in the linked record via lookup and rollup fields.

[ScottWorld]

Yes, I know, but that only creates one unique relationship between 2 tables. With multiple key fields, you can create multiple different types of relationships between 2 tables for more advanced database solutions.

[kuovonne]

Scott, can you give an example?

My understanding is that you can create multiple relationships between tables by creating multiple linked record fields.

Also, I believe that Airtable does not use primary keys and foreign keys the way many other relational databases do.

[ScottWorld]

kuovonne:

My understanding is that you can create multiple relationships between tables by creating multiple linked record fields.

Right, sorry, I didn’t really explain this very well in my initial post above. Maybe I should go back and completely re-edit my initial post above.

You are ABSOLUTELY CORRECT that you can create multiple relationships between tables by creating multiple linked record fields, but every record always needs to be EXPLICITLY linked to another one. In other words, you have to MANUALLY click on that little “+” sign to link a record between tables.

Airtable never automatically links records between one table & another table, simply based on matching criteria in a field. Right now, every record needs to be EXPLICITLY linked to a record in another table by us clicking in the field and manually linking them.

However, there is one exception to this rule: Whenever you go through that one-time process of changing a normal field into a “linked field”, then Airtable AUTOMATICALLY links records during that one-time process only. (But again, Airtable will only link based on the value in the primary field of the other table that you’re trying to link to.)

Now that I think about this, my guess is that Airtable will probably never go down this path, because it moves them into the realm of “real database systems”, which would probably alienate much of their customer base. I know that Airtable is positioned as the easy/friendly database system that requires NO CODING at all, so this probably isn’t a direction that they want to go in.

Also, I believe that Airtable does not use primary keys and foreign keys the way many other relational databases do.

This is the crux of the problem here, and since this is the way that Airtable was built from the ground up, my guess is that this will likely never change.

Scott, can you give an example?

I can’t really think of a great example right now. Here’s one example off the top of my head, but it’s probably not the best example.

Let’s say you’re looking a customers table, and you want to see all their linked invoices. That’s pretty easy right now in Airtable. But, let’s say that you just want to see their linked invoices that fall within a specific date range, or you just want to quickly toggle back & forth between seeing a list of their unpaid invoices & their paid invoices — all from within the customer table.

In other relational databases, you would specify this sort of thing with a relationship comprised of multiple fields linking to one another, and the database system would AUTOMATICALLY change the lookups & the rollups based on the changing criteria that the user chooses on-the-fly.

So, from the customers table, if you want to see a date range of invoices for that customer, the relationship from customers to invoices would look like this:
(1) Customer ID in Customers Table == Customer ID in Invoices Table
(2) Start Date specified in Customers Table >= Invoice Date in Invoices Table
(3) End Date specified in Customers Table <= Invoice Date in Invoices Table

Then, if you change the start date & the end date in the customers table, your lookup field would automatically reflect a different set of linked invoices.

But now that I’m typing all of this up, I’m realizing that this is probably way beyond the scope of what they are trying to do with Airtable. This is what truly advanced database software like FileMaker Pro is for — which is a complete database programming language (and FileMaker Pro is the other language that I work in).

[kuovonne]

Thank you for the explanation. Yes, Airtable does not create automatic links the way that other database systems do with foreign keys.

ScottWorld:

I know that Airtable is positioned as the easy/friendly database system that requires NO CODING at all, so this probably isn’t a direction that they want to go in.

With the release of scripting block, it looks like Airtable is no longer positioned as strictly a no code platform.

[ScottWorld]

kuovonne:

With the release of scripting block, it looks like Airtable is no longer positioned as strictly a no code platform.

Good point! Hopefully they’ll be moving more in this coding direction for other parts of their product, such as the links to other tables.

[ScottWorld]

I just came up with a good example of being able to choose our own fields for relationships. This post inspired me: Formula to reveal duplicate rows

If we could AUTOMATICALLY LINK a table to itself based on a certain field (even a primary key field), then we could AUTOMATICALLY find duplicate records within a table… without manually running a DeDupe block. I know that we can MANUALLY LINK records to other records within the same table, but again… this is done manually instead of automatically within Airtable.

[Bill_French]

kuovonne:

With the release of scripting block, it looks like Airtable is no longer positioned as strictly a no code platform.

Quote of the week! :winking_face: I chuckle every time I think about the vast coding pathways we use to transform this code-free platform into powerful and more complete business solutions.

ScottWorld:

If we could AUTOMATICALLY LINK a table to itself based on a certain field (even a primary key field), then we could AUTOMATICALLY find duplicate records within a table…

Good example - worthy of a +1 for this suggestion.

But we have to accept that automatic relationship-building features could trigger some burdens not the least of which are back-end server processes that might require vastly more computational resources. That, in turn, may lead to price hikes and perhaps additional revenue tiers.

Linking vs Relevance

The objective of linking is to create relevance. In some cases, this needs to be very rigid. In other cases, not so much.

Part of me (roughly 51%) believes that this is the domain of properly designed search features. There are so many relevance-related factors in database content that (a) the true linking model can never be completely predicted or (b) instantiated through discrete connective tissue. Instead, you need a more intelligent way of crafting a model that identifies records that are “nearby”.

The Lucene index algorithm, which is the foundation of ElasticSearch, is capable of creating subtle but very effective connections between records and all without overloading the database server itself nor requiring any prescribed schema design.

[ScottWorld]

Here’s a great post which just came up tonight, illustrating the importance of “automatic linking” between tables:

Data Matching and Referencing between sheets Formulas

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 au…

[Erik_Anderson]

We would be very very happy if this feature were introduced into Airtable.

I can’t see why it would alienate anyone. As long as the basic ease-of-use of the platform isn’t affected then the addition of more powerful features would only increase the range of use cases for all customers - existing and potential.

There may be workarounds to some of these issues, but there shouldn’t have to be. Software should be complete.

Furthermore there is simply nothing intuitive about the link/rollup/lookup features of Airtable.

When I first set up airtable I was able to make the table I needed, create a form, and get data flowing into that table easily, quickly, and in a well organised fashion.

But when I wanted to DO SOMETHING with that data. Ie use it on another sheet and reconfigure it to give me different information, or rather organise that information in a different way, the functional capability of Airtable came to a very abrupt end.

• Automatic linking to tables based on any field, not just records.

• linking a range of fields across multiple records that meet certain parameters to a single field in another sheet

• Linking/formulas that work across different bases, not just within the same base or same sheet

Airtable - please work on this with a sense of urgency. My feeling is that you’re missing out on a huge pool of potential customers, as well as underserving / causing frustration and headaches for a decent percentage of your existing ones.

I’ve been using Airtable less than a week. These are the limitations I’ve come across in trying to make our data work better.

If anyone out there can recommend an alternative that doesn’t cost millions of dollars and require an MIT degree to set up (SQL, Oracle etc) please please let me know.