Re: Could I have an explanation of why we can't hide the primary field?
Hey ripwit
I am gonna try to bridge the gap between databases and airtable, and I am basically just hoping you know SQL. I’m certain you’ve realized the the combination of filters and grouping is analogous to using SELECT/WHERE and GROUP BY functions. So if you were to say like [select * where name = dave] then you’ll get all the daves in your table, and if you say [select * where firstname = dave group by lastname] then you’ll get all the daves, but they’ll be grouped into clusters by last name. Same as putting a filter and grouping on airtable. And that is enough to get someone confused about how this all works. In SQL and many other relational databases, all records have a record id. This is not the case with airtable (at least not in a way that is meaningful at a glance, because RECORD_ID() returns something that I would not call human-readable). If you notice, every time you sort a table view differently, it reorders the record numbers. So row 14 in one view is not necessarily the same record as row 14 in another view. This is important because it means your records do not have unique ids assigned to them that you can reference easily.
I don’t doubt that you noticed that already, but I wanted to point it out to preface the next point. The only way to uniquely identify a record with a single column is by using the primary field. It is also the most visible field, and it is utilized heavily in airtable’s interface. Take the attached screencap for instance. “Dave” is simply not a great identifier for reference, and the whole point of airtable is to show information at a glance. It’s a visual tool. I know you can get great results using it for data analysis, but the point is to be able to deliver that information quickly and in a meaningful way, so you set up the data analysis stuff but the goal should be to deliver information quickly, not just store data. If you want to store data and process it, you should probably be using a database. If you want sequential records for analysis, maybe use [popular spreadsheet program] but what airtable EXCELs at is delivering information visually.
If I were you, I would experiment with the use of empty link columns that are only used to create links which give you access to lookup and rollup fields, and start using formulas for your primary columns which simply create unique identifiers out of multiple columns by doing things like [column2 & " " & column3].
That’s just my thoughts though, let me know if I didn’t quite hit the mark.
Supplementary post here- Just pointing out that using formulas for primary columns in linked tables is absolutely a valid practice
I think I see the problem. In your example, with an Order record that contains links to Product and Customer, you are effectively using the Order record table as a junction table. This is entirely unnecessary and I believe it is complicating things beyond what you would consider an elegant design. In my mind, you have 3 tables.
The Order table’s primary column would be the Order Number, in that case I would have a Product table link that allows multiple records, and a Customer link that does not allow multiple records.
The Product table would have a primary column that is the name of the product, along with an Order link that would allow multiple records (recording every order that involved that product) and it would not need a Customer link unless you desired it for some kind of prediction or analysis to see what different customers or customer types tend to buy. But even if you did that, I would hide that column in the Product table because it would be massive, and instead I would use formula columns to analyze the number of times the product has been ordered for each customer type, or something like that.
The Customer table would have an Order link that would allow multiple records (recording every order that customer has ever made) and maybe a Product link, for the reasons stated in the above paragraph, and utilized similarly.
The important thing here is that the Order table is NOT simply a junction table. To be honest, creating junction tables is kind of difficult in Airtable. It requires a lot of convoluted stuff I don’t like, and you don’t need them because the individual record view feature can make that happen on the fly. If you want to see [select orders from customer where product = chairs] then you can simply drill into the customer record or something. And if you’re drilling into a record and you want to see a related record, you can drill into that from the screen you’re already on. See the attached pictures.
Maybe you should start with what you want to accomplish with your base, rather than what data you have available. In my experience you get more out of airtable when you are trying to answer a question that you’ve already got ready than if you just pour data into it trying to organize it. I’ll post a followup in a second to show you what I mean.
