I am trying to arrange a database with collaborators (Teacher and Assistants) who each share identical data columns (Student Name, Grades, GPA, test scores etc)
Each student is shared with 1 Teacher and 1 Assistant. So, for example:
Teacher 1 and Assistant 1 share studentA
Teacher 1 and Assistant 2 share studentB
Teacher 2 and Assistant 2 share studentC
Teacher 2 and Assistant 1 share studentD
Now, for privacy reasons, my mission is to create an arrangement of tables where
Teacher 1 can see AND edit data on ONLY their studentsA,B
Teacher 2 can see AND edit data on ONLY their studentsC,D
Assistant 1 can see AND edit data on ONLY their studentsA,D
Assistant 2 can see AND edit data on ONLY their studentsB,C
Also, the data must be live updated in a bidirectional way. E.g. if Teacher 1 updates the data for student B in Teacher 1’s table, that data will also be updated in Assistant 2’s table, and vice versa.
Is there a way to arrange this, either through linking tables or through sharing permissions? I have searched the forums extensively and have not found a clear solution, although I’d imagine this scenario would be rather common for airtable users.
Thank you!