How to merge two connected Airtable tables into one combined view

Other Questions Airtable
1

I’m working with two linked tables in Airtable and need to combine their data into a single output. Here’s what I’m dealing with:

My STUDENT table contains:

Student | Courses
-----------------
Alice   | X,Y,Z,W
Bob     | Y,W
Tom     | Y,Z

The linked COURSES table shows:

Course | Year | Students
------------------------
X      | 1980 | Alice
Y      | 1995 | Alice,Bob,Tom
Z      | 1850 | Alice,Tom
V      | 1500 |
U      | 2015 |
W      | 2005 | Alice,Bob

I want to create a flattened result like this:

Student|Course|Year
------------------
Alice  | X    | 1980
Alice  | Y    | 1995
Alice  | Z    | 1850
Alice  | W    | 2005
Bob    | Y    | 1995
Bob    | W    | 2005
Tom    | Y    | 1995
Tom    | Z    | 1850

What’s the best way to create this kind of merged view in Airtable?

2

Honestly, the easiest way I’ve found is using Airtable’s sync feature with Zapier or Integromat. Set up automation that creates new records in a separate “flattened” table whenever there’s changes in your student or course tables. Takes some setup work upfront, but then it runs automatically and you don’t have to mess with complex formulas or junction tables.

3

I’ve had good luck with this approach using Airtable’s lookup and rollup fields - no extra tables needed. First, make sure your linked field between the two tables is set up right. Then go to your COURSES table, create a new view, and add a rollup field that expands the linked Students field. This automatically creates separate rows for each student-course combo. You can use this expanded view for reports or exports. The trick is configuring the rollup to show each linked record individually instead of summarizing them. Your original tables stay unchanged, but you get the flattened data you need.

4

To merge your STUDENT and COURSES tables in Airtable effectively, consider creating a junction table. This intermediary table would establish links between students and their respective courses. By doing so, each student-course relationship is captured as a unique record. Implementing Airtable’s automation can streamline this process, allowing for real-time updates whenever a student enrolls in a course. This method not only produces the desired flattened view but also maintains the integrity of your original tables.