10 Replies Latest reply on Apr 19, 2012 11:12 AM by MadMacs

    Appointment Scheduler

    MadMacs

      Title

      Appointment Scheduler

      Post

      I have an appointments table which includes the following fields:

      Appt_ID: (unique ID auto assigned by the table)

      Holder: (employee name or ID)*

      Site: (location)*

      Date: (date of meeting)*

      Time: (time of meeting)*

      Client_ID: (client identifier or name)

      Etc…

      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. 

        • 1. Re: Appointment Scheduler
          philmodjunk

          Do you know how to create a looping script?

          Do you know how to use variables in that script?

          A looping script that uses data in variables and/or global fields can be used to generate a set of records for this. It may also be useful to have a "schedule" table with fields for Schedule_Name, Site and Holder and a ScheduleSlots table with one record for each date and time (which can be done with separate fields or a single time stamp field.)

          The time interval might be best stored in an employees table. That makes for these relationships:

          Employees::EmployeeID = Schedule::Holder
          Schedule::ScheduleID = ScheduleSlots::ScheduleID

          You'd create a new record in Schedule, selecting a date in a global field, gScheduleDate, specify start and end times (which can have auto-entered default values) and selecting an Employee in Holder. Then you'd perform this script:

          Set variable [$SchedID ; value: Schedule::ScheduleID ]
          Set Variable [$Time ; value: Schedule::Start ]
          Go To Layout [ScheduleSlots]
          Loop
             New Record/Request
             Set Field [ScheduleSlots::ScheduleID ; $SchedID ]
             Set Field [ScheduleSlots::Date ; Schedule::gScheduleDate]
             Set Field [ScheduleSlots::Time ; $Time ]
             Commit Record
             Set Variable [$Time ; value: $Time + Employees::Interval ]
             Exit Loop If [$Time > Schedule::End // loop will be infinite if Interval is zero or empty for related employee record ]
          End Loop
          Go to layout [original layout]

          • 2. Re: Appointment Scheduler
            MadMacs

            PhilModJunk, many thanks. No I have not used looping scripts or variables but your solution worked brilliantly! Perhaps I'm pressing my luck here but I would like to create a summary portal that would show dates, holders names and start and end times. The idea being to navigate quickly to a particular holder and then display a page specific to the date showing each of the appointment slots created. Is there an easy way to filter the ScheduleSlots table to achieve this or would I need to create a new summary table that holds this data and would be populated as another script to the one you suggested to create the ScheduleSlots? I hope this makes sense. 

            • 3. Re: Appointment Scheduler
              philmodjunk

              On an Employees layout, a portal to ScheduleSlots would list all slots for all dates for that employee record--close but not quite what you want here.

              You currently have this set of relationships:

              Employees---<Schedules-----<ScheduleSlots     (---< means one to many)

              In Manage | Database | relationships, make a new table occurrence of Schedules by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as SchedSlotsSchedule.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Add it to your relationships like this:

              Employees---<Schedules-----<ScheduleSlots>-----SchedSlotsSchedule

              SchedSlotsSchedule::ScheduleID = ScheduleSlots::ScheduleID

              Modify the relationship between Employees and schedules to be:

              Employees::EmployeeID = Schedule::Holder AND
              Employees::gSelectedDate X Schedule::Date

              gSelectedDate will be a date field with global storage enabled in field options. Using the cartesian join operator will keep this from affecting your current relationships and will enable a portal filter on your portal to update. Now add this filter expression to the portal to ScheduleSlots:

              Employees::gSelectedDate = SchedSlotsSchedule::Date

              You can put gSelectedDate on your employees layout formatted with a drop down calendar and you can then select different dates in it to see that employee's schedule for that day.

              Note: including the gSelectedDate field in the relationship with the cartesian join operater (X) like this forces the portal to update automatically each time you change the date in this field.

              • 4. Re: Appointment Scheduler
                MadMacs

                Many thanks PhilModJunk. I dont think I explained myself very well but your suggestion fits with the third step I envisaged. The second step, after creating the Schedule per your original advice, would be to have an Appointments Page/portal showing current sessions available giving a summary as below. By clicking on a row it would jump to step 3 which you describe above. So what I'm looking at is a page with a portal that looks something like this:

                Day/Date

                Session Name

                Employee

                Start

                End

                Free

                Wed 18/04/2012

                LondonFinance Clinic

                John Farraday

                08:10

                14:30

                1

                Wed 18/04/2012

                LondonFinancial Planning

                Leah Jordan

                14:00

                18:00

                0

                Thu 19/04/2012

                Croydon Family Finances

                Leah Jordan

                18:00

                20:00

                0

                Mon 23/04/2012

                BirminghamFamily Finances

                John Farraday

                10:00

                17:00

                2

                Tue 24/04/2012

                Birmingam Tax Planning

                Paul Robbins

                10:00

                17:00

                1

                Tue 24/04/2012

                London Retirement Planning

                Martin Childs

                09:00

                12:00

                0

                Tue 24/04/2012

                London Family Finances

                Leah Jordan

                12:00

                15:00

                2

                The free field would display the number of free appointment slots so at a glance you can see that while Family Finances is fully booked for Croydon on 19/04/2012 we could offer the same session in nearby London on 24/04/2012. If the customer wants to book it, clicking on the row would go to the layout you suggest above containing the employee appointment slots for that day. Sorry I didn't make that clear but your idea to use SchedSlotsSchedule table addresses this last stage very neatly. The question is how to generate this summary and that was why I questioned whether I needed a Summary table that would be populated when running the looping script or whether it would be possible to filter the ScheduleSlots table to do this instead?

                • 5. Re: Appointment Scheduler
                  philmodjunk

                  I'm not sure here....

                  My original concept was that you created a new record each time you booked an appointment. But now there are additional complicating details:

                  1) You can schedule the same appointment for more than one person

                  2) Each slot has a capacity permitting a certain number of people to "enroll" in that appointment.

                  3) You want to know how many more people can attend a given appointment

                  So far, all we've done is assign employees to time slots. Are you looking to enroll multiple employees in the same appointment or do you have one employee (the "holder") and then multiple clients who schedule to attend that appointment?

                  • 6. Re: Appointment Scheduler
                    MadMacs

                    PhilModJunk, the idea is to create a series of appointment slots in advance so that client appointments can be pre-booked with individual employees, some of whom may be working on the same day either with identical or overlapping hours. So no, the appointment slot would only ever be for just one client/employee at a time. Your original suggestion of creating a Schedule Table (to define session name, date, holder, start and end times) works perfectly and populates the ScheduleSlots table with employees, dates and individual appointment times. Individual sessions can be displayed in a portal which is an ideal way for the employees to see what appointments have been fixed for them. So your above suggestions work exactly the way I want them to.

                    The bit that's missing is a way of displaying all available "clinics" so that the admin person booking appointments can instantly see what clinics are running on which dates with the employee concerned. So if one clinic is full, another appointment could be offered perhaps on a different date/venue with same or another employess. If you like, the view I'm after is purely a summary of the day, date, session name (clinic name), holder (employee), start and end times as above but not the specific time slots. Free slots would be a count of the number of empty (unbooked or empty Client_ID fields) appointment slots within the time range for each of the clinics. In this way the person taking the booking can instantly see clinic availability where there's free slots available. I would set it up so that by clicking on a portal row the related employee layout (your suggested SchedSlotsSchedule portal) would show all appointment slots for the day in question so a client can be booked. Ideally it would be good to filter this summary information from the ScheduleSlots table but I'm not sure whether this is possible. The alternative idea I had would be to add something to your original script that would copy summary data to a Summary_Table instead. This table would then be used to display a summary of the clinics available sorted/filtered in date order. Hope this clarifies.

                    • 7. Re: Appointment Scheduler
                      philmodjunk

                      It looks like you need a list of all appointment slots from a specified date (Today, tomorrow, or ?) on through either all that exist or up to a specified date range?

                      That sound right?

                      And each slot should list the number of free slots for the same date range and clinic ID.

                      Let me know if that is what you have in mind. The date range criteria I am describing is something you'd use to exclude slots that are in the past, have too little lead time (Too close to the present date) to be possible or that are too far in the future to be considered.

                      And such a list could be filtered to drop out all slots that are not still open. (This is not shown in your example as you have appointments where "Free" shows a zero...)

                      • 8. Re: Appointment Scheduler
                        MadMacs

                        Almost right, except that the portal I'm seeking to create and order is sessions as opposed to individual free time slots. I have created a visual of what I'm after and attached this. Its Layout 1 that I want to create. Layout 2 already exists thanks to your initial advice about setting up Schedule and ScheduleSlots table. The way I envisage it working is this, the person booking the appointment will do so from Layout 1. In this example I have selected Leah as the session holder whose clinic I want to look at - this shows that on Thursday 19/4/12 she has one Free appointment left that can be booked. Clicking on the clinic date field will take us to Layout 2 - the session in question, so that her free appointment can be booked. If the date/time does not suit the client but they only want to meet Leah, then clicking her name field in Layout 1 would take us to layout 4 which in effect is a filtered subset of Layout 1 for Leah. Layout 3 is really just another way of accessing individual session views useful when creating and managing individual employee sessions. Layouts 2, 3 & 4 I think are probably fairly straightforward to construct. Its how to automatically create the data entry to populate the portal in Layout 1 simultaneously with setting up the session using the first script you suggested? 

                        • 9. Re: Appointment Scheduler
                          philmodjunk

                          Almost right, except that the portal I'm seeking to create and order is sessions as opposed to individual free time slots.

                          There appears to be a disconnect between this sentence and the examples you are showing. That's why I stated this the way I did in my last post, to hopefully clear this up. If you only want to list sessions, how can it be that your examples all list an employee, start and end time?

                          If you only want to list sessions, I would think that there would be no employee, start or end time, just dates and clinic names in the list of sessions.

                          • 10. Re: Appointment Scheduler
                            MadMacs

                            Thanks for your patience. Layout 1 portal is purely to provide summary information on clincs available and to act as a means of navigating to an appropriate layout. This will be used by an admin person speaking to clients over the phone so ease of identifying the right employee, date and time quickly is important. The reason why an employee name is required is because not all employees are qualified to run the same sessions added to which clients may also have a preference for a particular adviser who they may have seen before. So Paul does tax but not financial planning while Leah and John don't do tax but both can provide Family Finance services. Showing start and finish times is also important because clients are offered morning, afternoon, lunctime and evening sessions and always ask what times clinics start and end so they can decide which is most convenient for them. As these times vary from day to day/person to person having this info listed saves going in and out of layouts to get this info. Equally having all available appointments listed in a single portal could lead to incorrect bookings. In a sense this portal is rather like a website page of links that allow you to navigate quickly to other layouts to book appointments or look at individual sessions; obviously it will need to update daily to show current and upcoming clincs, though the ability to scroll back to see past ones would be very useful. I hoped a simple bit of additional scripting would enable me to use the data entered in the Schedule layout (to create the ScheduleSlots) to generate the summary into perhaps a separate table thus avoiding duplicated activity and the risk of incorrect data entry.