Combining two linked Airtable tables into a single view

Other Questions Airtable
1

I’m working with Airtable and I’ve got a problem. There are two tables that are linked together, and I want to merge them into one view. Here’s what I’m dealing with:

Table 1 (Students):

Student | Courses
-------------------
Alice   | Math,Science,Art
Bob     | Math,History
Charlie | Science,History

Table 2 (Courses):

Course  | Year | Students
----------------------------
Math    | 2023 | Alice,Bob
Science | 2022 | Alice,Charlie
Art     | 2024 | Alice
History | 2023 | Bob,Charlie

What I’m trying to get is something like this:

Student | Course  | Year
-------------------------
Alice   | Math    | 2023
Alice   | Science | 2022
Alice   | Art     | 2024
Bob     | Math    | 2023
Bob     | History | 2023
Charlie | Science | 2022
Charlie | History | 2023

Can anyone help me figure out how to create this combined view in Airtable? I’m not sure if I need to use a formula or another feature. Thanks!

2

hey emma, u might wanna try using a ‘Linked Record’ field in ur Students table. add a new field that links to the Courses table. then create a new view and select the linked fields u want. it should give u somethin close to what ur after. hope this helps!

3

I’ve actually tackled a similar problem before, and I found that using a junction table can be really effective. Here’s what I’d suggest:

Create a new table called ‘Enrollments’ with these fields:

  • Student (link to Students table)
  • Course (link to Courses table)
  • Year (pulled from Courses table)

Then, populate this table with all the student-course combinations. You can do this manually or use Airtable’s API if you’re comfortable with coding.

Once that’s set up, create a view in the Enrollments table. This view will naturally give you the structure you’re after, with each student-course pair on its own row, along with the year.

It’s a bit of work upfront, but it makes managing the data much easier in the long run, especially if you need to add more details about each enrollment later on.

4

To achieve the combined view you’re looking for, you’ll need to leverage Airtable’s relational database capabilities. First, ensure your ‘Courses’ field in the Students table is set up as a Link to Another Record field, connecting to the Courses table. Then, create a new view in the Students table.

In this new view, add fields from both tables: Student Name, Course Name (from the linked record), and Year (also from the linked record). This should automatically expand the linked courses for each student into separate rows.

To refine further, you can use field visibility settings to hide unnecessary columns and sort the data as needed. This approach should give you the desired result without complex formulas.