room booking db - checking for clashes

[kisscass]

My department recently purchased a room booking database for classroom scheduling from a developer. However he neglected to tell us that the database doesn't prevent double-booking, and he wants to charge us an exorbitant amount to make the database do this. I'd like to sort this out myself if possible but it seems to be quite a difficult process. I've had a look through similar posts but I'm not sure which solution is best.

Classes can be booked at 15 min intervals between 9am-6pm. They're usually booked for up to 2 hours at a time, but bookings can be for more or less time. The booking fields are Activity, Date, TimeStart, TimeEnd and Room. What I would like to do is input the required date, time and room into the record then have a script which would check against any other records occuring within that time and alert me to any clashes.

My guess is that I'd need to set a few globals and use a calculation, but the rest alludes me. I'd really appreciate some help to sort out this issue.

Thanks,

PJ

[Fenton]

I'd say that developer is not very up to date. This is not hard to do, even in version 6, using calculated keys. It's easier in 7; you don't really need calculated keys, because you can have more than 1 criteria in a relationship. So you've got the RoomID, a Date. a TimeStart and a TimeEnd.

What you're trying to match is a conflicting record, via a relationship. It will have to match 2 criteria flat out:

Date

Room

Then it needs to use > or < than to see if it falls with the same time. Remember that the "end" time is actually 15 min before TimeEnd (create a field, = TimeEnd - 900). Because a slot ending on one TimeEnd will not conflict whose TimeStart is that same time.

I've done this; but I'd have to go seriously look to find mine. Here is a quote from a post by our own RalphL, which sums up the situation:

There are 3 posible conditions for an overlap. Using A & B as the 2 time periods being compared.

Case( StartA >= StartB and StartA <= EndB, 1,

EndA >= StartB and EndA <= EndB, 1,

StartA <=StartB and EndA >= EndB, 1,

0)

You just need to add those conditions as criteria to your conflict testing relationship.

[kisscass]

Thanks so much Fenton!

I'll have a go and let you know how I get on.

PJ

[Fenton]

Another little enhancement. When I've done this I put the user onto a special little window, with only global fields. The relationship is build using the global fields to the regular fields. The advantage is that you can stop them, and show them the conflicting record, before they've actually entered any data into real fields. Makes it very easy to stop.

I also put them into a Loop/Pause state, with Allow User Abort ["Off"]. Which means they can't click out of the layout until they make a decision. Buttons on the layout need to have the Halt or Exit step as the option (you get that choice when you attach a script to a button). PUT A HALT BUTTON BEFORE YOU RUN THE LOOP. Or you may have to Force-Quit FileMaker, which is very bad for your clean development file.

To control the interface while trapping them in a Loop, you also have to trap in all visible scripts, so they can't run. There is a new step in 7, Get (AllowAbortState), which is normally 0, but is 1 if it's on and active. Halt visible scripts if it's 1. You'd normally never start such a script while Abort was on anyway.

This may sound like overkill. But sometimes telling people something is wrong is not sufficient to stop them, especially if they can just leave the data as is and go somewhere else.

[Reed]

Here is my lab management database. There is a scheduling portion which might be what you're looking for. There's a lot of other stuff in here, but look specifically in the relationships graph at the brown TOG. The day_view layout is where the schedule entry is done... it's set up to work with IWP so it's a little different than other data entry screens in the file.

Comments/Questions are welcomed....except for on the color schemes etc. I'm no graphic designer, I'm a chemist dammit!

account: root

password: root

mslabback.fp7.zip

[kisscass]

Thanks for your help.

Can I use a similar calculation to clash-check all the existing bookings that I have made? I'm pretty sure that there are already a few clashes in there!

PJ

[Reed]

In my file, there is a field called conflict in the schedule table. I have record validation set up to check for this, but you could also search preexisting records by searching on this field.

[kisscass]

Reed,

Your database is just what I needed. I tried to emulate your schedule conflict system after converting the files to FM7, but it didn't work fully. There were other major problems in the database so I think the conversion process may not have worked totally.

I copied your conflict field into this database and tried to do a search to find existing conflicts, but I wasn't quite sure how it worked. Would I just search on all records that have a "1" in this field?

Thanks,

PJ

[Ender]

You can check to see if there are already records accupying the desired time slot before adding new records by making the relationship between the entry globals and the Start, End, Date, and Room fields in the self join. This will be a little cleaner than Reed's method of creating and then deleting the record if there is a conflict.

By this method, a conflict error message could show without having to run a script:

Case(not isempty(self_range_rel::RecordID), "Conflict")

The relationship looks like this:

gStartTime <= End Time

AND gEndTime >= Start Time

AND gDate = Date

AND gRoom = Room

where the fields starting with g are the global data entry fields. You would still need a script to populate a new record with the global values.

There is an example of this type of relationship in my SmartRangesCF demo in the Custom Function forum.

You'll note in my example file, the relationship is between calcs that use a placeholder start or end date in case one of the fields are empty. This allows for a open ended range. I'm not sure in your case if this is appropriate.

Hey Reed, what's with your Logout button quitting FMP? I had other things open.

[Reed]

Hey Ender,

The Logout button is on a page designed for IWP... the exit application script step is used in IWP to close the session explicitly... otherwise the session stays open until the server-defined timeout.

I like the idea of checking the conflict right from the globals, I was going to do that at some point before I implemented the IWP scheduling part. But don't you still need to check for more than just the one conflict case? In my file you have to check 4 situations where the new record could conflict with existing ones....

gstart <= start

gend >= end

room = room

gstart >= start

gend <= end

room = room

gstart <= start

gend > start

room = room

gtart < end

gend >= end

room = room

This way you catch it whether the end of the new schedule overlaps the beginning of existing ones

or

the beginning of the new schedule overlaps with the end of exisiting ones

or

the new schedule completely encloses an existing schedule

or

the new schedule is completely enclosed by an existing schedule

I used timestamps instead of dates and times because it seemed easier to write the relationships, esp. if a schedule stretched across a date change.

Sorry about the quit button

[Reed]

kisscass,

Note that you can't just paste the conflict field calc in and use the result. You have to set up the relationships too... maybe you already knew this but I just wanted to make sure.

Dana

[Ender]

I think this relationship works correctly for range-to-range relationships:

gStartTime <= End Time

AND gEndTime >= Start Time

If we look at each possible overlap, it seems to cover all the possibilities. When there is no overlap, the relationship fails to match. Take a look at the attached diagram and see if there is an error in my thinking.

[Reed]

That's pretty slick... all along I'd been writing a single relationship for each possible conflict case rather than one catch-all. One thing though... if you use <= and >= won't it be seen as a conflict if the end time for a new reservation equals the the start time for an existing one or vice-versa? That usually wouldn't be considered a conflict. If you change the operators to < and >, then that is fixed but then the situation where start or end times are equal is not caught as a conflict.

I just don't know if it can all be done with one relationship. Unless you add gstart != end and gend !=start. Would that fix it all? My brain feels like mush this morning.....