Trouble with Value Lists and Relationships
I am building a student system and when a student enrols they must be assigned a tutor depending on the course, the course type, whether the tutor is active and also if the tutor has spaces available on that course.
A tutor is active if 'Tutor Status' = "Active" and 'Tutor Allocation' = "Allocate" these fields are in a Tutor table.
I used a calculation If(Tutor Status = "Active" and Tutor Allocation = "Allocate"; "Yes"; "No") to calculate an 'Active Tutor' field.
There is a Course Tutorial table which features as a portal on the Tutor table.
It has 'Course Name'
'Enrolled Students' (ValueCount ( FilterValues ( List ( Enrolled Courses::Course Status ); "On Programme" ) and Case ( Enrolled Courses::Course Name; Course Name )and Case ( Enrolled Courses::Course Type; Course Type ) )
'Student Spaces' (=Maximum Students-Enrolled Students))
'Tutor Record Number'
The Course Tutorial works well to show how many spaces are left on courses with a particular tutor.
I tried using an 'Available Tutor' field on the Course Tutorial table to find tutors who were active and had spaces. (If(Enrolled Students>0 and Tutors::Active Tutor = "Yes"; Tutor Name; "") This field works but it is not possible to index the field.
I have got myself confused as to what I need to do to get only the relevant tutors appearing as a drop down list when enrolling a student.
Any help would be appreciated.