Change data record of another table when a data record in table 1 changes.

[mirkolando]

Hello everyone,

I have two tables. Table 1 contains campaigns and table 2 contains events. The events can then be linked to the campaigns with a linked data record. Now I would like a notification to be sent to the creator of the event if something changes in the campaign.

I would also like to display both the campaigns from Table 1 and the events in a timeline overview. How can I do this with values from two tables?

Thank you and best regards.

[dilipborad]

Hello @mirkolando ,

First of all some of the content is generated using AI but if you know the proper prompt then you can directly ask it on different AI tools.

The structure of the Table/fields varies from your current structure but think about the concept and workflow that need to follow.

We'll create a database structure with two tables - one for campaigns and another for events. Then, I'll guide you through setting up automation in Airtable to notify the event creators when there are changes in the linked campaigns. Additionally, I'll explain how to create a timeline overview using both tables.

Database Structure

1. Table 1: Campaigns

   • Campaign ID: (Single Line Text) - Unique identifier for each campaign.
   • Campaign Name: (Single Line Text) - Name of the campaign.
   • Description: (Long Text) - Details about the campaign.
   • Status: (Single Select) - Current status of the campaign (e.g., Planning, Active, Completed).
   • Start Date: (Date) - Campaign start date.
   • End Date: (Date) - Campaign end date.
   • Updates: (Long Text) - Field to note any changes or updates in the campaign.

2. Table 2: Events

   • Event ID: (Single Line Text) - Unique identifier for each event.
   • Event Name: (Single Line Text) - Name of the event.
   • Description: (Long Text) - Details about the event.
   • Event Date: (Date) - Date of the event.
   • Linked Campaign: (Link to another record) - Link this field to the Campaigns table to associate events with campaigns.
   • Event Creator: (Email) - Email address of the event creator.

Setting up Automation in Airtable

1. Create a View for Updated Campaigns in the Campaigns Table

   • Create a view (e.g., "Recently Updated Campaigns") that filters campaigns based on the "Updates" field or the "Last Modified Time" field.

2. Create Automation for Notifications

   • Go to the “Automation” panel in Airtable.
   • Create a new automation with the trigger set to "When record enters a view" and select the "Recently Updated Campaigns" view.
   • For the action, choose "Send an email."
   • Configure the email action to use the email addresses from the linked records in the Events table. Use the "Event Creator" field to target the notification to the right person. In the email body, include details about the update.

Creating a Timeline Overview

1. Use a Gantt Chart or Timeline Block
   • Airtable offers blocks like Gantt chart or Timeline that can visualize data from multiple tables.
   • Select both the Campaigns and Events tables as sources.
   • Configure the block to display information based on the dates in both tables (Start Date and End Date for campaigns, Event Date for events).

This setup allows for efficient tracking and notification regarding campaign updates and provides a visual timeline that integrates data from both the Campaigns and Events tables.

[mirkolando]

Hi @dilipborad, 

thank you for the detailed an quick answer. 

I just dont find a way how to configure this part of your explanation. 

• Configure the email action to use the email addresses from the linked records in the Events table. Use the "Event Creator" field to target the notification to the right person. In the email body, include details about the update.

How can i add the email addresses from the linked records in the Events table? 

[TheTimeSavingCo]

If the number of events linked to a single campaign will definitely be less than 100 and you want to personalize the emails, you could try:

1. Create an automation that'll trigger when the campaign gets updated
2. Use the "Repeating group" function
3. Look for the appropriate event record (based on the name, or ID or something)
4. Have a "Send email" action that'll use the found record data from step 3 for the email.  This'll let you do stuff like "Hello [Name]" etc since you're able to use all the data from the record and personalize the email

And so you'll end up with something that looks like this:

And here's a link to the base where it's set up

--

For the timeline thing, does this mean that both a campaign record and an events record will have start and end times?  If so, you'll probably need to just consolidate all the records into a single table I'm afraid

[dilipborad]

Hello @mirkolando ,

Try the things given by @TheTimeSavingCo .

If it's not works then use these things to configure the email action in Airtable automation to use the email addresses from the linked records in the Events table, follow these steps:

Step-by-Step Solution

1. Ensure Proper Linking Between Tables

   • First, make sure that each event in the Events table is correctly linked to a campaign in the Campaigns table. This is done through the "Linked Campaign" field in the Events table.

2. Create a Lookup Field

   • In the Campaigns table, add a new field. Choose the type "Lookup."
   • For this Lookup field, select the link to the Events table and then choose the "Event Creator" field. This will pull in the email addresses of the event creators related to each campaign.

3. Set Up the Automation

   • Go to the "Automations" panel in Airtable.
   • Create a new automation and set the trigger as before (e.g., "When record enters a view" for recently updated campaigns).
   • For the action, select "Send an email."

4. Configure the Email Action

   • In the "To" section of the email setup, use dynamic content from your Airtable records.
   • Here, you should see the option to insert data from the Lookup field you created. Select this field, and it will dynamically insert the email addresses of the event creators linked to the updated campaign.
   • In the email body, you can also include dynamic content, such as the name of the campaign, details of the update, etc.

5. Test the Automation

   • Before fully implementing, test the automation to ensure it works as expected. Make a change in a campaign record that would trigger the automation and check if the correct email is sent to the right event creator.

By using a Lookup field to gather email addresses from linked records and incorporating this into your automation, you can ensure that event creators are notified whenever there's an update in the campaigns they're linked to.

[mirkolando]

Hi @TheTimeSavingCo

thanks for your answer.

I just dont understand the point:

3. look for the appropriate event record (based on the name, or ID or something)

I can create a configuration as follows:

show me all records, in the Events table, where Focus & Stories (Campaigns) is exactly (Trigger record). But I can only select a specific record and not the record from which it was triggered.

Or is that possible?

[TheTimeSavingCo]

Ah no, that step is supposed to look for one specific event record so you can grab data from it, not all the event records that are linked to that campaign.  If you don't need to personalize it I think you might want to just go with @dilipborad 's solution actually, it's a lot more elegant than mine

[mirkolando]

@TheTimeSavingCo @dilipborad 

exactly, I need all linked data records.
But I actually like your idea. Because then I can also use all the information from the data records in the email.

For example:

Hi, (name)

the focus / story mentioned in the subject line has changed. Please check your events for necessary adjustments.

The following events are affected:
Event 1
Event 3

Is there a solution for this?

[mirkolando]

i found out how to do it. Thank you guys 🙂 

[mirkolando]

@dilipborad  how can i add a second data source to a timeline?