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