I have created a course registration database with the following relevant tables: course, students, registrations (the join table).

Each course has a maximum capacity that is set by the 'capactity' field in the course table. If a student registers and the capacity has been reached, then the "waiting list" field in the registration table should be checked.

Anyone have an idea of how I can accomplish this?

Thanks,

Moses

Hi Moe,

make a selfjoin relationship in your Registrations table, based on CourseID::CourseID. Make your WaitingList number field an auto-enter calculation

If (Count(Selfjoin::CourseID)>Courses::MaxStudents);1;0)

or simply :

Count(Selfjoin::CourseID)>Courses::MaxStudents

The MaxStudents field in your Courses table would hold the maximum number of students allowed, of course.

HTH,

Peter

Peter,

Thanks for writing. I like your idea, but what if somebody is deleted from registration? How do I make the person that is next in line go off of the waiting list?

Thanks,

Moses

I'd recommend using a regular text field to hold the Status of each Student. Then use scripts to set the Status when a student registers, or when a seat becomes available. You can use a Timestamp field to remember when a Student is placed on the waiting list, and then use that for sorting the selection list.

If it's also important to remember the Date/Time a student enrolls, drops, or transfers, then you might instead use a Date of Timestamp field for each of the things that affect the Status, and have the Status field be calculated based on which Dates or Timestamps are filled in.

In any case, I'd use scripts to make the changes.

I was trying to avoid using scripts, but it seems I will have to. Thanks for your help!

Sorry, I should have explained my reasoning. Using regular text fields (or Date/Timestamp fields) allows you to manually change the enrolled/waiting lists. This can be helpful when you have to get the alumni's kid in right away.

Besides, if it were happening automatically, there would be no way to trigger a notice to a student that they are now enrolled after being on the waiting list. By using scripts, you can send/print notices for each time someone drops, gets on the waiting list, transfers, and gets slotted into a class.

I agree with Ender that scripting this would be the best way to go.

Regards,

Peter