Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 15, 2022 07:49 AM
Hi! I have two tables with data that’s pulled from another site through Zapier. Table 1 shows a list of registrants for an online class (User ID, Name, Email, Course Name fields), and Table 2 shows a list of users who completed a class (User ID, Name, Email, Course Name, Completion Status fields).
I’m trying to set up an automation that would add link records in the two tables, so that I can see which people who registered for a course actually completed it (by adding a Lookup field in Table 1 that would pull in the “Completion Status” field from Table 2). Creating a manual link each time someone completes a course is possible but would take up a lot of time.
The problem is that each person might register for and complete multiple courses and would have multiple records in each table. So an automation that links records just through the Name or User ID fields wouldn’t necessarily work. Is it possible to set up an automation that would link records only if the User ID and Course Names both match across the two tables?
I know this setup isn’t ideal and that I might just have to change how a contractor set up Zapier to pull the data, but I’m wondering if this is possible to do just in Airtable itself.
Solved! Go to Solution.
Nov 15, 2022 10:30 AM
Got it.
So I would think your automation would be something like…
This guide might help: Linking Existing Records Using Automations | Airtable Support
Nov 15, 2022 08:27 AM
Hello @pyexed and welcome!
There are a few possible approaches here.
Two questions first:
Nov 15, 2022 08:50 AM
Hi @Nathaniel_Granor! Thanks! The course name fields look the same across the two tables–text fields with the name of the workshop that a user registered for or completed:
Table 1:
Table 2:
And yeah, I’d ideally want this to run when new data comes in.
Nov 15, 2022 08:58 AM
Hm, I guess I’m not understanding what the need for Table 1 is at all. It seems like Table 2 has all the same information as Table 1 PLUS the status, in which case why not just look at Table 2?
Can you show a row in Table 1 and a corresponding row in Table 2?
Nov 15, 2022 09:15 AM
Sorry! I was being unclear. Table 1 shows registrations, while Table 2 shows completers. So Table 2 inherently shows people who have registered for a course and have completed it, but doesn’t show registrants who haven’t completed a course.
Nov 15, 2022 10:16 AM
And here’s what the rows looks like:
Table 1:
Table 1 also includes their titles and info about their organizations.
Table 2:
That last field is the completion status (Field Name = “Completed?”), which isn’t pulled in automatically through Zapier. It’s populated with a formula to show “Yes” when a record is created, so right now it works as a lookup field for manually linked records.
Nov 15, 2022 10:30 AM
Got it.
So I would think your automation would be something like…
This guide might help: Linking Existing Records Using Automations | Airtable Support