Automatically linking records based on multiple fields/conditions?

[pyexed]

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.

[Nathaniel_Grano]

Hello @pyexed and welcome!
There are a few possible approaches here.

Two questions first:

• Can you show an example of a row from Table 1? I’m not clear on what you mean by “Course name fields.” How does Table 1 represent course names?
• Is the idea that you will get new data trickling in from Zapier over time and you want the automation to run when new data comes in? Or do you want something you can run once every so often that processes all the data?

[pyexed]

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.

[Nathaniel_Grano]

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?

[pyexed]

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.

[pyexed]

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.

[Nathaniel_Grano · Accepted solution]

Got it.
So I would think your automation would be something like…

• When a new record is created in Table 2
• Find Records in Table 1 where User ID matches the new record’s User ID AND Course Name matches the new record’s Course Name
• Update Record using the Record ID found in “Find Records” and set the Completed field to Checked

This guide might help: Linking Existing Records Using Automations | Airtable Support