Could anyone point me to a resource (book, web site, etc.) that has information about how to get started with building a SIMPLE calendar / schedule solution ? Or perhaps explain the basic schema ?

I need to build a simple scheduler for a single user that has a bunch of timeslots on each day in which meetings can be booked. I don't need to display anything on a month layout.

I would like to do build a daily view and a week view.

I'm not concerned with conflict-avoidance, as in this case,

the secretary will be eyeballing the schedule before booking the meeting, and WILL be allowed to book a conflicting meeting.

I will, though, want to indicate that there is a conflict.

I have already checked out the existing calendaring solutions that are available. They don't suit my needs and I wasn't able to reverse-engineer them to figure out how they work.

thanks!

Hi cappiels,

Scheduling solutions are fairly complex and require thoughtful planning. The specific structure depends on what your needs are, but here's the basic idea:

A parent table might be records of each person's Day or Week, with a grid of the time slots for that day or week. We want those time slots to actually be related records so that we can remember details about each one and we can do conflict checking with them. Usually when you want to see multiple related records, you'd use a portal, but since we're talking about time slots, where the related records exist only for specific times, I'd use multiple relationships (one for each time slot.)

This brings up a performance trade off: On a slow network, showing lots of relationships on one screen can get pretty slow. You have to weigh your need for granularity of the schedule vs. the performance of it.

Another possibility that can help here is to use repeating fields to display the schedule, but use related records to hold the actual details about each time slot. This requires more scripting to keep everything in sync, but the performance can be greatly improved.

Now back to the time slots. The related records themselves are the Activities. If an Activity spans more than one time slot, then it should show in each time slot it is used in. This is achieved with range relationships. Each time slot's relationship would look something like this:

Day <=> Activity 08AM_09AM =

Day::EmployeeID = Activity 08AM_09AM::EmployeeID

Day::Date = Activity 08AM_09AM::Date

Day::g08AM <= Activity 08AM_09AM::TimeEnd

Day::g09AM >= Activity 08AM_09AM::TimeStart

Now to conflict detection. One possible way is to use separate count() calcs on each time slot. If there's more than one related record for a slot, show a flag. But I might have the data entry process do the checking instead. If the data entry is being done in that Activity table, then it would be a self join relationship that would look something like this:

Activity <=> Activity 2 =

Activity::EmployeeID = Activity 2::EmployeeID

Activity::RecordID <> Activity 2::RecordID

Activity::Date = Activity 2::Date

Activity::TimeStart <= Activity 2::TimeEnd

Activity::TimeEnd >= Activity 2::TimeStart

If there's a related record, then there's already something scheduled in that time range.

Of course, this was the basic explanation. It gets harder if you also want to handle repeating Activities (with all the variations.) :wink2:

Go to www.practicemaker.com and go to the Scheduler page, you can see how someone else did this.