1 2 Previous Next 29 Replies Latest reply on Mar 10, 2017 12:54 AM by lauross

    Room Booking Database



      I was wondering if I could get some help/advice on a room booking database I'm working on.  I'm having serious speed issues with two scripts and I cannot figure out why that is.


      Background:  There are several buildings in my job and each of those has several rooms that are required to be booked for meetings, etc.  Not everyone has access to all buildings so users have persmissions set.  The rooms are booked in half hour increments and note the person who booked the room, the reason and it greys each half hour slot out to show visually that it is unavailable.  I'll provide some screenshots.


      So there are two main tables.  One I just called menu and the other I called room bookings.  The room bookings has a record in it for each half hour slot of each room for the day.  So, if there are two rooms, there would be 20 records for each room in a day to account for each slot, nearly 15,000 reccords for the year.  The script runs a find when they want to book a room, looking for that day/time/room and if the fields associated with a booking are blank, it writes info it it, if it's already booked the user gets a custom message saying it's already booked, find another room, etc.


      Initially all my layouts were based on the menu with relationships built between the two.  I put loads of portals showing only one field from the Room bookings.  It filtered what was showed based on the relationships.  At the bones of it all of this works fine, as it should.  I have a script on a button, when the users enters the date, room, time they want to book, it goes to room bookings table, finds the appropriat records and either books or confirms a clash in bookings.  This script is taking a long time to load, sometimes timing out users.  There are two steps to it, first it loops through all the records it needs for the booking and does a check to see if it's already booked, if it is, there is a text it saves.  Then once this loop is completed it has two options:  If it's already booked tells them, does nothing more.  If it's not, in then finds the records again via loop, and sets the appropriate fields.  Screenshots attached again.  Clearly there is something in my scripts that is upsetting the process, but I can't see what.  Maybe I'm looking at it way too long.


      It's of note that some of the fields were originally global fields, all working off the one menu record.  I thought this may be an issue so on the login script it creates a new record in menu and they are no longer global fields.  Each user is now just amending the record they created on menu.


      Can anyone see any problems?  Or am I going about this all wrong?  Is there a different/better/faster way?  Thank you SO much.

        • 1. Re: Room Booking Database

          Hi Annette.  Here's a few thoughts that might point you in the right direction.

          Getting stuck with FileMaker taking a long time to go through the script or timing out suggests a problem with the loop.  But I don't think you need to be doing any looping at all.


          1.  If you have a StartTime, a Room, a Date and a Location, when you do a find shouldn't you end up with 1 record?  Try manually doing a FileMaker find (not via a script) to see if that is the case.


          2.  If you find 1 record, then you don't need to do any looping in your script.  Either the record that is found is already booked - in which case you advise the user it is booked - or it is not booked, in which case you set the required fields in that record.  No looping required.


          3.  If you find more than 1 record then there is a problem isn't there because by your definition "The room bookings has a record in it for each half hour slot of each room for the day."  Then you might need to check that your records are correct.


          Hope this helps.  Chris

          • 2. Re: Room Booking Database

            hi Chris, thanks for your reply.  The reason I have the loop is while you are correct there is only one record for each time slot, if someone needs a room for an Extended period of time, say an hour and a half it has to find three of them.  Does that make sense?

            • 3. Re: Room Booking Database

              Hi Annette - yes that does make sense.  Further thoughts.


              As you are finding more than 1 record, (e.g. a 2 hour booking should find 4 half hour slot records) then your script should look more like this:


              Screen Shot 2016-01-19 at 11.10.07 PM.png

              You should only need to do 1 find - then test to see if any of the records have a booking already, and if not then loop through the records and set the fields to make the booking.


              Do you have FMPAdvanced?  I strongly suggest you should use the Script Debugger in FMPA and step through every script you create before trying it out, but at the very least any script with a loop should definitely be tested in debugger first.  I can't tell you how many times this has saved me and my users from getting into endless loops.


              HTH.  Chris

              • 4. Re: Room Booking Database

                I would completely reconsider the whole structure of your solution.


                In a nutshell, you should only have real bookings ( a tuple made of RoomID, bookerID, purpose, Date, startTime + number of slots, in your case the slot length is defined as 30 min) and play around that, using repeating fields for visualizing the situation of a specific room / day combination. You use ExecuteSQL to get the data from the bookings and fill the repeating fields accordingly.

                • 5. Re: Room Booking Database

                  Thanks for your suggestion.....does FM11 allow ExecuteSQL?  I didn't think it did.  We are in the process of trying to get staff systems upgraded to FM14 but this solution was needed in the meantime.  Also....I have no experience with SQL so I guess that's a new learning curve for me. 

                  • 6. Re: Room Booking Database

                    You don't necessarily need ExecuteSQL ( ), although it would certainly be helpful. +1 to the suggestion to refactor the solution to a different data structure. I would consider another table where each record is a single booking record (time slot), and then you can use simpler Find requests to locate the desired records (ex: to find a range of times, search on a range of times rather than having to loop over the fields).

                    • 7. Re: Room Booking Database

                      First point in case: working out a good solution under time constraints is the death of quality. Take your time and build something which will stand the winds of time, which bring data and more data and more users and end up slowing you down to a point of user frustration, to say the minimum.


                      Second point: behind your need lies a very common problem, the agenda/calendar problem. Many people are interested about the topic, about how to implement it well, so spending time helping you will be useful to many. Mike is of course right when pointing out that ExecuteSQL is not necessary, but it's something you can get to know in a couple of days by reading some docs and playing a bit in Data Viewer (FM Pro Advanced needed), and a new learning curve from time to time is what we pushed ourselves out of caverns, bringing us where we are now: look at it as an opportunity !


                      I will post ASAP a demo of how you can fill a repeating field with data from a booking table, stay tuned. Hopefully others will chime in, too.

                      • 8. Re: Room Booking Database

                        Hello Annette and others


                        I have done such a job for reservations and have only one record for a user (IDName, Start_Day, End_Day, Comment)


                        I can see this king of screen :



                        All labels are in French but easy to guess. A script with a loop build a board fill with "1 or "0", then cell is orange when 1 and green when 0. There is more than two colors to make difference between current customs, sales agent, stay in pension or half pension. All can be imagine. Calculation for a period of 14 days takes less than 5 seconds for 50 rooms, calendar can slide in past or in future.

                        Shortly, I have only 3 tables, one for description of rooms, the other for each réservation at the hôtel the last one for paiement. I can throw to the software of invoicing which is independent but uses the base of reservation.


                        I think you want to get something like that.


                        If I could use the slip with mouse or trackpad in FM, I would have improved.

                        • 9. Re: Room Booking Database

                          Thanks.  Unfortunately this is if they require a room for an entire day, which would not be the case for what I need.  I'm looking for booking rooms in time slots of 1/2 hour increments.

                          • 10. Re: Room Booking Database

                            I agree on all your points.  I think many will find this useful and I do look forward to this being a learning opportunity.  My goal is to be very proficient in designing/using various methods within FM...I still have a LONG way to go but I'm enjoying the learning process.  I look forward to seeing your example !  And as always...thanks so much for all the input.

                            • 11. Re: Room Booking Database

                              Annette, my purpose was to show you that something as you want can be done with Filemaker.

                              Imagine that instead of days, each column represents ½ hour and that instead of 14 columns, one by day, there is 20 columns for 10 hours.


                              You can also turn the board.

                              • 12. Re: Room Booking Database

                                Apologies, thank you.  I'll look at it further and see what I can customize.  I know there is ways to do this with FM, it's just learning to think outside the box of what I have experience with I think.

                                • 13. Re: Room Booking Database

                                  Hi Anette, I just saw your title and couldn't help but think of my own Bookings and Reservations systm I made for a Udemy Filemaker course.

                                  I don't know if you are having big structural problems with your file or not, but if you want some ideas you could look at this:

                                  Filemaker Bookings and Reservation System - YouTube


                                  Greetings Guy

                                  • 14. Re: Room Booking Database

                                    OK, here is a first draft of how it can be done, no SQL.

                                    1 2 Previous Next