Creating multiple rows in one table using values from multiple columns in another table


I’ve been using Airtable for just over a month now and loving it, been able to create workflows that I’d have needed a whole dev team for before! Just brilliant.

But now I’m straying into the world of scripting and feel a little lost, despite lots of reading, testing and trial and error. Think I need to do a course, but in the meantime I’d really appreciate some help.

My base is all focused around event planning. For the challenge in question there are x3 tables:

1. Speakers - a list of speakers at an event
2. Sessions - a breakdown of the event sessions. Each session is a row, with speakers associated with each session listed in columns (Speaker 1, Speaker 2, Speaker 3, Speaker 4, Speaker 5) - and yes, I know this is not ideal and I can use a single field to reference multiple speakers, but this is how I’m supplied the data (in old school spreadsheets which constantly update), and yep, number of speakers varies per session! Speakers are linked to the Speakers table as linked records.
3. Speaker checks - this is the table I want to write to via the script. Basically we have to check off each speaker as they arrive ahead of the session and sure they are prepared. And speakers can be speaking at multiple sessions. So I want a row for every speaker in every session.

So I want to create a script that I can either run via a button for each row in the Session table, or run for the whole Session table to create all the records at once.

Probably easiest to give a small example. I have this in the Session table:

Session 1:

  • Speaker 1 = Dave (linked to speaker)
  • Speaker 2 = Chaz (linked to speaker)

Session 2:

  • Speaker 1 = Chaz (linked to speaker)
  • Speaker 2 = Diane (linked to speaker)

And I’d like to create this in the Speaker checks table:

Item 1 = Session 1, Dave
Item 2 = Session 1, Chaz
Item 3 = Session 2, Chaz
Item 3 = Session 2, Diane

If anyone can steer me in the right direction, even just to get me started, it’d be hugely appreciated! And sorry, can’t share the table just now as has real data in it but can create a demo version if makes it easier.

Thank you in advance.