Project Management Template: Link Multiple tables ...

[cn29]

I'm trying to create a solution for managing events and event attendance.

I have 3 tables: Contacts, Events, Attendance.

In the Contacts table, an Event is manually linked via a linked field (the Contact can have multiple Events in here, sometimes added in quick succession). Once this link is set, I have an automation that runs to create a record in the Attendance table (fields: Contact link, Event link). This table must have one record per Contact per Event. The Contact link is set in the automation for this new record but how do I update the Event link (considering the Contact can have multiple). The part I'm struggling with is how can I link the Attendance table to the correct Event.

Any advice is much appreciated.

Thanks.

[Milan_Automable]

Hi @cn29 , 

If I understand correctly, you're using When a record is Updated Trigger from Event table and Contact field? So when the Contact is added, it will create the new Attendance record.

If this is the case, you can get the ID of the Event that triggered the automation, and use it to set your Event Link.

Hope this helps, let me know if I misunderstood something!

I'd love to explore more use-cases of Airtable and automation specific to your business, if you're interested feel free to book a free call at any time that works for you 🙂

-- Best, Milan - Automable.AI

[Mike_AutomaticN]

Hey @cn29!

Your setup could probably be improved in terms of workflow and database architecture. However, let's tackle your specific issue together!

Please check my schema of tables and fields below.

Please note how I created a lookup field of the Contacts Record ID above. For more on Lookups you might want to check out this article. 

Furthermore, as you might have realized, I'm including the Contact record ID on the name of the Event Attendance records. (see below). This is a key aspect of the automation we will be using.

See automation below:

1. Trigger: When a record is updated on the Clients table (specifically the Events field)

2. Find records on the Events table, where Contact field has any of the Record ID of the contact (record id which triggered the automation) AND Event Attendance table does not contain Record ID of the contact (record id which triggered the automation). As you can see, this is why we are including the Record ID on the Name of the Event Attendance records.

3. Create a Repeating Group on the automation, and choose as input the list of records found under the above Find Records action.

4. For last, within the repeating group (meaning for each of the records found on the Find Records action), create a new record mapping the corresponding values to the Contact an to the Event field.

Result:
If Contact gets tagged for Events A, B, and C, if Contact already has Event Attendance record for Event A, it will only go ahead and create new Event Attendance records for events B and C.

Possible limitation: If you update the Events field too fast, the automation might search for not existing Event Attendance records faster than what you are creating them so you might end up with duplicates.
Possible solution: Trigger the automation manually once you are done updating the events field for each Contact, by checking a checkbox for example.

That might have been long and kind of overwhelming, so feel free to reach out and we can go through it on a call if needed! Let me know your thoughts.

Mike, Consultant @ Automatic Nation

[ScottWorld]

@cn29

In your contacts table: Instead of linking your contacts to the events table, each contact should ONLY be linked to the attendance table.

(Note that when you link contacts to the attendance table, Airtable will automatically create a backlink field from the attendance table to the contacts table.)

Similarly, in your events table: Instead of linking your events to the the contacts table, each event should ONLY be linked to your attendance table.

(And note that when you link events to the attendance table, Airtable will automatically create a backlink field from the attendance table to the events table.)

So, after all of the above, your attendance table will be automatically setup by Airtable so that each attendance record will be linked to ONE contact and ONE event.

Your attendance table is called a "junction table", and that's where most of your work will be accomplished. Here is Airtable's support article on junction tables.

So, you will be primarily working from the attendance table to create new records... that is the table where everything will get accomplished, and where you will do most of your data entry. Each record in the attendance table will need to link to ONE contact and ONE event..

If you want to see all of the events that somebody is attending, you can create a lookup field or a rollup field in your contacts table that will pull from the Attendance table, and you can choose to show the events that they are attending.

I discuss setting up & working with junction tables in this Airtable podcast episode.

Hope this helps! If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Marvel]

You're on the right track! For now, try this:

1. In your automation, use the "Find Records" action to get the most recent event linked to the contact.
2. Then, in the "Create Record" action for the Attendance table, set the Event link to the first result from the found records.

This helps assign an event, but it may not always pick the exact one you want. If you need full control over which event gets linked, reach out, and I’ll guide you through it!