Filter Gallery based on SharePoint list relationship (one to many)
I need a help, really don't understand how to achieve this.
Let's start with the data model.
I have 3 SP lists: Educations, Courses and SignUp table.
They have a one to many relationship in between themselves, one education can have many courses and each course can have many participants, as it is shown in the picture below.
Education ID is primary key and it's used in Course table as a link between
Course ID is primary key and it's used in Sign-Up table as a link between
And I'm using this relationship to work with galleries in my App.
My questions are:
1. How to exclude courses for which user have already applied ( i understand the querry: I have to filter the data WHERE active app login email = email in the "SignUp table" && course ID "SignUp table" = Course ID "Course table")
2. How to count number of courses for each education (number of children)
3. When deleting education, how to delete all corresponding courses and signups?