FILEMAKER SCRIPT HELP

This is a tough one. Scheduling functions are always complex.

If you have been considering upgrading to FileMaker 7, this might be a good time to go
ahead. This is a lot easier to accomplish in version 7 because you can create relationships
based on greater-than and less-than tests, while older versions are limited to only A=B tests.
A couple of self-join relationships can show you if there are any records that overlap the
time frame in question.

It's still possible to do this with older versions, just not as neatly. There are a few
ways. Which is best for you depends on your specific needs and your skill level.
What is the typical event like? What's the maximum length of an event? If you can give
me an idea what the typical scheduled events are like, I can help you
figure out the best approach for your situation.

Here's an overview of the options...

Method 1: the brute force approach. Basically, you use a looping script to scan
through all the records, testing each one for overlap with the new record you're adding,
and return a flag in a global field to indicate whether a match was found. Fairly simple
but slow if your database is really big. You can do some optimizations to make it faster,
but usually this isn't the best choice.

The second method is to create a multi-line key which contains all the possible values in
between the two ranges, and use this as the basis for a relationship. For example, suppose you
wanted to find any values between 10 and 50. You could make the key field for the relationship
contain all the values from 10 to 50, each on a separate line,
like this:
10
11
12
...
49
50

This approach works well and is fairly fast, as long as the range of possible values isn't too
big. Date and time fields are really nothing but number values, so this technique can work with
them as well. A date field is really just the number of days since 01/01/0001, and a time field
is really just the number of seconds since midnight.

Here is a free plugin for FileMaker that automatically generates multi-line keys for this
purpose. It includes an example database to demonstrate the technique.
http://www.geocities.com/siliconvalley/network/9327/plPLUGs.html

The third approach is called "smart ranges." It's basically a more sophisticated version of the
second method. Instead of filling in all possible values in the range, you create a key which
contains a series  of optimized values that are functionally equivalent to the entire range. It's
faster and more efficient than the second method, but also more complicated to set up.

Here's a technical description of the smart ranges technique.
http://www.onegasoft.com/tools/smartranges/

You can do things with the design of your fields to optimize these approaches. For example,
when scheduling event times, you can require that they be scheduled in 15 minute intervals.
Then you can store the event start time as a count of fifteen-minute intervals. Use calculated
fields and scripts to convert this value back into calendar dates and times on-the-fly.
This way, a one-hour event is represented by just four sequential numbers, instead of 3600.

Let me know if you would like more specifics about any of these.

I'm very interested in getting more information on the "smart ranges" approach.  

I have no problem requiring that they be scheduled in 15 minute intervals but I would prefer 5 minute intervals. Additionally, many users will be creating records.  I'm storing the UserID on a field.  So, I will also have to include this key as part of the checking process.  The sample database now looks like this:

UserID: A38283
event_name: Experts Exchange
start_date: 8/10/2005
start_time: 10:00 am
end_date: 8/10/2005
end_time: 11:00 am

I'm looking at the technical description of the smart ranges technique, but frankly I need all the help I can get.

Thanks