Scripting Enrollment

[ktalley]

Hello,

I am wanting to create a script that will "enroll" a found set of students in a particular class. I have a camp database hosted on FileMaker Server 11 with clients running FileMaker Pro 9. This pertains to my summer music camp database that I've been working through some of here: http://fmforums.com/forum/topic/78174-value-list-of-current-employees/

I have a students table, an enrollment join table, and a table of classes/rehearsals. I also have a faculty table and join table to link faculty who are teaching each of the classes/rehearsals. The problem I will encounter is enrolling the students into a rehearsal. This is a band camp, so students audition on the first day to be placed in a band based upon skill level. We don't know what band and section rehearsal they will be in until they audition - and we have to process 500 students in a short period of time. In order for me to easily create roster sheets for the faculty, I would like a way to automate enrollment into a certain rehearsal/class.

For example:

Students

Johnny - Trumpet

Sue - Trumpet

Bob - Trumpet

Billy - Saxophone

Stacey - Saxophone

Jill - Saxophone

etc.

Bands

Blue Band

Red Band

White Band

Gold Band

Based upon auditions, we enter the following manually into each students' record:

Johnny - Trumpet - Blue Band - Chair 1

Sue - Trumpet - Blue Band - Chair 2

Bob - Trumpet - Red Band - Char 1

Billy - Saxophone - Blue Band - Chair 1

Stacey - Saxophone - Blue Band - Chair 2

Jill - Saxophone - Red Band - Chair 1

etc.

For the band rehearsals, I can easily create a roster by just searching for "Band Name" and sorting by section and chair. But each section in every band has different section rehearsals with a different faculty member teaching each section (around 48 different section rehearsals). There are also elective classes (that are different from the band and section rehearsals) that the students enroll ahead of time. I have a script already that loops through the teaching faculty and prints a roster for each of their classes. I would like to tie into this eventually so a single faculty member will be able to get all their rosters at one time.

So, in this example, I need a script that will search for "trumpet" and "blue band" and enroll the found students into the "Blue Band Trumpet Section Rehearsal" Class and the "Blue Band Rehearsal" classes then do the same for each instrument and band combination ("trumpet" and "red band", "saxophone" and "blue band", etc.) - which will be about 48 different rosters.

I can do this manually when entering in band and chair information, as we enroll them in the elective classes they pick out ahead of time, but is a little tedious and time consuming. Any thoughts on how to build this script?

Thank you for your assistance.

kt

[comment]

I need a script that will search for "trumpet" and "blue band" and enroll the found students into the "Blue Band Trumpet Section Rehearsal" Class

Why is it necessary to enroll them - when the "Blue Band Trumpet Section Rehearsal" Class can 'see' them based on matching "Blue Band" and "Trumpet"?

[ktalley]

Why is it necessary to enroll them - when the "Blue Band Trumpet Section Rehearsal" Class can 'see' them based on matching "Blue Band" and "Trumpet"?

That would give me the list, but it wouldn't be tied to the faculty member teaching the rehearsal. I would like to get rosters for all classes and rehearsals to which a particular faculty member is assigned.

[comment]

it wouldn't be tied to the faculty member teaching the rehearsal

I don't follow you. The faculty member should be linked to the class record. This has nothing to do with the students.

[ktalley]

I would like to treat these rehearsals just like regular classes (because to filemaker they are the same), except be able to enroll multiple students at once into a single class. Currently I have to enroll one student at a time into the class. All classes and rehearsals are in the same table.

Apparently I have a disconnect on what I think is happening and what is actually happening in my join tables and therefore doing a poor job explaining what I would like. I may be making things too complicated.

Here is my table layout:

Students -< Enrollment_Join > - Classes_Rehearsals -< Faculty_Class_Join >- Faculty

Currently, I can select a single student and enroll them into a single class. Then assign a faculty member to that class and get a list of students in the class via the assigned faculty member (or from the student side, list the faculty member's name). I would like to do exactly the same thing except take a set of found students and put them in a single class - or have a script loop through the found set and enroll them.

I'm probably going about this all wrong... Does this help explain a little better?

Thanks for your patience with my inexperience

kt

[comment]

I would like to treat these rehearsals just like regular classes

If so, you probably should go back a bit and examine the process of assigning students to chairs. Clearly, having the chair in the student's record AND in a join table is redundant. Why not create the join record at that point?

[comment]

On second thought, you could also import the student records into the join table. There is no need to

search for "trumpet" and "blue band" and enroll the found students into the "Blue Band Trumpet Section Rehearsal" Class and the "Blue Band Rehearsal" classes then do the same for each instrument and band combination ("trumpet" and "red band", "saxophone" and "blue band", etc.)

since there is exactly one enrollment per student and the required join data is already in the students record.

[ktalley]

I think I follow you. I'll give this a try. Thank you for all your help

kt