12 Replies Latest reply on Nov 28, 2011 1:34 PM by philmodjunk

    Filter portal based on count of records in data range

    NorbertKowalski

      Title

      Filter portal based on count of records in data range

      Post

      I am building a system for booking of rooms.

      My database layout (simplified for this problem) is:

      Table rooms

      id_room - unique, auto generated 
      id_type - type of room, linked to room_types [
      other_data] - number, floor, etc
      

      Table room_types

      id_type - unique, auto generated
      beds - how many beds are in the room
      [other_data] - misc info
      

      Table reservations

      id_reservation - unique, auto generated
      id_room - links to rooms::id_room
      id_person - links to people::id_person (table not related to current issue)
      date_from - when does the person checks in; sets global field globals::date_from
      date_to - when does the person checks out sets global field globals::date_to
      


      What I am trying to achive, is to have a portal (I think it is a best solution here) on "create reservation" layout where, after selecting reservations::date_from and reservations::date_to the user would get a list of rooms that have at least one free bed in given time range.

      The filter would be something along the lines of

      if (
        count reservations::id_reservation > room_type::beds
        (
          globals::date_from =< reservations::date_from =< globals::date_to
          or
          globals::date_from =< reservations::date_to =< globals::date_to
        )
      ) return false

      Meaning, find any reservations already made, see if the one I am creating starts or ends within the existing one. If there are more matching reservation that there are beds, do not show this room on list.

      In theory, all is peachy. I just cannot get the filtering script to pass the syntax checks.

      I am using global fields, since the system will be deployed using instant web publish, and I forsee having to do the reservation proces in two steps, since there will be no dynamic portal filtering on web.

      Once again, I would be gratefull for any help or hints anyone could provide. I am using FM pro adv 11.

        • 1. Re: Filter portal based on count of records in data range
          philmodjunk

          Reservations can overlap date ranges in three ways:

          Start date of new reservation falls on start to end date date range

          End date falls on this same date range.

          Start date of new reservation precedes reservation start date and End date of new reservation comes after end date existing reservation date.

          Thus, you may need to craft a more sophisticated portal filter.

          I can't really parse your example filter expression and have it make sense to me.

          Are you looking for a list of available rooms in this portal?

          Is the criteria that the room be a room with at least as many beds as needed for the new reservation and which is not reserved for the date range of the new reservation?

          • 2. Re: Filter portal based on count of records in data range
            NorbertKowalski

            I'll try to be more descriptive.

            Each room has a declared number of beds, meaning max number of people who can be assigned to that room at any given date.

            On creating new reservation, I want to have a portal, that shows the rooms, that at have at least one free bed for the time the reservation is created.

            So, for each room, it should count all the reservations for that room, where the existing reservation overlaps the one we create 

            ( so 

             new_reservation_start is between existing_reservation_start and existin_reservation_end 

             or

             new_reservation_end is between existing_reservation_start and existin_reservation_end

            )

            count the matching results, and if the count is equal or greater than the number of beds the room should not be visible in the portal.

             

            I'll try to post an example of data and the results I am hoping to get.

             

            Table room types:

            id_type: 1

            beds: 2

             

            id_type: 2

            bed: 1

             

             

            Table rooms:

            id_room: 1

            id_type: 1

             

            id_room: 2

            id_type: 2

             

            id_room: 3

            id_type: 2

             

             

            Table reservations:

            id_reservation: 1

            id_room: 1

            date_from: 2011-11-20

            date_to: 2011-11-24

             

            id_reservation: 2

            id_room: 1

            date_from: 2011-11-20

            date_to: 2011-11-22

             

            id_reservation: 3

            id_room: 2

            date_from: 2011-11-20

            date_to: 2011-11-24

             

             

            Test case 1:

            Setting reservation for from 2011-11-26 to 2011-11-28

            Portal shows

            room id_room 1

            room id_room 2

            room id_room 3

             

            Test case 2:

            Setting reservation for from 2011-11-20 to 2011-11-28

            Portal shows

            room id_room 3

             

            Test case 1:

            Setting reservation for from 2011-11-23 to 2011-11-23

            Portal shows

            room id_room 1

            room id_room 3

            • 3. Re: Filter portal based on count of records in data range
              philmodjunk

              Its your description of this part:

              I want to have a portal, that shows the rooms, that at have at least one free bed for the time the reservation is created.

              I think that's just imprecise language but it reads like you are renting out individual beds instead of rooms. (Bed 1 is occupied but bed 2 in the same room is free so list this room....)

              Unless this is a hostel or dormitory, that seems unlikely so I am trying to get confirmation on what you need.

              You indicate you want a list of available rooms but your examples refer to matching reservations that overlap. I read this that you want to see a list of rooms that DO NOT have any reservvations that over lap the specified date range.

              Seems like a new reservation would have three items of information, start date, end date and number of beds, which can be entered as global fields. Then, when an available room is selected in the portal, the data from the global fields can be combined with the Room ID of the selected room to create a new reservation record. (This can also be used to refine room selection per guest specified criteria. "Room 123 has a jacuzzi bath it's $235 a night instead of $135, would you like to reserve it?")

              That make sense?

              • 4. Re: Filter portal based on count of records in data range
                NorbertKowalski

                "I think that's just imprecise language but it reads like you are renting out individual beds instead of rooms. (Bed 1 is occupied but bed 2 in the same room is free so list this room....)"

                Yes, this is exactly what I am doing. Single reservation is always for single bed only, and there can be more than one person booked for given room, if there is more than one bed in it.

                I am doing a team based reservations, within a pool of rooms, and each person from the team is given a separate reservation for multi-bedded rooms. People from the team arrive and depart at different times, and there can be more people in the team overall than there are rooms (case of more people than rooms showing at once is not part of issue on my end).

                The overlaping reservations were the part of the pseudocode for filtering which rooms should not be listed. I was hoping to count the overlaping reservations and compare them to the number of beds. If the number of reservations is equal to number of beds, the room would not be listed for selection.

                If I was booking whole rooms, than the filtering would be borderline trival. However, it is the exactly the possibility of having reservations for given date and still having one spot open. which is giving me problems.

                • 5. Re: Filter portal based on count of records in data range
                  philmodjunk

                  Ok. That opens a whole other question about whether a bed is available if the other beds in a room are reserved by a different team, but I'll leave that issue alone for now...

                  Reproducing the apparent relationships that you have:

                  RoomTypes---<Rooms----<Reservations

                  RoomTypes::id_type = Rooms::id_type

                  Rooms::id_room = Reservations::id_room

                  What I don't see here is a field in your reservations table that indicates how many beds of the specified room have been reserved by that record. Perhaps you have a "numbRooms" field that is part of that field. Otherwise, I don't see any way to determine if a given room does or does not have any available beds.

                  • 6. Re: Filter portal based on count of records in data range
                    NorbertKowalski

                    Yes, the issues of overbooking (as in sending more people at once that there are spots open), and mixing of teams are not part of my problem at the moment.

                    There is no field for number_of_beds_reserved, since as I mentioned, every reservation is for single bed. Therefore, the number of used bedes for given date is equal to number of reservations that are active on given date. And that brings me back to the problem outlined in the first post, namely "how to count records that fall into given date range, compare the count result to rooms::number_of_beds_from_room_type, and use the result to filter the portal records".

                    Sorry if my description of the problem is not as clear as it should be, but english is not my primary language.

                    • 7. Re: Filter portal based on count of records in data range
                      philmodjunk

                      Apologies for mis-reading your post. I've not perceived the reservation table correctly until, hopefully, now.

                      I also think that I misunderstood the purpose of the portal. I thought you wanted a list of rooms available, but this sounds like you want a list of rooms that already have a reservation overlapping the one in question but have at least one bed available.

                      I've not seen it that way as I've not been able to fully imagine why such a list would be useful.

                      To count bed reservations for a given room for a given date range is not simple as we've found out. I've been posting to another thread here where the user has a similar need for renting out equipment without double booking the same equipment:  Equipment QTY Available for a Given Date Range

                      LaRetta made an interesting suggestion here and I want to investigate that suggestion in light of what we need here in a test file before I go any further.

                      The issues that make this "messy" are:

                      THere are three tests you have to make to see if a given reservation overlaps a specified date range.

                      When you count the overlapping records, care must be taken to keep from counting the same record more than once.

                      The resulting relationships can become very complex and this can create screen refresh issues.

                      • 8. Re: Filter portal based on count of records in data range
                        philmodjunk

                        After some testing, I believe I have been able to get this to work. See if the portal on the ResCheck layout of this demo file does what you want: http://www.4shared.com/file/gGZ8xzCh/BedRentingTest.html

                        If LaRetta is reading thie, I'm pleased to note that no Refresh Window or even a commit record step was needed to get the portal to update when you specify different date ranges.

                        This system uses three relationships between rooms and reservations to count all reservations records that overlap the specified dates entered into the global date fields. The global fields are defined in Rooms so that they can be used for these relationships.

                        EnclosedBedReservations is used to count the reservations where the start date precedes the global start date and the end falls on a date after the global end date. < and >, not < and > are used in this relationships so that no records that match this relationship are also records that match in either of the other two relationships.

                        It uses this relationship:

                        Rooms::__pk_RoomID = EnclosedBedReservations::_fk_RoomID AND
                        Rooms::gStartDate < EnclosedBedReservations::Startdate AND
                        Rooms::gEndDate > EnclosedBedReservations::EndDate

                        StartDateBedReservations is used to count the reservations where gStartDate falls on the interval from startDate to endDate.

                        Rooms::__pk_RoomID = StartDateBedReservations::_fk_RoomID AND
                        Rooms::gStartDate > StartDateBedReservations::Startdate AND
                        Rooms::gStartDate < StartDateBedReservations::EndDate

                        cStartDateResList is used to generate a list of all reservation IDs that meet the criteria of the StartDateBedReservations relationship. We'll use this calculation to exclude any records from the third relationship that meet its criteria:
                        List ( StartDateBedReservations::__pk_BedResID )

                        The third relationship, for EndDateBedReservations, then becomes:

                        Rooms::__pk_RoomID = EndDateBedReservations::_fk_RoomID AND
                        Rooms::gEndDate > EndDateBedReservations::Startdate AND
                        Rooms::gEndDate < EndDateBedReservations::EndDate AND
                        Rooms::cStartDateResList  ≠ EndDateBedReservations::__pk_BedResID

                        To count reservations so that we can determine which rooms have available beds, I defined a summary field, sCount in BedReservations to "count" the __pk_BedResID field. (Selected this field for counting as it is never empty.)

                        This then makes possible two calculation fields in Rooms:

                        cBedsReserved:
                        EnclosedBedReservations::sCount + EndDateBedReservations::sCount + StartDateBedReservations::sCount

                        cBedsAvailable:
                        RoomTypes::BedCapacity - ( EnclosedBedReservations::sCount + EndDateBedReservations::sCount + StartDateBedReservations::sCount )

                        Now for a portal filter expression, I used:

                        Rooms::cBedsAvailable > 0

                        Had I omitted that filter expression, the same portal would list all rooms and provide a count of available beds for each.

                        • 9. Re: Filter portal based on count of records in data range
                          NorbertKowalski

                          "I also think that I misunderstood the purpose of the portal. I thought you wanted a list of rooms available, but this sounds like you want a list of rooms that already have a reservation overlapping the one in question but have at least one bed available."

                          Close enough. Exact solution I seek is "that already have a reservation overlapping the one in question but have at least one bed available OR have no reservation made for date range in question", but I should be able to work my way from here. Once again, sorry for not being clear enough with what exactly I am needing.

                          Thank you very much for all the help you have provided. I will look into given examples, and hopefully be able to understand how they work and, should the need arise, tweak them to my project specific needs.

                          • 10. Re: Filter portal based on count of records in data range
                            philmodjunk

                            After I set up and tested the demo file, I found that it lists exactly that. It lists all rooms that have at least one bed available in the specified date range.

                            • 11. Re: Filter portal based on count of records in data range
                              NorbertKowalski

                              I successfully integrated your solution into my system, and as far as I tested, it works perfectly. 

                              Thank you very much for your help, I doubt I would be able to work it out myself.

                              • 12. Re: Filter portal based on count of records in data range
                                philmodjunk

                                I've been running two very similar threads on this issue. Another user needed to rent out beds (not rooms) and needed a list of all rooms with at least one bed available for a specified date range. I suggested something there that may update more smoothly than the sum functions I just recommended here.

                                Define a summary field in the LineItems table as the total of your quantity field. then replace the sum functions in that last expression with three references (one for each relationship) to this summary field. You may find that it updates quicker as you work with the larger numbers of records in your actual database.