Student Table

StudentID

Grade

Classes Table

ClassID

Grade

Enrollment Cap

Time

Student|Class Table

StudentID

ClassID

I want students to enroll for a summer school class.

When they enter their StudentID and Grade, I want a portal to Student|Class configure with a value list of available Classes for the student to enroll in.

That value list should be filtered by Grade. Easy enough.

But it should further be filtered by enrollment Cap, so if the class is full it no longer appears in the Value List (haven't figure this out yet).

Also, each student can select 2 classes. If they pick a class that runs from 8:00 - 9:30 AM, their 2nd pick should be filtered so only the classes with time 9:30 - 11:00 AM in their Grade Level that are NOT full are listed.

So the value list needs to be configured like this:

Pick 1

Grade Level - Enrollment Cap

Pick 2

Grade Level - Enrollment Cap - Time of 1st selection

How is this done?

BTW: The only way I can think of to do this is to create a Global Calculation in the Student Table for Enrollment Status = (Available) and creating an Enrollment Status in the Classes table that would be either Available or Full, which could then be filtered in a value list.

BUT, to do that for class times I still cannot figure out.

I could setup another Global Calculation in the Student Table for 1st Pick Time = 8:00 - 9:30 AM and make two relationships to the Class Table one that equals 8:00 - 9:30 AM and one that doesn't, but I don't know how to setup 2 portals on the Student layout to all students to enter 1st Pick and 2nd Pick.

Well I cannot make a conditional value list on enrollment status.

In the Class Table, Enrollment Status is a Calculation based on a self relationship to see if the current enrollment is more than the enrollment cap. Because of that, a relationship will not work between Students <-> Classes because the Calculation cannot be indexed.

I am not sure how to do this then.

I can get the Conditional Value List to appear based on Grade, since I can enter a Grade for the Student, and enter Grades availability for each Class.

BUT, I want to make sure users cannot add a class to a student if the class is FULL. I guess this just cannot be done.