Entering data that's connected by a join table -? (grouping multiple linked records / tagging)

Ask the community
#1

How can I enter data for records that are linked by a join table?

Details:

I understand Airtable is kind of lousy at tagging.
I’m trying to do something very basic but getting frustrated at how exactly to make it work. I am setting up a series of events, and I want to tag each event with multiple tags, THEN group by those tags.
I understand from this and other posts that it’s not directly possible.

I’m trying to follow ScottWorld’s instructions here to set up a join table that links records.
So now I have 3 tables, per his instructions:

  1. Events
  2. Tags
  3. Join table that connects multiple events with multiple tags (one entry at a time)

For example, let’s say I want to set up an event: EASTERFEST
I want to tag it: FREE, MEMBERS-ONLY, ENGLISH, ZOOM

It seems that this will be 4 records in the join table:

  1. EASTERFEST : FREE
  2. EASTERFEST: MEMBERS-ONLY
  3. EASTERFEST: ENGLISH
  4. EASTERFEST: ZOOM

In the original thread, ScottWorld suggests that a) data entry won’t take longer this way, and b) you can create entries from any of the 3 tables. He writes, “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 I can’t figure out how! Right now, I can only enter the records one at a time, i.e. 4 separate records for this example, and only in the join table. This is a TON of data entry, but I can’t figure out how to incorporate these tags back into the events table. When I try to create a linked lookup field, it just says “Lookup fields should be configured in the field menu dropdown.” And I have no clue what that means.

Suggestions???

I’m new to Airtable - well, not quite. I tried to like it a couple of years ago but didn’t love it. Now I’m trying again, but it looks like this might be another dead end.

Any help is appreciated!
Tzivia

#2

@ScottWorld Any light you could personally shed on this would be particularly appreciated :slight_smile:

#3

Update: Also, it seems that when I try to create a grid view and group by tags, it STILL repeats the basic behaviour I was trying to avoid, i.e. it does not break down all the FREE events, all the ENGLISH events, etc.

Instead, it’s grouping all events tagged as ENGLISH, FREE separately from those tagged ENGLISH, ZOOM etc.

I want a single grid view where I see all ENGLISH events, all FREE events, all PAID events, all ZOOM events, etc. I understand that this will show the same event in multiple categories. How can I make this possible? Is there really a way?

Thanks!

Tzivia

#4

Welcome to the community, @Tzivia_MacLeod!

I would recommend reviewing Airtable’s guide on how to set up many-to-many relationships:

If that doesn’t solve your issue, please post some screenshots — or even better, a video — of how your system is setup & what you’re trying to accomplish.

#5

Thanks for your quick reply, @ScottWorld !
I’ve set up a sample base that parallels what I’m trying to accomplish

In this sample DB, I have a list of book titles and a list of genres.
Each genre can have multiple books.
Each book can have multiple genres.

I would like to list books by genre, i.e.

  1. Group books by genre (even if this means repeating some books under multiple genres)
  2. Sort books alphabetically within each genre

As you can see, I’ve tried doing this in a couple of different places in this DB, but with no luck.
I have a strong basic understanding of DB and data structure theory, including some pretty high-level querying and coding in older versions of Access.
But I can’t seem to make this very basic, supposely easy interface do what I’m trying to accomplish.

Couldn’t figure out how to share video here, so the MP4 is shared in my Dropbox.

Thanks!!

Tzivia

#6

Here’s the problem:

In your Join table, each record can only have ONE GENRE and ONE BOOK.

The Join table is the intersection between genres & books, meaning that each record can only have one genre + one book.

So, in your example, “Esperanza Rising” would need to be 3 different records in the Join Table, “Catcher in The Rye” would need to be 2 different records in the Join Table, and “Pretty Little Liars” would need to be 3 different records in the Join Table.

Each one of these records would have one (and only one) unique genre assigned to them.

Then, you would do all of your reporting/sorting/filtering/grouping from the Join Table instead of the other tables.

#7

May I ask, for my own edification, what the advantage is of having a discrete join table to establish the relationship, verses just directly linking Books to Genres, and making the grouping on the Books table?

#8

Oh, duh, you’re absolutely right! :man_facepalming: I wasn’t even thinking this all the way through! I got totally caught up in the original question that started this entire thread, which was how to properly setup a many-to-many relationship — without even analyzing whether this was necessary or not!

My bad. It’s not even needed for this scenario.

#9

Thanks to both of you @ScottWorld @Jeremy_Oglesby - clarity is always useful!!! :slight_smile:

Given this feedback, I tried setting it up this way, this time with a base in which we’re offering a variety of programs (a little closer to the application I’m trying to create).

  1. Every program being offered can be tagged with zero, one, or multiple tags
  2. Every tag can have zero, one, or multiple programs associated with it

I set this up in the same sample link I shared yesterday if you’d like to take a look. The tables are called “Programs” and “Tags.” (I moved them to the front for simplicity)

This certainly “works,” in the sense that I can input records and tag them with as many tags as I’d like. But when I go to output all the programs by tag/topic, the output is not at all useful:

image
image745×134 3.78 KB

Why isn’t this useful?
All programs with a given tag are listed on a single line and are not alphabetized or broken down separately. And the “count” at the top of each tag does not tell me how many programs are tagged with that tag.

What I would LOVE is the ability to output something that looks a little like this…
image
Here is the same data (manually entered into Google Sheets!), only with each tag broken down, showing a count of how many programs there are for each tag. Also, the programs are listed alphabetically, which seems like a pretty basic requirement but somehow impossible (for me!) in Airtable.

Any way to accomplish something like the above using Airtable?

Thanks!

Tzivia

#10

Airtable is great at collecting data, but when it comes to presenting that data in useful ways, its built-in views and features (grouping, filtering and sorting) don’t provide the type of control that most are looking for. To create meaningful output will require a bit more work.

Page Designer might be usable for some things, but it hasn’t been updated in quite a while, and is still quirky in many ways.

I’ll sometimes lean toward custom scripts, partly because I’m comfortable writing code to get the output that I want. That even goes into custom apps at times. I built a quick app a while back to show me data related to my planning system when another method kept failing.

There are also third-party tools that give you other ways of presenting Airtable’s data. I’m not very familiar with those (largely because of the costs involved)

I’m at a point now where I know what kind of output I can realistically expect from Airtable’s basic features, and what’s going to take more work. In your situation, I don’t see the output that you seek happening without either a custom script/app, or getting a third-party tool involved.