Automating the creation a record when field containing multiple linked records is updated

Hi! So I’m pretty new to Airtable but it seems awesome and I’ve achieved a lot, mostly through help found in this forum. This is the first time I’ve actually had to ask something because I’m really stuck. And I’m not sure what I’m trying to do is even possible, but be good to know either way!

So my setup is all based around events, and I have the following key tables:

  • Speakers
  • Sessions

A session has multiple speakers, and speakers can be associated to multiple sessions. All pretty simple so far.

But here’s where the complication comes in. For big virtual events, commonly each speaker will be in their own Zoom room, which is then combined into a single stream (I won’t bore you too much with the tech). So normally we’ll have x10 Zoom URLs to be used for the whole event, with speakers assigned to a room for a session.

e.g. 10-11am - Speaker XYZ - Zoom Room 3

It’s worth considering that a speaker may be in one room for one session, and a different room for another (just to make things difficult!).

So I started by creating a table of the Zoom rooms and their associated URLs, all good there.

Then I need one final table to join it all up together (let’s call it the Speaker Room table), with the following key fields:

  • Session
  • Speaker
  • Room

I can do this all manually, no problems. But what I’d like to do is a little automation to ensure I’ve not missed anything. So in an ideal world I’d like to automate the following:

  1. Speaker is added to an session via linked record (remember that multiple speakers can be added to a session)
  2. System automatically creates a record in the Speaker Room table, with linked records for speaker & session pre-populated
  3. I’ll then manually add the Zoom room (linked record), but at least I have a record for each speaker/session to remind me that I need the room details

So using automations, I can do the above when the first speaker is added to a session (using “conditions” i.e. speaker column within session table contains a value).

What I really can’t work out is how to do this when I add a second speaker to a session! I can create a new record, but it pulls through all the speakers associated to that session. I just want to create a record for the last speaker added. Or alternatively create multiple records once the speaker list for a session is complete, via a “all speakers added” checkbox or similar.

So in short, I want to automate the creation of a single record for each occasion that a speaker will be speaking for a session.

I have tried everything I can think of but no joy. I’ve even questioned whether my data model is simply not correct but can’t see another way. Any help would be hugely appreciated!

If you read this far, thank you :slight_smile: If you help me I owe you a beer!

Any chance you can reverse your process? Connect Speakers to a Zoom Room first. If there’s already a field for Session in that same table you can set up an Automation to run anytime a new Speaker Room record is made to update the linked record field in the Session table.

That’s a really great idea but I’m not sure it’s possible due to the way the physical process works. Normally we get a rough list of sessions first, with tentative speakers. Then that’ll keep changing (just to make life hard), then at the last minute the techies will give us the list of Zoom rooms! So normally add sessions first, then speakers to those sessions as they come in (and are taken out), then assigning each speaker for each session to a zoom room is last.

But thanks for thinking about it, appreciate it :slight_smile:

It initially felt like in the sessions table I actually needed to make that more granular, with a separate record for each speaker within each session. But the session record also contains things like start time, end time, description etc, so then that info just ends up getting duplicated across loads of records, which is not good because overall sessions can change (e.g. change start time).

Whole thing is hurting my head! Sure someone out there is smarter than me and can work this out though :slight_smile:

Perhaps I’m missing it, but can’t you assign Speakers to Sessions in the Zoom table, and just fill in the Zoom info when you get it?

It seems like you’re linking Speakers to a Session twice: once directly, and the second time via the Zoom table.

Hmmm, I see completely what you’re saying. So you would:

  1. Add sessions to one table
  2. Add speakers to another table
  3. Link session, speaker & room via another table

That would make sense. I think the reason I was going for the automation aspect is to make sure nothing is missed. So it’s easy to list speakers, it’s easy to list sessions then attach speakers from the same table, it’s a bit tricker to ensure you have created an individual record for every speaker:session scenario.

But I guess if I grouped table 3 by session, that makes it a lot easier to scan and make sure nothing is missed. I’ll sleep on it!

Thanks so much for your input, I really do appreciate it.

Okay. If you find you want to keep your original setup and continue on with the Automation, this thread is somewhat similar and can give you an idea of what can be done. This was solved using the Scripting App (meaning you press a button and additional records are created if necessary), but it can be adapted to be run in an Automation.