How to refence via a many to may relationship

I have 2 tables ‘Course’ and ‘Pupils’ which are connected via the ‘CoursePupil’ table which just holds the keys for the other tables thereby providing a many-to-many relationship.

Appsheet automatically creates the Refs to the CoursePupil table and I have a Related CoursePupil field in the Course table.

I would like to create a Virtual column in the Course table which gets a list of all the Pupil emails from the Pupils table (via the CoursePupil table).

Can anyone help me do that?

One way would be to add a virtual column in the CoursePupil junction table that holds the pupil email, and then use a select statement in a virtual column on your course table:


There are other ways I can think of using IN() statements which would do what you need in a single virtual column, and @Steve probably has some cool tricks up his sleeve. Performance-wise, i’m not sure what the best strategy would be.

1 Like

I think it’d be more helpful to create a virtual column (Pupils) in the Courses table to hold a list of all Pupils, with SELECT(CoursePupil[Pupil] , [Course] = [_THISROW].[Course]). At that point you could pull a list of email with [Pupils][email].

Essentially the same as what Jonathon said, but now you have a convenient list of Pupils already in each Course if you want anymore info from them, without having to create more columns in the CoursePupil table.


My suggestion is in line with @Marc_Dillon’s. Performance would benefit from the precalculated virtual column of pupils for the course if you need it more than once.

1 Like