How to automatically link records between a summary table and more detailed list?

[Virginia_McManu]

Hi,

I'm pretty new to Airtable, and I've set up a table with a list of students that my organization works with at a certain school district, where each record is an individual child (we only work with some of the children in the school district). I've linked that table to another one that has a list of all of the reading assessments completed by all children in the school district, where each record is an individual assessment("Mastery Test" = MT) .

Here's part of that first table (record = student):

Here's part of the second table (record = assessment):

I've got the formulas set up that I need, but it's been extremely time-consuming to manually link the records between the two tables (there are over 1,500 students & 36,000 assessments). 

I've read through the general guidance about using automations, and also related questions in the discussion board, but I can't find an answer to what I'm trying to do, which is to tell the first table (the list of students): only link the records if the student ID matches in both tables. I've also never set up an automation before, so maybe this is easy and I just can't figure it out yet. I'd appreciate some guidance; thank you!

[Stephen_Orr1]

Hi @Virginia_McManu,

It’s not too difficult to setup. Your automation would be something like this:

Trigger: when a record in the MT table is created or updated (I would choose updated first to be able to run it initially on all records by creating, mass populating, then deleting some new field in this table, and then switch to created for future runs)

Action: “Find Records” from your Students table where the student ID from your trigger record source (MT) matches the student ID in your Students table

Action: “Update Record” in MT (the trigger record) and populate the Students linked record field with the Find Records step’s found list of records

Please try it out and let me know if you need more help

-Stephen

[TheTimeSavingCo]

If this is a one off for your existing data and both tables have the student ID, you could also try:
1. In the first table, change the "Child Full Name" field to contain the student ID instead
2. In the second table, click the header of the field that contains the student ID, selecting the entire column, and then copy the values
3. Still in the second table, click the header of the linked field to the first table and then paste

This should link everything like you want, and you can now change the first field to be "Child Full Name"Again