Email automation with properties from 2 tables that allow linking to multiple records

Dear Airtable community,

I’m new to Airtable and have been struggling with this the whole week.

I have a base with 3 tables (creators, brands, tasks). They are all linked in the following way

  • A task can have multiple creators
  • A task has a single brand
  • A creator can have multiple tasks

What I’m trying to do is send an email every time a new creator is added to a task, with information both from the task (around 12 properties) and the creator tables (around 2 properties, incl. the recipient email address).

I tried 2 things, but none of them worked:

  1. Automation ‘A’ triggers when a task record is updated; the creator linked field is watched. I can create the email body with all the properties I require from the task and pull the email addresses from the creators table with a lookup field. The issue here is that each time I add a new creator to the task (which happens often, as most tasks have up to 10 or more creators) all creators receive an email. This means creators can receive the same email many times. This is sadly not acceptable

  2. Automation ‘B’ triggers when a creator record is updated; the task field is watched. Here I found no way to pull the properties from only the task that triggered the automation, which I require for the email body. I can only pull a list of tasks using the creator as a filter, but this is no good

I’m quite stuck and have no new ideas. Any help would be greatly appreciated!

Thank you so much!

Welcome to the community, @Creators_Club !

You will need to create a many-to-many relationship, which requires creating a 3rd table (called a “junction table”).

This “junction table” represents the “intersection” between creators & tasks.

Each record in your “junction table” will be linked to one unique creator & one unique task.

Airtable explains this type of relationship more in depth here:

1 Like

Dear @ScottWorld, thank you for the warm welcome and for the tip!

It seems aa junction table is exactly what I need. I’ll jump into it immediately. Thanks!

1 Like