Double booking based on date and time

[sirmatter]

I am currently updating my database with some new features and have come across an issue that I cannot seem to solve. I am attempting to redesign how my database books rooms for our customers. I currently have 100 rooms and have unlimited customers.  I am attempting to design this system to prevent Double Bookings during the same date and time as another customer. My old system just used check boxes for "booking rooms" which required me to look at all customers during that time frame manually.

This is my current relationship including the Join Table. I have left out fields that do not have anything to do with this project.

Customer

• Customer ID
• Name
• Booking ID

Booking ID

• Booking ID
• Room ID
• Start Date
• End Date
• Arrival Time
• Departure Time

Rooms

• Room ID
• 100 Room Fields (I may be doing this wrong they may need to be added as records in this table. I just can't remember which is right)

Sorry for the newbie questions but it has been years since I have worked in Filemaker and I have forgot a majority of what I use to do. I have searched the web for hours trying to find an answer and I have reviewed similar examples but have not been able to get it working.

Thank you!

[LaRetta]

Hi there Sirmatter!  Welcome to FMForums!  :-)

Customers would not have a booking id - only information about the customer.

Rooms would have a unique RoomID (auto-enter serial or UUID) as RECORDS and not fields and should only contain details about the Rooms (single, double, non-smoking etc).

Bookings table would then have its own unique BookingID (auto-enter serial) and also hold a CustomerID and RoomID as well as the booking date/s.

I can't find the posts right now so I'm attaching a few booking samples presented by Comment which should move you forward quite well; he has presented several of them for us.  If you have further questions, let us know!

 

1154021903-DoubleBooking.fp7.zip

1209148774-BookingSimple.fp7.zip

1287752710-ViewBookings.zip

1278507838-BookingSimple.zip

[sirmatter]

Thank you La Retta,

I will give it a try and let you know the outcome.

[comment]

Just a note or two:

1. You should definitely have an individual record for each Room in the Rooms table. Unless you have absolutely nothing to record about a room, other than its unique number (that will never change). In such (rare) case, you could do with just a custom value list of the room numbers. In no scenario would 100 fields make sense.

2. You have many Customers per one Booking?

[sirmatter]

Thank you Comment,

1. I kind of thought so but I couldn't remember and I had a hard time finding a similar solution.

2. I can have about 400 people book in one group for the rooms. Some rooms have space for up to 10 people. In some cases though, I can have 1 customer book all the rooms for a weekend and then provide them to their customers. This also makes this a difficult scenario.

[sirmatter]

By the way, I work for a Non-profit and this is why we can't afford to purchase something. I am stuck working on it myself and doing my best.

[Lee Smith]

This is how some of the best got their start.

[LaRetta]

That thread you reference, Lee, is probably the best (and funniest and saddest) post ever.  Thanks for bringing it up again!! :-)

[Lee Smith]

I know.

Every time I hear a new member say, “I’m new to FileMaker and we have no budget, but I need to create this complex database, so it’s up to me” this priceless post by comment comes to mind.

Edited April 21, 2015 by Lee Smith

[sirmatter]

Thank you everyone for the help!

I was able to get the rooms worked out using Comments files. Thank you for providing those La Retta!

Does anyone have a suggestion about a fast selection of rooms? I have about 100 rooms that can be selected and there may be times when all 100 rooms are booked by one person.

To be clear... Each room selection would create a new record in bookings for that room. Right now I have to manually create a new record for every room through my portal in Customers

Here is my final relationship...

Customer

• Customer ID
• Name

Booking ID                                      BookingConflict

• Booking ID       Not Equal        Booking ID
• Room ID           =                     Room ID
• StartDate          >=                   EndDate
• EndDate           <=                   StartDate
• ArrivalTime
• DepartureTime

Rooms

• Room ID
• Room
• Type

[sirmatter]

Hi Everyone,

This is the continuation from another fantastic answer from you all. This is similar to another post I did but a little different. 

http://fmforums.com/forums/topic/95686-room-double-booking/#comment-438428

I have set up my Rooms using the instructions from Comment and La Retta. This has worked great except for one problem. I need to incorporate time into the mix. I have tried a variety of ways to do this and have not been able to get it to work.

I have a StartTime and EndTime field in my bookings database. When I add ArrivalTime and DepartureTime fields to the relationship between Bookings and the TO ConflictBookings, it only functions some of the time. It will show a double booking sometimes but not others. I am sure I am missing something.

Bookings                                        BookingConflict

• Booking ID       Not Equal        Booking ID
• Room ID           =                     Room ID
• StartDate          >=                   EndDate
• EndDate           <=                   StartDate
• ArrivalTime       <=                  DepartureTime  
• DepartureTime  >=                 ArrivalTime 

Any help is much appreciated!

 

[yilbber]

You created this relationship wrong.

You need Two Instances, One instance will Show All of the start, and another will show all the end.

They will be self joined for exampling Booking , Booking Arrival, booking Depart

You will need to convert these to timestamps to make it easier. (getastimestamp (blah blah blah).

Then just use a script with this as it's IF evaluation (if (count (booking arrival::TimeStampArrive => 2 or Timestampdepart =2> ...

You should probably add it as a Script step trigger on commit. You Might want a 3rd instance showing all the bookings to test against for new records (such as (Taken). Which will have arrive and depart.

 

 

Edited June 2, 2015 by yilbber

[eos]

I think you could calculate StartDate & ArrivalTime, and EndDate & DepartureTime, respectively, into timestamp fields, then replace the two date predicates in the relationship with

Booking::cArrivalTS >= Booking_conflict::cDepartureTS
Booking::cDepartureTS <= Booking_conflict::cArrivalTS

and, of course, remove the two time predicates you've added.

Edited June 2, 2015 by eos

[comment]

I have a StartTime and EndTime field in my bookings database. When I add ArrivalTime and DepartureTime fields to the relationship between Bookings and the TO ConflictBookings, it only functions some of the time.

​Assuming ArrivalTime occurs on StartDate and DepartureTime occurs on EndDate, define two calculation fields:

cStartTimestamp = Timestamp ( StartDate ; ArrivalTime )

cEndTimestamp = Timestamp ( EndDate ; DepartureTime )

(result type is Timestamp. obviously), then change your relationship to:

Bookings::Booking ID ≠ BookingConflict::Booking ID
AND
Bookings::Room ID = BookingConflict::Room ID
AND
Bookings::cStartTimestamp ≥ BookingConflict::cEndTimestamp
AND
Bookings::cEndTimestamp ≤ BookingConflict::cStartTimestamp

 

Edited June 2, 2015 by comment

[eos]

You created this relationship wrong.

You need Two Instances, One instance will Show All of the start, and another will show all the end.​

I also think you may want to read the referenced thread to understand what this is about.

[sirmatter]

It worked like a charm. Thank you EOS and Comment. You guys Rock!