each year our dept has auditions for prospective students. as each audition date approaches, students are constantly added to the schedule and the order changes (due to cancellations, students needing a specific time, etc.). our scheduler (not me, luckily) has been tediously changing the audition time of each student using EXCEL. there's got to be an easier way. i thought i would try to help her, but i need some help myself. if you need more info, please let me know.

And your question was....?

-bd

oops. how can i use filemaker to automatically update audition times?

it would be to mostly close the gaps when someone cancels. there are also situations which call for rearranging times as well, and other times when a student must be added in, which would mean bumping down anyone scheduled to audition after that student. it gets messy.

i meant to answer how the process begins manually. i spoke with our scheduler. she begins the schedule chronologically, i.e., the first student to request an audition has the first audition time, and so forth, although there is another catch. we separate graduate auditions from undergraduate auditions (the graduate students audition before the ugs)

There are a lot of ways that you could alter the schedule. For example, do you want to move all the audition times up in order to remove gaps? Or, do you just want to find where the gaps are so that new appointments can be entered into the gaps? There are a lot of possibilities here. How do you do it manually?

Well, the possibilities range from crudely simple to very sophisticated. I'll go for simple here.

Create a field called "Order". You can set it up as an auto-enter serial number. Whenever a new appointment record is added, the order field will get the next higher available number assigned to it.

Create a scheduling script that sorts by the order field, and then assigns audition times in sequence. Suppose that you start at 9:00am and go to 4:00pm, and each audition is a half hour (1800 seconds). Set a global field "gTime" to 9:00, and a global field "gDate" to the date of the first audition. Looping from record to record, set the audition time in the record to the value of gTime and the audition date to gDate, then add 1800 to gTime. If gTime>16:00:00 then reset it to 9:00:00 and add one to gDate.

Whenever you run this script it will automatically close up the gaps. You can get fancy using "If" statements to skip certain times like lunch hour.

If you need to delete an audition, simply delete the record, and re-run the script.

If you need to switch two appointments, simply exchange the values in the order fields of the two audition records, and then re-run the script.

If you need to insert an audition before another, create the new record and then change the order field to a value halfway between the two records you want to insert between (if you want to insert between two times that have order=25 and 26, then set the order of the new record to 25.5) and then run the script.

You can automate these individual schedule changing actions with scripts and buttons, but this should give you something to start with.

Also, you should have an additional two fields for previous date and previous time that you set equal to the current audition date and time at the beginning of the scheduling script. Then after it is run, you can compare fields to see which ones have had the date or time changed so that the person affected can be advised.

thanks for your help, Bob. i'll give it a whirl and let you know how it goes.

Stephen Wray