1 2 3 Previous Next 32 Replies Latest reply on Nov 13, 2012 10:58 PM by NicolayFlaaten

    Finding a conflicting resource within a date Range

    yaldir

      Title

      Finding a conflicting resource within a date Range

      Post

      I like to be able to book a "room" within a start and end date. however, I may have other reservations falling into the same date range.  If I have booked a resource previously "RM100"  from 2.21.2011 - 2.24.2011 and now when creating another reservation and enter From Date: 2.21.2011 and ending date 2.22. 2011 or From Date: 2.21.2011 - 2.23.2011 I should be able to get a warning that there is a conflicting reservation for this resourec "RM100".  Searches starting from 2.21.2011 all the way upto 2.23.2011 should warn me. 2.24.2011 would be OK since normally Resource will become available on its last booked day.

      I have tried couple of suggestions and created a script using a find with no luck.  Suggested solutins can only find if the new and existing dates are exact match. In other words, If I select 2.21.2011 and end date 2.24.2011 which is the previously entered item, I get a warning, but nothing happens if I key 2.21.2011 - 2.23.2011.  I guess, during a new scheduling entry, I need to be able to find the duration between the two dates, Start and end date.  Then, take the start date and adding 1 to it until I reach the duration and at that time quit.  During this increment for each day, I should check to see if there is any "RM100" or and Start day = Incremented day AND Resources are equal?

      I am not sure if explained this clearly but any help would be appreciated.

      best regards

      alp

        • 1. Re: Finding a conflicting resource within a date Range
          philmodjunk

          This can be handled with a multiple request Find. I used to think you could two this with two requests, but now conclude that you need a third to find all possible records with conflicting dates for your specified room.

          If the start date or end date of the proposed reservation overlaps any start, end date ranges for the specified room, you have a conflict. These can be found with two requests. But now I realize that you also need to check for cases where the proposed start date falls before the start date of an existing reservation and the proposed end date falls after the end date of this proposed reservation. That requires a third request.

          Say you enter the start date, end date and room number into three global fields: gStartDate, gEndDate, gRoomID

          Then this script can check for all reservation records that conflict:

          Enter Find Mode[] //clear pause check box
          #Check to see if Start date falls in date range of any reservation records
          Set Field [Reservations::RoomID ; YourTable::gRoomID ]
          Set Field [Reservations::StartDate ; "< " & YourTable::gStartDate]
          Set Field [Reservations::EndDate ; "> " & YourTable::gStartDate]
          #Check to see if End date falls in date range of any reservation records
          New Record/Request
          Set Field [Reservations::RoomID ; YourTable::gRoomID ]
          Set Field [Reservations::StartDate ; "< " & YourTable::gEndDate]
          Set Field [Reservations::EndDate ; "> " & YourTable::gEndDate]
          #Check to see if new reservation enloses an existing date range
          New Record/Request
          Set Field [Reservations::RoomID ; YourTable::gRoomID ]
          Set Field [Reservations::StartDate ; "> " & YourTable::gStartDate]
          Set Field [Reservations::EndDate ; "< " & YourTable::gEndDate]
          Set Error Capture [on]
          Perform Find[]
          If [ Get ( FoundCount ) > 0 /* you have a conflicting reservation */]
             // do what you need to do when there is a conflict
          Else
            // book the new reservation
          End If

          • 2. Re: Finding a conflicting resource within a date Range
            yaldir

            Sorry but does not work...:( I need a solid simple approach to this issue where it should be bullet proof. Althoguh I am new to FM, there must be a

            typical file read where you can compare each record within a criteria etc.

            • 3. Re: Finding a conflicting resource within a date Range
              philmodjunk

              Here is a demo file. Using this file, can you find a case where the script fails?

              If so, let me know how it fails and I'll take another swing at it.

              http://www.4shared.com/file/eAi4f485/CheckReservations.html

              • 4. Re: Finding a conflicting resource within a date Range
                yaldir

                Phil,

                Thanks for all your help. As I indicated earlier, I wanted to come up with a simple yet bullet proof way of finding the conflict since users will solely be relaying on its finding conflicts.  After digging into old logic's of mine used under different platforms, I realized that if i can find a way to go through each slice of the Beginning and ending dates and compare each date withing this date range including room, I should be OK. So, I created a loop to go through each date until the duration is reached (end date - beg date) I would quit. During the loop, I am doing a find by keep incrementing the $date variable. This way, I don't even need the end date since I quit when loop counter is >= to $duration. This works and it is solid. 

                Thank you again, Like I said to some of my colic's that regardless of the tool, platform, you can either "delete, Change, or Add" records...:)

                warmest regards

                alp

                • 5. Re: Finding a conflicting resource within a date Range
                  philmodjunk

                  The example I gave should be "bullet proof" and will accomplish what you describe without looping through records--something that might result in significant delays in some cases when there are a lot of reservation records to check.

                  Can you give me a specific example of how it might fail to be "bullet proof"?

                  I wouldn't allow changes to dates and rooms to be made directly to the reservation table BTW, I'd use scripts and a different layout so that the users select the reservation for editing, specify the changed information in the same global fields I have in the example file and then a similar script to what the demo uses would be used to check availability before making the actual changes to the selected reservation record.

                  • 6. Re: Finding a conflicting resource within a date Range
                    Sorbsbuster

                    yaldir - I think we all like a bullet-proof idea.  But I'm not sure how the looping idea will work when someone mis-keys and types the start date as 1/3/2011 and the end date as 5/3/3011.  Or maybe erroneously starting 1/3/2011 and ending 2/3/2010.  Phil's suggestion looks very clean to me and I'll find a use for it I'm sure: I'd be interested to hear where you see the weakness.

                    • 7. Re: Finding a conflicting resource within a date Range
                      yaldir

                      Phil,

                      Below example shows where it failed.  First, I have a room 1004 with these gstartDate and g Enddates dates.  I simply keyed 1002 and gave me an conflicting message but when I OK the msg, it went a way and created a new reservation not only creating a new reservation but also changed the existing reservations’ room number to 1002 which was 1004. 

                      In your example,  I had a room reserved for 2/21/2011 - 2/24/2011 with the room # 1004.

                      I them wanted to create a reservation knowing that wouldn't have conflict and keeping the same dates, changed the room to 1002 got a conflicting error? and when OK'ed it, it went ahead created a new reservation with 1002 but also changed the room number of the existing record to 1002?

                      When it comes to looping issue against quite a few reservation records as indicated by Sorbbuster, I am already checking to see if the criteria dates entered are OK like "unusual duration length, duration cannot be neg, etc...

                      Of course loop does the same find request and would be as fast as or as slow as any other solution.

                      Phile try it and see If I am missing something.

                      thanks

                      • 8. Re: Finding a conflicting resource within a date Range
                        philmodjunk

                        What fields did you use to enter the data for a new reservation? If it presents a message telling you that there is a conflict, it does not create the new record, it leave all fields and records unchanged.

                        You would enter the proposed dates and room numbers in the global fields in the header, then click the button to check for conflicts. It only creates a new reservation if there is no conflict. It does, however, list all reservation records that conflict with the proposed room and date range so that you can see why there is a conflict.

                        Trying to recreate what you describe, here's what I did:

                        Leaving the records I uploaded in place, I entered the following values into the global fields in the header:

                        gStartDate: 2/21/2011
                        gEndDate: 2/24/2011
                        gRoom: 1004

                        I click Check Res and get a message that there was no conflict and it creates the reservation record.

                        I then changed gRoom to 1002 and clicked the button again. Again, I get a message that there is no conflict and a new reservation record is created. The previous record for room 1004 is not visible as it isn't part of the current found set at this point. If I choose Show All records, however, I find that I have both records for different rooms but for the same date range.

                        I think the fact that the only visible records are either the records that conflict or the newly created record has created a false impression on your part that the script changed the record's room number from 1004 to 1002 when it actually created a completely new record with the same date but different room number while omitting the previous reservation records from view.

                        The scripts, can, BTW, be modified to so that a different group of records are visible when the script finishes. That simply requires adding a script step or several to the end of the script to find and sort the group of records you'd find useful to see at that point. You could see all records for the same room starting with 10 days prior to start and going to 10 days after the end date if you wanted and the newly created reservation record could be highlighted, just to give one possible example.

                        • 9. Re: Finding a conflicting resource within a date Range
                          yaldir

                          Phil,

                          After numerous testing, I found that your solution is solid! Works every time.

                          Thank you so much for all your help.

                          alp

                          • 10. Re: Finding a conflicting resource within a date Range
                            NicolayFlaaten

                            I have a quite similar problem that I want to solve, but instead of finding the rooms that have confilct with my reservation, I want to find the rooms that is available. I have tried to modify the search described by PhilModjunk, but I cant figure out how to set up a similar search that finds the rooms that is available in the wanted date-range. Any idea how to define this search?

                            Regards Nicolay

                            • 11. Re: Finding a conflicting resource within a date Range
                              philmodjunk

                              That type of search definitely makes more sense for a hotel. I came up with this approach:

                              First define another table, Rooms with just a RoomID and RoomNumber field. Define this relationship:

                              Rooms::RoomID = Reservations::RoomID

                              Then this script will find the available rooms for a given date range:

                              #Finds all rooms that aren't available and then lists those that are available by showing the omitted records.
                              Enter Find Mode [ ]
                              #Check to see if gStartDate falls with in start and end dates
                              Set Field [ Reservations::StartDate; " ≤ " & Reservations::gStartDate ]
                              Set Field [ Reservations::EndDate; " ≥ " & Reservations::gStartDate ]
                              New Record/Request
                              #Check to see if gEndDate falls with in start and end dates
                              Set Field [ Reservations::StartDate; " ≤ " & Reservations::gEndDate ]
                              Set Field [ Reservations::EndDate; " ≥ " & Reservations::gEndDate ]
                              New Record/Request
                              #Check to see if gStartDate...gEndDate encloses a reservation record's date range.
                              Set Field [ Reservations::StartDate; " ≥ " & Reservations::gStartDate ]
                              Set Field [ Reservations::EndDate; " ≤ " & Reservations::gEndDate ]
                              New Record/Request
                              Set Error Capture [ On ]
                              Perform Find [ ]
                              Go to Related Record [ From table: “Rooms”; Using layout: “Rooms” (Rooms) ] [ Show only related records; Match found set ]
                              If [ Get ( LayoutName ) ≠ "Rooms" /* All rooms are open on this date range */ ]
                                     Go to Layout [ “Rooms” (Rooms) ]
                                     Show All Records
                              Else
                                     Show Omitted Only
                                     If [ Get ( FoundCount ) = 0 /* no available rooms */ ]
                                             Show Custom Dialog [ Message: "No rooms are available for dates from " & Reservations::gStartDate & " to " &
                                                                             Reservations::gEndDate ]
                                     End If
                              End If

                              • 12. Re: Finding a conflicting resource within a date Range
                                yaldir

                                Phil,

                                I was able to get it work as per your last example concerning finding available rooms.  However, I would like this to be a Value List so that it can be selected once displayed. How do you accomplish this? Also, I will now only check available rooms but additional criteria for availability of the rooms.

                                Kind regards

                                alp

                                • 13. Re: Finding a conflicting resource within a date Range
                                  philmodjunk

                                  Populating a value list will be tricky. You could capture the RoomID's of all the found records, make them into a list with each value separted by a return to use in a relationship for a conditional value list or a portal serving as a selection list box (click a row set up as a button to select a room), but it would be simpler to pop up the list of found records in a floating window where you see the rooms listed and you click a button or the fields set up as a button to select the room. That last approach avoids the scripting and delay in updating the list.

                                  Specifying additional criteria can be done as a follow on find that Uses Constrain Found Set instead of Perform Find to reduce the list of available rooms.

                                  • 14. Re: Finding a conflicting resource within a date Range
                                    yaldir

                                    I understand, modified the layout, added a selection button... I am not sure (don't know how) how to display a small window displaying the available rooms. I like to keep the original reservation selection screen (with global fields) and pop up a smal window on top somewhere so that they could see both screen at the same time.  tried couple of things, none of them worked ?

                                    1 2 3 Previous Next