Skip to main content

I’m trying to convert an Access database we use to track event and participant data to Airtable so that we can get away from Access and all of its downsides. What we have has always seemed pretty simple and straightforward, but I’m really struggling to create a similar situation in Airtable that isn’t so time consuming. What I have is two tables connected by a join table: Events is where I create records for each new event we host, with a unique number as the identifier, as it’s possible to have multiple events on the same day. Participants is where I create records for each new attendee who participates, and the unique identifier is their username. We have new events about every week, and new attendees is unpredictable. Participants may attend one event or many events, and the goal of the database is to track who comes to which events, to be able to pull data about the types of people who come to our events, and to offer transcripts of the events attended by attendees who may ask for it to help their promotion dossiers. Then I have a join table that connects the two, creating a unique record for every persons attendance at every event. This is the only way I can see how to be able to pull the data I need to for year end reports and individual participation record requests. 

My biggest issue is that entering new data is so bulky and slow. In Access, I was able to pull the join table in as a subtable (see image) into the Events table so that when I entered a new Event, I could quickly add the usernames of the attendees (anywhere from 5 to 100), I could even copy/paste the usernames from the sign-in sheet, and they would automatically create all the new records in the Join Table. I have not figured out a smooth way to do this in Airtable. I seem to have to create the records IN the Join Table, and each one requires me to do a LOT more clicking. If I want to copy/paste, first I have to click the plus sign as many times as I have attendees to record. And I can’t see a way to just add attendees to the Event inside the Event table and have them create the unique records I need in the Join Table. 

This is the join table (Participants_Events) as a subtable in the main Events table. As I type the username of the attendees, the names populate if they’re already in my Participants table. 

How do I solve this? I’m brand new to Airtable, but I’ve google around a bunch trying to find others with this issue and haven’t found any existing answers that fit my scenario. This seems like a really simple issue, I just can’t figure it out. Thanks!

If you are trying to paste multiple rows into the grid view, Airtable will ask you if you want to expand the table to automatically add all the new rows for you. So you wouldn’t need to add all those new rows ahead of time.

To visualize your join table as a “subtable”, you would need to do that at the interface level.

You would start by looking at a “main record”, and then you would add the “linked record” field onto that main record’s detail page.

Then, here’s the trick: You would choose to display your linked record field as a “view” instead of a “field”. That will show you all of the linked records in a list or a grid format.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hmm, what’s the starting point for your data? 

If you have it as a list of comma separated values like ‘Jerry, George, Elaine’, then you could:

  1. Create a linked field between Events and Participants
  2. In Events’, paste the comma separated values into the linked field
  3. Use an automation to create the records in the Join table for you, and I’ve created an example base here for you to check out

If you’re copying it from a spreadsheet where each name is an individual row, then you could try creating them directly in the Attendance table itself instead by pasting it in like so, and then setting the Event manually:

 

Terms & ConditionsAccessibility statement