A better way to link Tables

[Daniel_Simon]

Request: a better UI for linking tables, without the need for Linked Fields. Also, allowing users to link records on something other than the Primary Key.

Scenario: I am in the midst of building out a Base that will replace a local Sports club’s player management database. The current method being used is an Excel spreadsheet that has a number of Vlookups and other formulas to determine computed player abilities (based on tryouts) so that teams can be equally balanced and assigned. Airtable looks like the perfect fit us, but I struggle with the requirement to create a Linked Field for every single lookup. The end result is I have a number of extra ‘dummy’ fields that don’t hold data and just make the interface messy and hard to follow.

Additionally, the way that Linked Fields work, they automatically just join on the Primary Key, with no ability to pick another field to join on or even format the field (ex no ability to join on a concatenation of values). This is making some of my desired table joins impossible or extremely convoluted.

Coming from a background in MS SQL Server, this seems like an overly complex way to create table joins and yet annoyingly limiting at the same time. I feel like if we had a separate UI for specifying table joins, we could a) really simplify the process and b) offer more possibilities on the types of fields that can be joined together.

[Bill_French]

Daniel_Simon:

some of my desired table joins impossible or extremely convoluted

100% agree.

The current linking constraints tend to cause users to create workarounds on top of workarounds, leading to unsustainable complexities. In many companies and organizations advocates of Airtable also have a duty to provide solutions that will eventually be handed off to other people as custodians. Some of the approaches I’ve seen here and many I’ve built myself will not be sustained over the long run unless they are straight-forward.

[Jason_Szotak]

I think any of us that has even minimal SQL experience yearns for a good old left/right inner/outer join option.

I think the thing I’ve had to accept is airtable is not a true database, and it’s not intended to be, so if I really want a true online database I need to find a different product. Or I can work within what it is and still do some pretty good stuff.

[Bill_French]

Jason_Szotak:

airtable is not a true database, and it’s not intended to be

Indeed, there are certainly limitations that are [seemingly] unpleasant and there’s a fair bit of truth in your comment. All products have limitations and strengths. Overcoming limitations is a natural process when probing for better solutions. But what may not be wise is creating hyper-complex data designs to avoid using the API or an entirely different tool. Cascading workarounds is a sure sign you’ve crossed the line.

If you find yourself pushing a shopping cart through the aisles of a 7-Eleven, you probably have crossed a line. :winking_face:

[Mike_Alexander1]

I wholeheartedly agree that better support for joining is needed, but from the brief description of your use case it doesn’t sound strictly necessary.

One of the key things to keep in mind is that in AirTable, unlike databases, calculations on data should be put in the same table as the data. This was very counter-intuitive for me, but became easier the more I did it. If all the data you need for a calculation is in a single table, you should create a Formula field in that table to perform the calculation. You can then include the calculated formula results in another table via a Lookup. Alternative you can create a Lookup in the new table for every field used in the calculation, then create a Formula that operates on these Lookups.
If you have data from across a number of tables you need in your calculations, you’ll have to bring it all together in one table. That means Lookups to get the data from one table into another, then Formulas to operate on that data.
If you have specific use cases you’re trying to work thru, the Community can often help figure out how to organize your data, when you need to do Lookups, and/or when you need to do Rollups and operate on the results as an array if you post more detailed specifics about your use case.

I’m not sure what databases you’re familiar with if you’re concerned with Linked fields being limited to Primary Keys, all relational databases have to use Primary Keys as the link between tables because they’re the only thing guaranteed to be unique for a record in a table. Based on the age of your question, this might actually be a complaint about how you can pick the records in a Linked field, which has been updated to display most of the information from the records rather than only the Primary Key of the records during selection of the records to Link. If you actually want something like the player’s last name to show up instead of the automatically assigned number you’re using as the primary key in another table though, you’ll need to create a Lookup to get it.

In terms of the dummy fields, I find that hiding them or creating a new “main” View is really helpful. I only want to know about the intermediate Lookups, Rollups, and Formulas when I’m trying to debug a problem. Usually I only want to see the results, and be able to add new information. For that I can either hide these extraneous fields, or create a new View and hide them there. In practice I usually end up with almost half my fields hidden in the “main” view of my top level tables.