I’m working with two linked tables in Airtable and need to combine them into a single output. Here’s what I’m dealing with:
STUDENT table:
Student | Courses
-----------------
Alex | Math,History,Art,Science
Emma | History,Science
Tom | History,Art
COURSES table (linked):
Course | Year | Students
------------------------
Math | 2020 | Alex
History | 2018 | Alex,Emma,Tom
Art | 2019 | Alex,Tom
English | 2021 |
Music | 2022 |
Science | 2017 | Alex,Emma
My goal is to create this flattened view:
Student | Course | Year
---------------------
Alex | Math | 2020
Alex | History | 2018
Alex | Art | 2019
Alex | Science | 2017
Emma | History | 2018
Emma | Science | 2017
Tom | History | 2018
Tom | Art | 2019
What’s the best way to create this combined table or view in Airtable?
The junction table approach works great, but there’s an easier way if you don’t mind a few workarounds. Just create a formula field in your STUDENT table that pulls from the linked COURSES data. Set up a formula that combines each student with their course info, then export that to a new table. I’ve done this tons of times when clients want flattened reports. The key is using ARRAYJOIN with some conditional logic to grab the year data through the relationship. It’s messier than a proper junction table, but you get that denormalized output without manually creating hundreds of enrollment records.
To achieve the combined view you’re looking for in Airtable, you should consider creating an intermediate junction table. This ENROLLMENTS table should have linked fields for both Student and Course, pulling the Year from the COURSES table as a lookup. Unfortunately, you’ll have to create entries for each student-course combination manually, but this approach will give you the denormalized structure you need. It’s a bit of extra work initially, but it significantly enhances data organization and integrity, allowing for better reporting and analysis later on.
Just duplicate your courses table and add a student field. Then manually split each course record by student - so history becomes 3 separate records (one for Alex, Emma, Tom). It’s tedious but you’ll get exactly what you want without worrying about complex formulas breaking later.
Been there with similar data headaches. Don’t create junction tables manually - it’s a nightmare with large datasets.
Just use Airtable’s automation. Set up a script that runs when records update in either table. It loops through STUDENT records, grabs linked courses, then auto-creates records in your ENROLLMENTS table.
Built something similar for customer data last year. Script pulls course name and year from lookup, matches with student, populates your flattened table. Takes 30 minutes to set up, saves hours of manual work.
Alternatively, export both tables to Google Sheets, use VLOOKUP to merge, then import back. Less elegant but works when you need it fast.
Automation scales better though. Once running, your flattened view updates automatically when someone adds students or courses.