You're doing a Many-to-many relation, and in database modeling this forces you to have an auxiliary table (with those "duplicate" rows -ids of the companies, really-). Airtable allows you to link several records to other, but then you can't do some things (as you have experienced). Here you have, now the aux. table is which does the magic. I have created 2 different views, explore the base:
This introduces some complications that really should be unnecessary. One of the main advantages of Airtable is that it allows the use of features normally only available in advanced relational databases, but without having to actually build up the back-end yourself. As a product suggestion, I think that this is still a necessary feature despite the existence of a workaround like this. I’ve seen many people request this and I am having similar trouble. There really should just be a setting that you can toggle when you choose a grouping: you should be able to select either the current behavior or that described by the OP here.
Really, this needs to be a feature. The same needs to apply for sorting within grid views, as well as kanban views - the ability to group records by single groups, or by multiple.
I understand this is a fundamental feature of relational databases - that each record only exists in one place - but it really doesn’t break that if it simply displays it multiple times on a view.
It’s absolutely insane to me that Airtable continues to insist on being so pedantic about this, throwing up their hands and saying, “Welp, no can do, you see, it would violate the core relational database paradigm blah blah blah. You’ll just have to go through 68 convoluted steps to achieve a workaround solution for the deceptively simple thing you were asking for.”
There have been SO many requests for this feature. It’s actually hard to aggregate them all because people struggle to describe what they’re looking to do, but a google search shows this same essential question being asked dozens of different ways for years.
At the end of the day, all of the responses from Airtable reps on this forum center the product, when they should center the user needs… Why are users using the Groups feature at all, and what are they trying (but failing) to do with it? Who is being drawn to Airtable and why? If seeming ease of use is a big selling point, you need to recognize the fact that to a large percentage of users, the ones you have aggressively marketed to, the relational database aspect is IRRELEVANT. We want to see a display layer. It’s on Airtable to make the constraints and challenge of doing so invisible.
Sorry, but NO database software will split up 1 record to show up in 2 separate places in the same table. That would violate all database record integrity.
It can be easily worked around with a join table (also known as a junction table). This is how ALL database software works, not just Airtable.
Airtable blurs the line between tables and views. This may not be a feature of tables in relational databases, but neither are sorting, filtering, or even grouping for that matter. Those are all features of queries, or views based on those queries. And there are plenty of use cases for a query/view that displays the same record in multiple places.
No one is asking for Airtable to stop being based on a relational database; we’re asking for a pretty basic query type to be implemented in a way that makes sense within the user experience of Airtable. Editing in place also “violates record integrity” and isn’t a standard feature of relational databases but it’s central to why so many people like Airtable.
I don’t think you’re fully understanding how database systems work. There are never any queries in any database system that would result in a single record showing up in multiple places in the results of those queries, because a single record can only live in one place at any given time.
For example, take a look at the Contacts app on your iPhone. You could add JOHN SMITH to many different groups, but if you do a search for JOHN SMITH, he will only show up once. There is never any way to see that person more than once.
Same thing with the Photos app on your iPhone. A photo can be in multiple different albums, but if you do a search, that photo only shows up once. There is never any way to see that photo more than once.
Even with Quicken or QuickBooks, you can assign multiple tags to a transaction. But if you print a report sorted by tags, it does the exact same thing that Airtable does: it groups the tags together as complete groups of entities, not individual entities.
Even with the world’s largest databases, Google and Amazon: each record only shows up once in the results.
So, you’re trying to change how databases fundamentally work. You’re trying to change what makes a database a database.
And yet, there is already a solution: create a join table.
