Table of People, Table of Events: Magic wand waving ta-dah - New Table of People going to event?

[gaomeile]

I'm sleuthing but not getting anywhere - would be grateful for pointers for the following which is the crux of my business.  Is this an automation? Or a script? Should I start looking at Make? Thank you so much. 

Table A - Long List of People (500+)

Table B - List of One-off Events (500+)

How best to create Table C - Shortlist (50+) people going to 1 event?

I've seen the Event Tracking Base and understand the connected table structure but there are so many People/Events and wondered if, instead of manually typing the person or event into a field, I could go to Table A, scroll through and tick let's say 50 people to go to 1 event in Table B, and have that create a new separate line item record in Table C that I can then use as a lead to chase (without creating duplications if they are already on the list)

[TheTimeSavingCo · Accepted solution]

Hmm, so the end result is for Table C to contain one record per person in the shortlist, and each record is linked to a single record in Table A and a single record in Table B?

If so, you could probably do this via automations where you tick each person you want to attend a specific event, and then you also create a checkbox field in Table B that you tick to indicate that's the event you want everyone to go for.  Your automation would then:
1. Do a "Find Record" of all the records that are ticked in Table A
2. Have a repeating group that would have two actions in it:
  i. A "Create Record" that will create a record in Table C with the appropriate link to the person and the event ticked in Table B
  ii. An "Update Record" action that will update the ticked record in Table A and clear the tick
3. Finally you'd have an "Update Record" action that will update the ticked record in Table B and clear the tick

Your end result would thus be: No ticked records at all in Table A or B, and Table C will contain one record per person ticked, connected to that person and the event you chose

[kuovonne · Accepted solution]

Does table C already exist? It sounds like a junction table. 

What you want can be done with a script or an automation. I have done it with both. 

My favorite method is with a script, because it requires no helper fields. However if you are not familiar with scripting, it is easier to do with an automation. 

Because you do not want to create duplicates, you will need to do a find records to see if the junction record already exists. This means you will need one automation run per potential junction record. 

I like having a helper field that is a direct link between the two tables. Having a value on that field triggers the automation and also specifies both records in the final junction record. 

When the helper linked field has a value, do a find records action to see if the junction record already exists. If it does, just clear the direct link field. If it does not, create the junction record and clear the direct link. 

In practice, I find the easiest way is to create a filtered view of all the selected people, and then fill down the direct link to events in bulk. 

[gaomeile]

This works beautifully. Simple and effective - the two Update Record actions to clear the ticked records in both Table A and Table B is icing on the cake. Thank you so much for writing out the steps.

[gaomeile]

Spent a happy morning learning about Junction Tables which were new to me and already proving so useful for a host of wishes on my to-do list. I need to do more homework on helper fields. Thank you for taking the time to post signals. And the 'filtered view and fill down' is such a quick and easy approach - I can't believe I'd overlooked it.