I have an appointments table which includes the following fields:
Appt_ID: (unique ID auto assigned by the table)
Holder: (employee name or ID)*
Date: (date of meeting)*
Time: (time of meeting)*
Client_ID: (client identifier or name)
By using different layouts and filtered portals I want to be able to show appointment slots filtered so that current and future appointment slots both booked and available by site, by date by employee can be viewed.
Different employees have different lengths of appointments but they remain the same in each each session, sessions last between 4-6 hours. I would like to automate the process of creating record entries into the Appointments_Table so I don’t have to manually create a record for each appointment time on a particular date. So a session starting at 08:10 with a last appointment at 14:30 with each appointment lasting 20 minutes would require 20 records to be created where the only difference is the incremental increase in time by 20 mins, i.e, 08:10, 08:30, 08:50 and so on until 14:30 - the Site, Holder, Date would all remain the same.
As not all employees have the same time allotted, some are 15 minutes, others 10 and not all work the same hours or even days, I thought about setting up a schedule table to define the parameters. And wondered whether these parameters could then be used in a script or series of scripts to create the new records with the incremental times in the Appointments_Table?
The table I have in mind, we’ll call it Schedule_Table would have the following fields:
Schedule_Name: eg “Wednesday London Finance Clinic” text name to identify
Site: eg “London” selected from predefined location picking list*
Holder: eg “John Farraday” selected from predefined employee picking list *
Date: eg “11/04/2012” selected from calendar*
Start: eg “08:10” time field
End: eg “14:30” time field (this would be time of last appointment)
Interval: eg “20” minutes i.e., length of appointment
Slots: “20” (calculated from 6hrs 20m – end time minus start time)
This would allow a large number of standard schedules to be created from which entries into the Appointments_table could be made but the question is how would I script this to create the necessary records and field entries marked with * above in the Appointments_Table?
Any suggestions would be most appreciated.