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.
"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.
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?
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.
That works like a charm!
Thank you again for all of the help!