Jan 19, 202305:10 AM - edited Jan 19, 202305:16 AM
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.
Person A: Event 1,2,3 Person B: Event 2 Person C: Event 1
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.
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:
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)
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?
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.