Week At A Glance Scheduling

by Jack Wheeler

Does this sound familiar to you? I have seen so many BIG companies that will repeat the same daily tasks three or four times, once for each software package they are playing with. With Alpha Five and a little creative programming, it's possible to centralize data and eliminate redundant work.

If you look closely you will find that EVERY office does the same things at some point of the day, month, year etc. Therefore, if you can automate and schedule these items you would be eliminating MOST of the repetitive time consuming tasks, thus saving time and money.

With the invention by Gary Smith and Michael Pesach, Alpha Five has a calendar utility. I have been exceptionally fond of Mr. Smith’s Utility and therefore you will note that this scheduling program uses most of his tables and variables.

Design Requirements

There are some basics I needed for a calendar and scheduler:

Items Required:

1. A form to show seven days of the week with times from 7:00am to 8:00pm
2. A calendar to cycle through days, weeks, months and years
3. A form to show 30 days of the month with appointments as they are set
4. Show a form with Day at a glance
5. A table to keep history of all appointments linked to a contact manager module
6. The ability to schedule dates with:
   1. Daily tasks
   2. Weekly tasks
   3. Monthly tasks
   4. Custom tasks (occurring the 1st and 15^th of every month)
7. Print a calendar sheet as a reference sheet
8. Speed - it can't be too slow to use

Mr. Smith designed a utility that uses two tables an appointment table and a VIP table. In his schema you can schedule appointments as you wish. When you try to save them, the appointment is compared to the VIP table to see if this DATE exist. If it does then the user is asked if they are working this day.

Using the two tables works fast and easily for Gary Smith's calendar. However, I had great difficulties trying to massage his tables into a format to get a week at a glance. Ultimately I settled on a series of subforms. Now that I had this figured out I simply had to design one table with every time available and every date available for the 42 possible days available on the calendar. (Editor's note: A given month can have portions of up to 6 weeks in it, so a calendar has to be able to display 42 days.) As I started to develop this table I noticed I would have 48 time factors for 42 dates for a total of 48*42=2016 fields! Not only was this ridiculous and cumbersome but Alpha Five would not allow all the fields to be visible at one time. I found the limit to be just over 1000 - something like 1024 or so. I really couldn't fault Alpha Five - I knew in my heart that my design was clumsy.

I then came up with the idea of designing a separate table for each day of the week. This seemed to be the ticket and what I came up with is this:

1. Time values every 30 minutes, labeled T1 – T48
2. Rating the importance of appointments as Low, Medium and High, and labeled A,B,C 1-48
3. Note fields labeled N1 through N48

Here is what the form looks like:

Figure 1. The Calendar subform for one day (Friday).

Once the first form was properly done and formatted I would be able to copy and paste it to each of the other 6 tables separately. Each of the 48 fields has a color equation to indicate the type and priority of the appointment. See the first article in this series for an explanation of the color equations!

Figure 2. Each field has a color equation.

I would like to be able to say these forms just flowed when I sat down to design them, but the reality is that when I started this project I was not too organized in my thought patterns and had to redo these forms over and over for months! There are just so many variables to deal with here. The general concept, however, seemed flawless: every Time slot had an Appointment Level and Note value associated with it. Since every field was labeled in sequence 1 through 48 I could effectively use Loops to fetch and find records. For example, 9:30 Am = slot 19, and once I found this number value I could find all the other fields. This also means that you can have many appointments for the N19 field. I wrote a single function to retrieve the slot number from the time:

function TimeName as C(time as C)
 dim timename as c
 dim value as c
 dim time as c
select
 case time="12:00am".or.time="12:00 am"
 value="1" 
 case time="12:30am".or.time="12:30 am"
 value="2" 
 case time="1:00am".or.time="1:00 am"
 value="3"
 case time="1:30am".or.time="1:30 am"
 value="4"
 case time="2:00am".or.time="2:00 am"
 value="5"
 case time="2:30am".or.time="2:30 am"
 value="6" 
 case time="3:00am".or.time="3:00 am"
 value="7" 
 case time="3:30am".or.time="3:30 am"
 value="8"
 case time="4:00am".or.time="4:00 am"
 value="9"
 case time="4:30am".or.time="4:30 am"
 value="10" 
 case time="5:00am".or.time="5:00 am"
 value="11" 
 case time="5:30am".or.time="5:30 am"
 value="12"
 case time="6:00am".or.time="6:00 am"
 value="13" 
 case time="6:30am".or.time="6:30 am"
 value="14"
 case time="7:00am".or.time="7:00 am"
 value="15"
 case time="7:30am".or.time="7:30 am"
 value="16"
 case time="8:00am".or.time="8:00 am"
 value="17"
 case time="8:30am".or.time="8:30 am"
 value="18" 
 case time="9:00am".or.time="9:00 am"
 value="19"
 case time="9:30am".or.time="9:30 am"
 value="20" 
 case time="10:00am".or.time="10:00 am"
 value="21"
 case time="10:30am".or.time="10:30 am"
 value="22" 
 case time="11:00am".or.time="11:00 am"
 value="23"
 case time="11:30am".or.time="11:30 am"
 value="24" 
 case time="12:00pm".or.time="12:00 pm"
 value="25" 
 case time="12:30pm".or.time="12:30 pm"
 value="26" 
 case time="1:00pm".or.time="1:00 pm"
 value="27"
 case time="1:30pm".or.time="1:30 pm"
 value="28"
 case time="2:00pm".or.time="2:00 pm"
 value="29"
 case time="2:30pm".or.time="2:30 pm"
 value="30" 
 case time="3:00pm".or.time="3:00 pm"
 value="31" 
 case time="3:30pm".or.time="3:30 pm"
 value="32"
 case time="4:00pm".or.time="4:00 pm"
 value="33"
 case time="4:30pm".or.time="4:30 pm"
 value="34" 
 case time="5:00pm".or.time="5:00 pm"
 value="35" 
 case time="5:30pm".or.time="5:30 pm"
 value="36"
 case time="6:00pm".or.time="6:00 pm"
 value="37" 
 case time="6:30pm".or.time="6:30 pm"
 value="38"
 case time="7:00pm".or.time="7:00 pm"
 value="39"
 case time="7:30pm".or.time="7:30 pm"
 value="40"
 case time="8:00pm".or.time="8:00 pm"
 value="41"
 case time="8:30pm".or.time="8:30 pm"
 value="42" 
 case time="9:00pm".or.time="9:00 pm"
 value="43"
 case time="9:30pm".or.time="9:30 pm"
 value="T44" 
 case time="10:00pm".or.time="10:00 pm"
 value="45"
 case time="10:30pm".or.time="10:30 pm"
 value="46" 
 case time="11:00pm".or.time="11:00 pm"
 value="47"
 case time="11:30pm".or.time="11:30 pm"
 value="48" 
end select 
TimeName=value  
end function

Script 1. My TimeName() function.

function timename as C(time as C)
timename=alltrim(str(toseconds(time)/1800)+1))
end function

These daily subforms were made read-only. If you enter the note field a script calls up the Main Scheduling form in which editing is allowed:

Figure 3. The OnArrive code for each Note field calls up a data entry form.

And here is the final set structure I employed:

Figure 4. Set structure for the scheduler.

At this point I have decided not to include the scripts on each form based on each date value. I will be including a download of all the tables and forms after the next article. Right now I just hope that the concept can help you in your designs.

The tables are related like this:

• Popupcalendar: Holds only one record and it is the DATE holder that links all tables.
• Scheduler is Monday table
• Scheduler1 through Scheduler6 is Tuesday through Sunday
• Scheduler_Main is the holder of all appointments.

Month At A Glance

Now that I had come this far, the Week at a glance design was simple: it only had to have the subform with the Seven Days, the calendar utility to change the date freely, and a browse with all the appointments for the particular day in question.

But the Month at a Glance was another thing: How was I going to show every appointment for every time on the form? I read Jeff Moses' article with the service bays and found a little script called Schedule_fill. BINGO! That was what I needed. I simply designed a form like the Calendar Utility except I made list boxes for each variable within the day of the month. What a gift this was!

Figure 5. Month At A Glance.

 This started to look like something now. I then printed up a Week At a Glance from Symantics' Act and studied it. It has three months on top: the previous month, the current month and the following month. I must say that I was not too fond of keeping all my appointments on computer, but I tried it out and found this not only easy and efficient to have on hand, but dependable and accurate for arranging my days. Boy, I am always surprised by how much we still do manually!

Here is the design for the Week At A Glance:

 Figure 6. Week At A Glance form in Design mode.

And this is the form in View mode:

Figure 7. Week At A Glance form.

I had some real problems with the form in Figure 7. If I had days that were in the preceding or the following month in the week's view, I could not them to print. After struggling for a while I simply eliminated those dates altogether.

This is how the Main form looks now:

Figure 8. Completed calendar.

Now I am up to the hard part - scheduling the actual appointments. That will be covered in the next article!

2/5/00

Don't forget, we need your feedback to make this site better!

Return to home