5 Replies Latest reply on Mar 7, 2013 8:45 AM by philmodjunk

    Help With A Calculation



      Help With A Calculation


           This is my first time posting to the forum but i have been getting help here for years - so thank you in advance for all of your knowledge.

           I have a database which manages all aspects of a summer camp - from enrollment, to scholarships, to housing requirements, to class scheduling.  We have a total of 15 differents camps, and campers can attend more then one camp.  Each camper gets a personalized schedule of their classes which is created based on a number of factors, including which camp it is, what instrument they play (it's a music camp), how they perform at their audition, and some preference requests which they are allowed to submit.

           I have been using Filemaker for what seems like forever, but everything i know is self-taught or through books (and these forums).  My script and caluculation writing is novice at best, but so far I have been able to cobble together what I need to make the database work pretty well.  Of course, I'm always trying to improve it - and I'm stuck on a task.

           My problem is with the scheduling of a particular course.  We off an elective class where the campers send us their requests for what class they prefer, and we fill those classes on a first-come/first-serve basis.  Each class is attached to a particular camps, and has a different enrollment cap.  Right now, I have a count field which returns the current numer of campers currently placed into that class, and we use that to manually keep track of when we're hitting the cap; but I would like to  be able to run a script which prevents the user from commiting a choice for a class that has reached it's cap.

           The user is viewing this elective choice from a layout based on a table occurance called:  SCHEDULING_CAMPERS with a portal to the table occurance:  SCHEDULING_ENROLLMENTS which contains a field "ElectiveAM".  In the field ElectiveAM,  the user selects the class from a value list that populates choices from an index table called INDEX_ELECTIVES realtive to the camp they are enrolled in.  Each class record in the Index_Electives has a unique ID, and has a field which contains the enrollment cap value.  When a class is selected, the ID number  is recorded in the ElectiveAM field, and this field is ultimately used in a scheduling script which puts it into the campers schedule in the right calss period based on other factors.

           So, to make short story long, is there anything I can do to have the count of campers currently enrolled in the selected class compared with the enrollment cap for that class, and if the value is > the cap, return a custom dialog that says "This class is currently full".

           I hope I have explained this well enough.  HELP!  My brain hurts from trying to figure it out on my own.  Thank you so much!

        • 1. Re: Help With A Calculation

               We need to look carefully at this first:

               Each class is attached to a particular camps, and has a different enrollment cap.

               How do you manage that information? One record for each class for each camp? Or if the same class if offered at two camps, do you have just one record? And if offered at more than one camp, is the cap always the same or might it vary from camp to camp? Can the same class be offered more than once at the same camp? And if so, might the caps be different?

               Once you have a specific place to record the cap for each class, we can look at either a validation calculation or a script trigger driven check that compares your existing count calculation to this cap for a given class at a given camp

          • 2. Re: Help With A Calculation

                 Thank you for your reply.

                 Hopefully this will help:
                 All courses are only used once.  They are only attached to one camp, and each one has a unique ID and unique enrollment cap. If it's offered at another camper there is a separate record and ID for it.

            •           I have a table called Index_Camps and all that it contains is the camp name and camp ID number.  Each camp has a unique ID.
            •           I have a table called Index_Classes which is my master table for all classes.  It contains all details about every class for every camp such as the location, time, class period.  Each class has a unique Course ID and is attached to a Camp ID, so classes do not get re-used from camp to camp.  This is where the Enrollment Cap values live.
            •           This is where you will probably cringe.....
                        Beacuse of my limited brain power, and how I needed to access select certain types of classes when we are creating schedules, I have had to create separate tables that duplicate records in Index_Classes.   These tables each only contain a certain TYPE of class (for example, Small Ensembles Only), and the records exist exactly as they exist in Index_Classes. I know it's not the best system, but it was all I could to to be able to filter down the classes the way I needed for some other functions.  

                 So, in this particular case, we're working with the SchedulingIndex_ElectiveAM table.  This contains records for only the AM Elective classes we offer and it is populated by the same information as the Index_Classes table.

            •           Lastly we have the Scheduling_Enrollments table which contains all of the camp enrollments, and houses the data field for the selection result of the Elective class. 

                 I hope this helps.  I have also included a screenshot of the relationship graph just in case.

                 Thank you for your help.


            • 3. Re: Help With A Calculation

                   Perhaps you should set up one table, ClassCategories where you have one record for each type of class. In other words, one record for LargeEnsemble, one record for AuditionRooms, etc. Each record in that table could match to only those classes that are part of that category.

                   If you are using Filemaker 11 or newer, you can also use a filtered portal that selects for only one class category.

                   Either way or from your existing tables/relationships, I assume that a record in "Scheduling_Enrollments" links one camper to a specific class for which they have signed up. And that the Cap data is accessible via the link to Index_Classes.

                   A calculation field, cEnrollmentCount can be defined as:

                   Count ( Scheduling_Enrollments::ClassID )

                   and defined to evaluate from the "context" of Index_Classes. (There's a drop down in the top of Specify calculation for selecting the table occurrence context.)

                   Then a validation rule can be set up on the ClassID field in Scheduling_Enrollments with this calculation:

                   Index_Classes::cEnrollmentCount < Index_Classes::Cap //test this to see if < or < is the correct operator to use.

                   If you are using FileMaker 11, you can also use the OnObjectValidate script trigger to perform a script that makes the same check. This second option is a bit more user friendly as it catches the issue in time to revert the record if the cap has been exceeded.

                   If you are using Filemaker 9 or 10, you may also be able to manage this with the OnObjectSave script trigger.

                   My preferences in this type of thing is to use both a script and the validation calculation. The validation rule serves as my "insurance policy" that only kicks in if a user somehow manages to circumvent the script trigger.

                   An even better idea that you may want to investigate is to automatically remove the option to select a class once it's cap has been reached. A portal of classes used as a selection list can use a filter to omit all records that have reach their caps. A conditional value list can also omit filled classes, though it will take a script to update a field in Index_classes instead of using the unstored cEnrollmentCount field in order for it to update correctly as classes fill.

              • 4. Re: Help With A Calculation

                     Thank you so much.  I put this into action last night and it worked great. I am using it both as a validation and running OnObjectSave.  Works like a charm and will make so many people happy.  I can't thank you enough.  It's the little things...

                     I'm with you on creating the ClassCategories table, and I did start out with it set up that way, but I am using Filemaker 10 and because I can't filter portals I just can't seem to get it to work the way I need it to with value lists.  But it is something I will continue to explore.

                     Thank you again!

                • 5. Re: Help With A Calculation

                       We filtered portals long before we had FileMaker 11 and the Portal Filter feature. The trick was to set up additional match fields as part of the relationship that did the necessary filtering. In many cases, such relationship based filtering remains the better alternative to the portal filter expression.