4 Replies Latest reply on Oct 11, 2013 9:47 AM by GordBrown

    Searching for intersecting date ranges



      Searching for intersecting date ranges



           I am hoping that I might be able to ask for some assistance.  I'm fairly new to filemaker (so far, so good) and am working on a small booking database for a local not for profit.  I'm a mainframe developer by trade, so I spend most of my time working with JCL and REXX, and I find I'm having a little trouble shifting gears into the filemaker scripting language. 

           My problem is, I'm working on a setup to allow rooms to be booked (hotel or B&B type of thing). I've got most of it working fine, but what I would really like to do is to have a search criteria so that when a clerk is taking a booking, they can enter the "Check In" and "Check out" dates, and there would be a button that they could press beside this to display any bookings which occur in that time frame.  I've got the search results layout set up, and am using it quite successfully with other searches, just this one with multiple criteria is messing with me. 

           Logically I know exactly what I am trying to do......

           I need to take 4 values: 

           the check in date that was just entered (proposed checkin)

           the check out date that was just entered (proposed checkout)

           Existing check in dates from other bookings (check in)

           Existing check out dates from other bookings (check out)

           And I need to perform the following calculation to determine if a record should be displayed in the search form:

      proposed checkin <= checkout and checkin <= proposed checkout

           If this statement is true, then the booking intersects and should be displayed. I just cannot, for the life of me seem to make this work in a script or calculation. 

           Could anyone suggest anything to assist me? I really appreciate any help!




        • 1. Re: Searching for intersecting date ranges

               Actually, the date ranges can intersect in more ways than you may have realized at this point

               If your proposed date range is in: 9/1/2013, out: 9/5/2013

               All of the following ranges intersect:

               8/31/2013...9/3/2013    Check out date is after 9/1 but before 9/5
               9/2/2013...9/8/2013      Check in date is between in and out dates
               8/31/2013...9/20/2013  Check in date is before 9/1, check out is after 9/5

               Having worked with several others on this idea, what works best here is a relationship between the proposed dates and the existing reservation dates using a special feature of how lists of values work when used as match fields in a relationsihp

               A script or recursive custom function (requires FileMaker Advanced to define and install in the database) can take the in and out dates and produce a list of dates in a date field. In our proposed dates example that would produce:


               all in a single text field. The same can be done with the in and out dates in your reservation records. Let's call these two fields ProposedDatesList and ResDatesList

               Then you can define a relationship like this:

               Table::ProposedDatesList = Reservations::ResDatesList

               If this is for a specific cabin or room at your facility include an ID field for that specific location:

               Table::ProposedDatesList = Reservations::ResDatesList AND
               Table::PropRoomID = Reservations::RoomID

               If you get any related records in Reservations via this relationship, you have intersecting date ranges for this room and thus the new reservation cannot be made without changing either the dates or the location.

               This works because when you have a return separated list of values in a match field, you get a link to a related record if any one of the listed values matches. Since we have lists in both fields, you get a match if any one listed date on one side matches to any one listed date on the other.

               Additional notes:

               "Table" in this example could be a different table occurrence of reservations or not. It should be the table occurrence specified for the layout where you are handling new reservations.

               With either executeSQL or a calculation using the List function, it's possible to pull up a list of rooms that are available for the specified date range instead of using trial and error to select a room that is available.


          • 2. Re: Searching for intersecting date ranges


                 Thank you for that detailed answer!  The idea of creating a seperate date list for each sounds like the way to go. 

                 I do have Filemaker advanced, is this a specific predefined function within the function list?


            • 3. Re: Searching for intersecting date ranges

                   It's a custom function that you have to add to your database file. That's why you need FileMaker Advanced.

                   Here's a function I devised for that purpose in one of my solutions:

                   //DateRangeList ( DateStart ; DateEnd )
                   //Returns a list of return separated dates from DateStart to DateEnd
                   //DateStart  :   Date--first date of list
                   //DateEnd    :   Date--last date of list
                   Case ( IsEmpty (DateStart ) ; "" ;
                              IsEmpty ( DateEnd ) ; DateStart ;
                              DateStart > DateEnd ; DateStart ;
                              DateStart = DateEnd ; DateEnd ;
                              List ( DateStart ; DateRangeList ( DateStart + 1 ; DateEnd ) )

                   You can copy and paste this directly into the function editor though you still have to enter DateRangeList as the function name and specify DateStart and DateEnd as parameters for it.

              • 4. Re: Searching for intersecting date ranges

                     That works like a charm!  

                     Thank you again for all of the help!