11 Replies Latest reply on Apr 17, 2017 11:59 AM by beverly

    Validate requested bookings

    PecCars

      Hi everyone,

       

      I have an app where I record requests for bookings. I need to be able to check if a requested date is already booked. I have read a bit in the community and found that this could be done with self join relationships.

       

      From a technical perspective I have a table called Requests with the following fields:

      - ExpectedArrival

      - ExpectedDeparture

      - Arrival

      - Departure

       

      Booked entries are the records in the same table that have entries in Arrival and Departure.

       

      So, if I resolve this via self join relationships I would need somehow to have any existing Arrival or Departure to be within the ExpectedArrival and the ExpectedDeparture.

       

      Any suggestions how should I do this ?

       

      philmodjunk ?

        • 1. Re: Validate requested bookings
          PecCars

          Maybe just to elaborate a bit:

          - It is clear to me that the self join relationship is Request::ExpectedArrival => Request::Arrival AND Request::ExpectedArrival <= Departure among others.

           

          It is the among others where my questions arise:

          1 - I would need to do a similar test for Request::ExpectedDeparture against existing Arrivals and Departures. Do I create a separate self join relationship for that ? and if yes, how do I combine it at the validation ?

           

          2 - The other interesting point is that in the end if a request is done for a period already booked, I still want to keep that request as Not Booked for statistical purposes. This means, that the self join validation should give an alarm when I enter the date, but it should allow me to continue. Actually, what I need to do, is that if the period is already booked I will trigger an "Already booked" message for the entry.

          • 2. Re: Validate requested bookings
            philmodjunk

            Actually, you need this logic as either a self join relationship or as find criteria:

             

            ExpectedArrival <= Departure  AND

            ExpectedDeparture >= Arrival

             

            Some old posts of mine recommend much more complex criteria to the same end, but discussions by others of a more recent date have suggested the above criteria (or relationship matches) as a simpler means to the same end.

            • 3. Re: Validate requested bookings
              PecCars

              thanks PhilModJunk, that logic simplifies indeed the query. How do I implement the validation ? In the table field I did not find an option to activate that.

              • 4. Re: Validate requested bookings
                philmodjunk

                You have two options, both can start by entering the data for the requested reservation into global fields then you can:

                 

                a) Have a script perform a find using the criteria specified plus any other details such as a room or other facility ID (if needed). If any records are found, the requested reservation has a conflict and cannot be made.

                 

                b) Use a relationship where the global fields (dates, room ID, etc) are match fields into your table of related records. In this case, a simple check for the existence of any related records such as Not Isempty ( Bookings::_pkBookingID ) can be used to check for the existence of a conflicting booking.

                 

                Note that with the data in global fields, you can cancel the request simply by clearing the fields as you have not yet created an actual booking record.

                1 of 1 people found this helpful
                • 5. Re: Validate requested bookings
                  MichaelManousos

                  You can check the demo file from Nightwing Enterprises although its old and you must convert it first I think its easy to understand and implement the logic with one custom function and one value list

                  NightWing Enterprises - Booking System Demo for FileMaker Pro

                  1 of 1 people found this helpful
                  • 6. Re: Validate requested bookings
                    beverly

                    Note, that if you don't have Stuffit Expander, you can get the Zipped (Windows) version and Macintosh can open it just fine.

                    beverly

                    1 of 1 people found this helpful
                    • 7. Re: Validate requested bookings
                      philmodjunk

                      As I recall, and I could be recalling incorrectly, this example file uses the custom function to generate return separated lists of dates from the start to the end of the actual and requested reservations. This is then used in a relationship to check for overlapping reservations.

                       

                      This works and you can use it, but I am just noting that the method used is different from what we have discussed in this thread.

                      1 of 1 people found this helpful
                      • 8. Re: Validate requested bookings
                        PecCars

                        Have been checking the Nightwing Enterprises Demo. Basically it generates numbers from the dates and uses that to identify the overlaps.

                         

                        The ambition will be simplicity, which it seems that philmodjunk's option b: "Use a relationship where the global fields are match fields into your table of related records" could be. I will need to do some finicking to implement the global fields, as I am using now the table fields directly as entry fields and need to be local to the record.

                        • 9. Re: Validate requested bookings
                          MichaelManousos

                          Yes and then uses a value list in a relationship to exclude the booked ones.

                          I just mentioned it as a an alternative method.... you never know!

                          • 10. Re: Validate requested bookings
                            philmodjunk

                            Since what I suggested uses inequalities, there could be a difference in performance between the two methods, so test with realistic numbers of records to see.

                            • 11. Re: Validate requested bookings
                              beverly

                              there is a custom function (just one):

                              NumRange(To;From)

                              Let([Fn = GetAsNumber(From); Tn = GetAsNumber(To)];

                              If(Tn > Fn; Fn & ¶ & NumRange(Fn + 1; Tn); Tn)

                              )

                              beverly