auto linking or updating records from one table with data from another table

[Linda_Bonder]

I appreciate your help!

I have one main table of English learners who register for classes - name, contact info, etc. Let's call is the Main Student table.

There are two other tables that get data related to each student. (One table gets data from Stripe if the student is able to pay anything. Another table shows teacher comments about the student from the prior year.)

In the past, we set up links between the tables. Then, for each student, we manually linked the individual records and had "lookup" fields for the fee & comments in the main Student table. But we had 750 learners register with us last year!

How can we automate this? I'm thinking that an automation would need to:

1. Find matches between the Main Student table and the other tables (based on one or more of name / email / phone)

2. Update the record on the Main Student table with the data for that student in the other tables.

I have searched for examples (found one relevant one but from 2019) and tried some combinations of "find records" and "update record", but I'm lost. I have never used scripts ...

Thank you!!

[TheTimeSavingCo]

Hmm, assuming this is a one off, to link the Main Student table to the Stripe table, I would try:
1. Change the primary field of the 'Main Student' table to be the student's email address
2. In the 'Stripe' table, click the header of the field that contains the email address so that the entire column is selected and then hit CTRL / CMD + C
3. In the 'Stripe' table, click the header of the field that's linked to the 'Main Student' table and then hit CTRL / CTRL + V

This assumes that both the 'Main Student' table and the 'Stripe' table have the students email address, and that the email address used to pay on Stripe is the same as the one in 'Main Student'.  This might not be true for a few of the students and those will have to be handled manually unless you have some other identifier to match the two together

---
For the Main Student to the Comments table, I would repeat the steps above after identifying the data that would let me match the records between the two tables

---
For new data, I would create an automation that would trigger every time a new record gets created on Stripe (this trigger depends on how these records are created), and its action would be to look for a record in 'Main Student' with the same email, and if it found one, to link them together

[Linda_Bonder]

Thanks, Adam. Actually, it's all for new data. I want to be able to auto link new records as they come in. So it's your last point - "look for a record with the same email, and if it found one, to link them together" - how do I do that? I can set up an automation, and I know about the "find record" action. But I don't know how to find a record in one table with a field that matches a field in a record in another table. ??

[TheTimeSavingCo]

Hmm, I'm not too sure what you're having trouble with, so here's a screenshot of the condition setup I would use:

And check out Airtable's guide on how to find and link records together via automations here: https://support.airtable.com/docs/linking-existing-records-using-automations

If you could invite me to a duplicated copy of your base with no data in it I could set it up for you real quick too

[Linda_Bonder]

Hi - The AT guide has the automation looking for a static field that says "Regular". I need it to look for a value that matches a value in the other table.

So here is a base to play with: https://airtable.com/invite/l?inviteId=invbyoyvuGehsYDRk&inviteToken=6fa8d2c9746d945d5b1211430971c6c...

It has 2 tables. I want the automation to Link the record in Table 1 with the record on Table 2 that has the same email address.

For now, the automation could be triggered by setting the "Trigger" field in Table 1 to "yes." For production, I'll want the automation to be triggered when a new field enters Table 1.

THANK YOU!!

[TheTimeSavingCo · Accepted solution]

Thanks for the invite!  I've created the automation in the linked base:

There wasn't a linked field to Table 2 so I created that as well

[Linda_Bonder]

Thank you SOOOOO much!!! I had been heading down a similar path but clearly got confused somewhere along the way. We are an all volunteer non-profit, so need to automate everything possible. This is incredibly helpful. Thank you!