Best design for multiple records linking to multiple other records, with unique final view.

[Alan_Reid]

I am running an event where there are multiple participants that can participate in multiple events.  I know how to link the Participants and Events tables together.  However, I have a need for a view or third table that lists out each unique pairing of participant/event.

Example:

Person A: Event 1,2,3
Person B: Event 2
Person C: Event 1

Final Table

Person A - Event 1
Person A - Event 2
Person A - Event 3
Person B - Event 2
Person C - Event 1

This data will NOT be a one-time import.  People will be added to the base regularly, events will be added as well.  Signups will not be a one and done type thing.

A small wrinkle:  Participants, Events, and Signups will all be added to this base via forms.  Additionally, using automations and forms, event staff will need to be able to edit information in the records.  I know how to set that up already.  What I'm looking to avoid is a participant having to fill out multiple forms to sign up for more than one event.

What is the best base design for this?  I know it's a unique ask and I'm happy to go into further detail if necessary.

Thanks!

[pressGO_design]

Questions:

1. Who adds the events? 
2. Are the participants repeaters also? Or is this for a one-time thing, like a conference or a festival?

3. What sort of editing will need to be done?

4. Why is using this new community forum on mobile so excruciatingly awful?

(That last one isn’t for you obvs😁😁)

Feel free to PM me if that works better for uou. 

[Alan_Reid]

I actually think I got it for the most part.  Other than trying to figure out a script.  To answer your questions:

Events will be added by an "admin" using a form
Participants will not be repeating, this is a one-off event
Once a participant signs up for an event, a staff member will edit the record using a form (and an automation setup I already have designed) to confirm the participant's readiness for the event.

The solution I have come up with is the following

An events table
A participants Table
The participants table links to the events table with a multiple links allowed field
I have a Third table set up called "breakout" which uses a script to make individual entries for each unique pairing of event/participant.  The script is below:

const sourcetable = base.getTable('Events');
const desttable = base.getTable('Breakout');
const query=await sourcetable.selectRecordsAsync({fields:['Event','Participants']});
const converse=rec=>rec.getCellValue('Participants').map(x=>[rec.getCellValue('Event'),{id:x.id}])
const create=(el)=>({fields:{'Event':el[0], 'Participant':[el[1]]}})
const crt=query.records.flatMap(converse).map(create)
while (crt.length) await desttable.createRecordsAsync(crt.splice(0,50))

However, I would like to modify the script to run on each record in the participants table as they are created.  This is where I am stuck, as this script is a manual run.  If it's run again, it'll duplicate every entry.  I got the script from another discussion answer and modified it slightly to fit my needs, so that explains why I am struggling with making it run on each entry (I'm not 100% familiar with Airtable's scripting)

[pressGO_design]

I am script-averse, so unfortunately I can't help with that. But if the only issue you're having is the manual run one, then you could set up an automation that uses the form submit as the trigger and the run a script as the action, no?

[Alan_Reid]

Yeah, that's the idea.  I just need to modify the script because right now it acts on every record in the table, which isn't ideal on a form submit 🙂

[pressGO_design]

I think I got it (but without a script bc see above about me and scripts 😀) - I realize that you're looking for a script solution but if that doesn't work you have another option.... or if some other poor soul comes upon this and has the same relationship to scripting as I do it might help them......

I made a base with 3 tables - events, people, breakout - arrayed them all with a '|', added an extra '|' to the end, used FIND() to find the '|'s, used LEFT() and MID() to parse out the event names and then set up automations to create records in the breakout table when the event names aren't empty.

Link to base here.
Link to form here.

Screenshot of one of the automations (there are 4 of them; one for each event). 

[Alan_Reid]

You gave me something to think about.  I don't think this'll work for my needs because there are literally 100s of events, but it's at least a step in the right direction!  Thanks

[Alan_Reid]

Thankfully, event though I couldn't figure it out, ChatGPT could