Help

Automation from two tables, creating single entries in a third table

389 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Brendon_Johnson
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi all, I am trying to create an automation where when I add an activity to a person in the first table (which draws from a second table), in a third table it automatically creates a row with the person and activity each as individual entries, which I can manage in a Kanban with a third drop-down option, as outlined below. I want to be able to match each person individually to each activity for this Kanban option, so I can filter and monitor people's responses to an activity overall one at a time. 

The problem is, with the automation I've created, instead of creating an individual entry for the latest activity I've added with the person I added it to (see table 3 DESIRED below), it instead creates a row with all of the activities attached to that person (as you see in the last table – table 3 ACTUAL).

The automation goes like this: 

  1. When "activity" is updated in table 1
  2. Automatically create new entry in table 3

I tried adding a step in between – search for "person" in table 2, where "last modified" is "today" – but when I make multiple changes at the same time to "activity to invite to" in table 1, it still results the same (table 3 ACTUAL below).

Any suggestions for how to resolve this?

Table 1: People

NameActivity to Invite To (link to table 2, multi-select)
John DoePeer Coaching
Mary JaneLearning Group, Peer Coaching

Table 2: Activities

ActivityPeople (link to table 1)
Learning GroupJohn Doe, Mary Jane
Peer CoachingMary Jane

Table 3: Tracking Invites (DESIRED)

Summary (formula for Person + Activity)Person (link to table 1)Activity (link to table 2)Status (multi-select)
John Doe: Learning GroupJohn DoeLearning GroupInvited
Mary Jane: Learning GroupMary JaneLearning GroupConfirmed
Mary Jane: Peer CoachingMary JanePeer CoachingDenied

Table 3: Tracking Invites (ACTUAL)

Summary (formula for Person + Activity)Person (link to table 1)Activity (link to table 2)Status (multi-select)
John Doe: Learning GroupJohn DoeLearning GroupInvited
Mary Jane: Learning Group, Peer CoachingMary JaneLearning Group, Peer CoachingConfirmed
1 Reply 1

My gut feeling is that you'll need to solve this with a script.  Have you attempted a script, or have you only used non-Script actions in your initial Automation attempts?