Grouping By Multiple Linked Records / Expanding View of Linked Records

[Katie_Cettie_St]

Hi, hopefully I can explain what I am looking for.

I have several tables - “In the News”; “Events” and “Projects”. Each of those tables has detailed information (date, description, etc.) on each individual event / project / news item. I also have a field in each of those tables that links to a “Topics” table, so I can tag specific topics (e.g. coworking, consulting, affordable housing, etc.) that each news article, event or project could relate to.

The problem that I am having is that I want to be able to see the detailed view of Events, sorted by topic. If I sort in the Events table by grouping topics, then events with multiple topics get put in their own separate category (ie if an Event is tagged “coworking” and “consulting” it will show up as its own category, rather than showing twice, once in the “coworking” category and once in the “consulting” category which is what I want).

If I go to the topics table and group by topic, I have to click into each linked record to read about events, I can’t do things like sort events by date or see a view of the detailed information.

Does anyone have a solution for this? I am stumped.

[ScottWorld · Accepted solution]

Welcome to the community, @Katie_Cettie_Steinbe! :slightly_smiling_face:

This is a common database dilemma which isn’t just specific to Airtable. As you’ve noticed, each record can only show up IN ONE PLACE in your system at any given time, even if it has multiple topics attached to it.

The solution to this is that you will need to create ANOTHER table which acts a “join table” between Events & Topics. This join table is the intersection of topics & events. (You could call this join table something descriptive like “Linked Topics & Events” or whatever makes sense for your organization.)

This join table will act as a middleman between Events & Topics by linking to BOTH Events & Topics.

So, instead of Events & Topics linking to each other (in fact, you can delete the link between Events & Topics), your Events table will link to this join table, and your Topics table will also link to this join table. Two different tables will be feeding into this one join table.

I find it easiest to set all of this up from your new join table.

This is how it works:

Each record in this join table represents ONE UNIQUE COMBINATION of event & topic. In other words, each record in this table will link to ONLY ONE TOPIC and ONLY ONE EVENT.

So, for example, if an event (“Event A”) has 8 different topics associated with it (“Topic 1”, “Topic 2”, “Topic 3”, etc.) you will create 8 new records in this table. Each of the 8 records will all be linked to the same “Event A”, but each of the 8 records will be linked to a completely different Topic.

So, this join table is where you will end up doing your grouping & sorting & filtering.

If you’re working in this join table and you need to see detailed information from one of the other tables, you can create lookup fields to pull in information from the other tables.

You can actually do your data entry from any of the 3 tables, but at first, it might be a good idea to add a few records directly into this new join table to visualize how this works.

Back in your Events & Topics tables, you will notice that Airtable has automatically created “reverse link” fields for you which point to this new join table. If you don’t see the reverse link fields, they’re hidden within the “Hidden Fields” button.

Let me know if you have more questions about this! And, if this helped solve your problem, please mark this comment as the solution to your question — which will help other people who have a similar question. :slightly_smiling_face:

[Katie_Cettie_St]

Thank you @ScottWorld for your response and help! That solution seems like it would be quite a bit of data entry, having to create a new record for every topic tag. Is there any way to automate that? This is especially true since I am actually linking several tables (In the News, Events, and Projects) to Topics.

For now, at least for my purposes it seems like the best solution is to just use filters in each detailed view to see what I want. It is a bit frustrating that Airtable doesn’t enable you to make the kind of view I’ve described.

[ScottWorld]

That solution seems like it would be quite a bit of data entry, having to create a new record for every topic tag

Actually, it’s not really more data entry than you’re currently doing, because you can still do the data entry from either the Topics table or the Events table. But you’ll just be doing the data entry in a slightly different way.

You’ll just need to setup the structure of your tables differently at the beginning. But once the structure is setup properly, you’re doing nearly identical amounts of data entry. And you don’t have to leave the Events table to do your data entry — you can stay there for all your data entry.

It is a bit frustrating that Airtable doesn’t enable you to make the kind of view I’ve described.

This is not an Airtable-specific issue. This is the same issue with all databases. One record can only live in one location at a time. You can’t “split a record” to be in multiple places at once. You can even see this in a world-renowned program like Quicken, which is just a database of your financial transactions. You can tag each transaction with multiple tags — but when you run a report, it groups the tags together as combined groups (just like what you see in Airtable).

Even Gmail, which lets you tag an email message with multiple tags, only lets you click on one tag at a time. (And if you manually search by multiple tags at once, it only shows each email once in the resulting list.)

You can do that in Airtable, too — you can “filter” on one specific tag at a time.

So that could actually be a good solution for you — take the Google approach and create multiple different views, each view with a different filter applied to it. Each filter would be just for ONE specific topic.

[Katie_Cettie_St]

Hm, I think that I am just not quite understanding your solution @ScottWorld then if you are saying you could still do the data entry from the Events table (ie in this example, there is still only one “Events” record, but 8 tags – how do those tags get converted to 8 records in the Join table if I am doing my data entry from events?) I am not very familiar with database design / best practices so apologies that I am still wrapping my head around the join / junction tables!

[ScottWorld]

You might be better off just going down the very simple route, and just create new views in your Events table — each one of them filtered by a single topic. Then you can leave everything setup just like how it is setup now, and you don’t need to worry about linking tables (and the limitations that go along with linking tables, as I explain below).

But if you’d like to really set this up with the ideal database design (and it’s your choice if you want to go down this road), you’ll want to setup this join/junction table. That’s actually why I suggested that you simply start by doing your data entry in your new join table, simply so you can visually see what’s happening behind-the-scenes, and then you can take a look afterwards to see how the Events tables & the Topics tables look after you’ve manually added some records into your new join table. (In your Events & Topics tables, you’ll need to “unhide” any new fields that Airtable has automatically added to those tables.)

You don’t need to keep doing data entry in your join table, but you can just start off that way to see how Airtable works in this fashion.

From the Events table, it will be almost identical to what you have now, except that it will be very different when you’re adding new topics to an event. Let’s say that you have one “Events” record with 8 topics associated with it. In your Events table, you will end up seeing one linked record field with 8 topics attached to it, but all of those 8 linked records will be living in your join table instead of your Topics table.

So you’ll actually be CREATING new records in the join table whenever you need to add a new topic to an event. But you can do all of this from the Events table in the new “linked record” field that links to your join table.

Any new linked records that you create will automatically have the “Event” linked to them (because you’re creating them from the Events table), but you’ll need to manually add one new record for each linked topic.

Airtable’s interface for this will be a pop-up window that appears, so it will be a very different interface than what you’re currently used to. And when this pop-up window comes up, you might be initially overwhelmed because it will show you all the pre-existing records in the Join Table. You’ll want to ignore all of these pre-existing records and just click on the “Add New Record” button at the bottom.

(Even better, you can limit your linked record field to ONLY showing you records from a particular view in your Join Table, so you can create a view that shows you no records at all — then you won’t be overwhelmed because you’ll just be looking at a blank window, so you’ll instinctively know to click on the “Add New Record” button at the bottom.)

Oh, and one very important thing that I forgot to mention previously: One of Airtable’s gigantic limitations is that Airtable will ONLY display your “Primary Field” in a linked record field after you link records, so in your Events table, you will only see the values from the join table’s Primary Field in your linked record field. In my personal opinion, this is one of Airtable’s biggest limitations & weaknesses. What this means is that in your Events table, you’ll ONLY see the Primary Field value from your Join Table in your linked record field, so it’s going to look a bit weird. Since you always want your Primary Fields to be unique values, in your join table you’ll probably want to use a formula for your primary field that is a concatenation of your topic & your event. So this will look pretty strange in your Events table’s linked record field, because you’re not just going to see a list of categories like you see now. This is why I think that this is a huge limitation of Airtable. However, you can add “lookup fields” to your Events table to display the values that you want to see from your join table in an array (i.e. separated by commas) — so that will be a nicer way of looking at your topics. They will be a simple list of topics that is separated by commas.

(And — another limitation of Airtable which isn’t a limitation in other fully-relational database systems: If you actually remove a topic from an event later, you’ll end up with some “orphan” records in your join table. So, from time to time, you’ll want to filter your join table for any orphaned records and delete them from the join table. You can create another view for this as well.)

If you feel like going down this road, it might be best to just dive into creating this join table without trying to fully visualize it too much ahead of time, because it’s somewhat complex to setup, and if you’ve never done it before, it probably won’t make much sense until you actually start building it & seeing it in action. You will be dealing with 2 or 3 Airtable limitations (as I mentioned above), so you’ll have to see if you’re okay working within these limitations that Airtable presents to you.

But the cool thing is that you’ll be able to do all of your grouping/sorting of topics & events in this join table, just like how you originally envisioned! :slightly_smiling_face:

Also, if your company has a budget for this and you’d like to hire a professional Airtable developer to help you set this up, please feel free to send me a private message as well! :slightly_smiling_face: