I’m trying to merge data from two linked tables in Airtable. Here’s what I’m working with:
Table 1 (Students):
Student | Courses
------------------
Alice | Math,Science,Art
Bob | Science,History
Charlie | Math,History
Table 2 (Courses):
Course | Year | Students
--------------------------
Math | 2022 | Alice,Charlie
Science | 2023 | Alice,Bob
Art | 2021 | Alice
History | 2022 | Bob,Charlie
What I need is a combined view like this:
Student | Course | Year
-------------------------
Alice | Math | 2022
Alice | Science | 2023
Alice | Art | 2021
Bob | Science | 2023
Bob | History | 2022
Charlie | Math | 2022
Charlie | History | 2022
Can someone help me figure out how to create this unified view in Airtable? I’m not sure which features or formulas I should use to achieve this. Thanks in advance for any tips or step-by-step guidance!
hey there! i’ve had this issue too. what worked for me was using a Lookup field in the Students table. set it to grab the Year from Courses. then make a new view in Students, add all the fields you want, and group by Courses. it’s not perfect but gets you pretty close to what you need. good luck!
I’ve tackled a similar challenge in Airtable before, and here’s what worked for me:
Create a new table called ‘Student-Course Enrollment’. This table will serve as a junction between Students and Courses. Set up fields for Student (linked to Students table), Course (linked to Courses table), and Year (lookup from Courses table).
Then, use the ‘Import data’ feature to populate this new table. You’ll need to import the data twice - once for each original table - to ensure all connections are made.
Finally, create a view in this new table that shows Student, Course, and Year. This approach gives you the flexibility to add more fields later if needed, like enrollment dates or grades.
It’s a bit more work upfront, but it creates a more robust and scalable solution in the long run. Plus, it maintains the integrity of your original data structure.
To create the unified view you’re looking for, you’ll want to use Airtable’s ‘Linked Record’ feature along with a rollup field. Here’s a step-by-step approach:
-
Ensure your ‘Students’ and ‘Courses’ tables are linked properly.
-
In the ‘Students’ table, create a new field called ‘Course Details’ as a Lookup field.
-
Set this Lookup field to look up ‘Year’ from the linked ‘Courses’ table.
-
Now, create a new view in the ‘Students’ table. Choose ‘Grid view’ for simplicity.
-
In this new view, add the Student, Courses, and Course Details fields.
-
Finally, use the ‘Group’ feature to group the records by the ‘Courses’ field.
This should give you a view close to what you’re after. It might require some tweaking, but it’s a solid starting point. The key is leveraging the relationship between your linked tables effectively.