I've kicked this concept around a number of times here in the forum and the contributions of others helped simplify my original suggested solution.
The following method exploits a rather unique feature of FileMaker relationships. If you put a list of values into the same field of the same record, but separated by returns and use this field as a match field in a relationship, you get an "OR" type matching of values to records in the related table. So if you put "A"¶"B"¶"C" into a text field. A record with this list of values would match to related records with the value "A" or the value "B" or the value "C".
So we start with a relationship based on a text field that we will load with a list of dates:
NewReservation::RoomID = Bookings::RoomID AND
NewReservation::DateList = Bookings::Date
If you book the "familyRoom" for 15/02/2015 to 23/02/2015, you'd use a script to create 8 records in Bookings with the same RoomID, but with different dates starting with the first date (Feb 15) and ending with the last date ( Feb 23).
To check for availability for a new reservation, a similar script (or you can use a custom function defined in Filemaker Advanced) to load DateList with a list of dates from Start Date to End Date as well as selecting the correct RoomID in the RoomID field.
Then this test will be true only if no part of the specified dates in DateList do not match to any records in Bookings for the specified RoomID:
IsEmpty ( Bookings::Date ) // any field in Bookings that is never empty can be used to get the same result here
I'm completely confused by that.
If I created a field that displayed all dates from the arrival date to the departure date, and hid this field when a user is viewing the booking form. How would I create that field? Let's name this "Dates in Booking" for my explanation.
Then I could create some sort of validation that if the 'accommodation type' entered on the new booking record = "accommodation type' on a existing booking record AND the arrival date of the new booking record falls into a date contained within the "Dates in Booking" field, to return an error message?
But I don't understand how I would set this validation up if it is possible? As the accommodation type field on the new record and existing record is essentially the same field within a table. I would have to differentiate between the existing records and the new record being created within the validation?
Thanks, Daniel Jenkins
I am describing a relationship between two table occurrences. I am not describing a validation rule.
As I stated in my original post, DateList can be a text field loaded with this list of dates via a script or it can be a calculation field using a custom function. Which would you like? (Creating a custom function requires FileMaker Advanced.)
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
As I don't have FileMaker Advanced I'd have to use a script for the text field
So if you have two DateStart and DateEnd, you might use this script:
If [Not Isempty ( NewReservation::DateStart ) and Not Isempty ( NewReservation::DateEnd ) and
( NewReservation::DateStart < NewReservation::DateEnd ) // date interval is valid ]
Set Variable [$Date ; NewReservation::DateStart ]
Set Field [ NewReservation::DateList ; List ( DateList ; $Date ) // append Value of $Date to list of dates in DateList ]
Set Variable [ $Date ; Value: $Date + 1 ]
Exit Loop if [ $Date > NewReservation::DateEnd ]
Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.
PS: I highly recommend FileMaker Advanced to all people serious about developing good FileMaker databases. The developer tools in it save you a lot of time and frustration figuring out why something isn't working. The custom menus and custom functions, on the other hand allow you to build better, more user friendly designs with techniques not possible if you don't use Advanced to install those features into your solution. (Once you have added such features, anyone that opens the database will regular FileMaker Pro can then use those added features. You just need advanced to install them in your solution.)
I'm completely confused by that, to prevent double bookings it would need to check the date for the accommodation type.
Below is a screenshot of the double bookings form, to create a new booking I would use a blank booking form, not a new reservation.
It would have to check the arrival date is not between the arrival date ... departure date OF existing bookings where the accommodation type of the new record matches the accommodation type of existing bookings.
The fields involved would be the same for existing bookings and new bookings.
"Accommodation type" & "Arrival Date" & "Departure Date"
Currently this is the only system I am creating and I feel that the cost would not outweigh the benefits of filemaker advanced as this system is for a hypothetical client and I am encountering numerous problems as it is.
I'm not quite sure how to clear up your confusion but here goes:
The sole purpose of the script is to generate a list of dates that represent your proposed new reservation. The idea here is that starting from a record in the NewReservations table, you enter data documenting the details of the new reservation. You enter a room type, a date for the start of the reservation and a date for the end of the reservation. The script is then performed to generate a list of dates from the start to the end of this reservation. The system I described--which requires changes to the design of your Bookings table as described in my original post, uses a relationship to match the proposed new reservation against the table of Bookings records. This relationship matches by room type and by date. Due to use the list of dates, this record will match to any record for the same room type in Bookings with a date that matches to any one of the listed Dates in DateList.
I think we have a disconnect here on "room type" that will actually require a change in what I've described, but let's stick with the current design first as it was designed to work with your original specifications in your first post to see if you can understand how it works. We can then discuss how to modify this approach if it turns out to be necessary. Consider the following example.
Customer A books the "family room" for 10/02/2015 to 12/2/2015. That is recorded in Bookings as 3 records:
Customer RoomType Date
A FamilyRoom 10/02/15
A FamilyRoom 11/02/15
A FamilyRoom 12/02/15
Customer B books the "family room" for 15/02/15 to 16/02/15
B FamilyRoom 15/02/15
B FamilyRoom 16/02/15
Customer C then wants to book the Family Room for 12/02/15 to 14/02/05. You set up a record in NewReservation with a RoomType of "FamilyRoom" and DateList is loaded with a List of dates:
3 dates, all in one field.
When you use the relationship that I described to match this record to records in Bookings, it matches to this record in Bookings:
A FamilyRoom 12/02/15
And this tells you that you cannot book the family room for this date range. If the customer were, however, to attempt to book the room for 13/02/15 to 14/02/15, the record in NewReservations would not match to any records in bookings and this would tell you that the FamilyRoom is available for them to book.
Now the "disconnect" here is that this doesn't really work for "room type" unless you only have one room (or accommodation) of each type, this system is really checking individual accommodations for availability instead of a group of accommodations all of the same type. Doing that by this method would require pulling up a list of Accommodations all of the requested type and checking each one in turn by the above method for availability. The above method could be adapted fairly easily to do that and done via a script so that all you need do is click a button and the system either reports back that the reservation is OK or that no accommodations of the specified type are available..
So i'd have to create a new reservations table? alongside the bookings table i currently have.
If i could create a field (which is hidden from the user) on the booking form that displays all the dates within the Arrival date...departure date, calling it datelist
I could make a script that checks the arrival date inputted (for new bookings) against the datelist field for existing bookings that match the accommodation type.
I'm unclear on how I would create a script to enter the datelist automatically & also how I would create a script to check the arrival date when creating a new booking record against the dates in the hidden datelist field when the accommodation types match?
I could set the script to run whenever the user enters an arrival date on a new booking then.