Combining information from two linked Airtable tables

Other Questions Airtable
1

I’m working on an Airtable project and I’m stuck. I need to merge data from two linked tables. Here’s what I’ve got:

Table 1 (Students):

Student | Courses
------------------
Emma    | Math,History,Science
Liam    | Math,English
Olivia  | History,Science

Table 2 (Courses):

Course  | Year | Students
---------------------------
Math    | 2022 | Emma,Liam
History | 2021 | Emma,Olivia
Science | 2023 | Emma,Olivia
English | 2022 | Liam

What I want is:

Student | Course  | Year
------------------------
Emma    | Math    | 2022
Emma    | History | 2021
Emma    | Science | 2023
Liam    | Math    | 2022
Liam    | English | 2022
Olivia  | History | 2021
Olivia  | Science | 2023

Can someone help me figure out how to create this combined view or table? I’ve tried a few things but can’t seem to get it right. Thanks!

2

hey, have u tried using a lookup field? might work for this. in the Students table, add a lookup field that pulls the Year from Courses. then make a view showing Student, Course (from the existing link), and the new Year lookup. could give u what ur after without changing ur setup much. lemme know if that helps!

3

Having worked with Airtable extensively, I can suggest a solution that doesn’t require creating a new table. You can achieve this using a rollup field in the Students table. Here’s how:

  1. In the Students table, add a new rollup field linked to Courses.
  2. Set the rollup to ‘For each linked record’ and choose ‘Show all values’.
  3. In the calculation, use {Course Name} & ’ | ’ & {Year}.

This will give you a list of courses with years for each student. Then, create a view in the Students table, enable ‘Expand record fields’, and select the new rollup field. This should display each student’s courses and years separately.

For the final touch, use column splitting to separate the course and year into distinct columns. This method maintains your existing structure while providing the combined view you’re after.

4

I’ve tackled a similar challenge in Airtable before, and I found that using a junction table approach works well for this kind of many-to-many relationship between students and courses.

Here’s what I’d suggest:

  1. Create a new table called ‘Enrollments’ that links to both Students and Courses.
  2. In the Enrollments table, add fields for Student (link to Students), Course (link to Courses), and Year (pulled from Courses).
  3. Populate the Enrollments table with the correct relationships.
  4. Create a view in the Enrollments table that shows Student, Course, and Year.

This approach maintains data integrity and allows for easy querying and filtering. It might take a bit more setup initially, but it’s much more flexible in the long run, especially if you need to add more details to the student-course relationship later on.

Hope this helps! Let me know if you need any clarification on the steps.