How to refence via a many to may relationship

[Neil_Turner]

Hi
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?

[Jonathon]

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:

SELECT(CoursePupil[email_virtCol],[course_id]=[_THISROW].[id])

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.

[Marc_Dillon]

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.

[Steve]

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.