6 Replies Latest reply on Nov 19, 2011 2:54 PM by MichaelVoccola

    Finding available dates (equipment rental program)

    craig5005

      Title

      Finding available dates (equipment rental program)

      Post

      I'm working on a rental type program and have run into a problem.  I am trying to find a way for people to search when certain equipment will be available.  Here's a bit more information.

      People can reserve items in the future (ie. item A on reserve from Feb 1, 2011 to Feb 4, 2011).  Now if another person comes by and wants to reserve item A.  They select their start date as Feb 3rd and end date of Feb 8th.  How can I get it to return a message saying that item A is not available (due to overlap)?  I'm not far into the project so field or table set up doesn't really matter, they can all be changed to fit any solution.

      I've thought about this for awhile, but can't seem to come up with any solutions.  Has anyone worked with something like this?

        • 1. Re: Finding available dates (equipment rental program)
          FentonJones

          These are the 3 basic relationship criteria for a "date conflict" on a piece of equipment. I just wrote "Equip 2" for the target Table Occurrence name; you can come up with a better name.

          Equip::EquipID = Equip 2::EquipID
          Equip::Start ≤ Equip 2::End
          Equip::End ≥ Equip 2::Start

          • 2. Re: Finding available dates (equipment rental program)
            philmodjunk

            If you store the dates in two separate fields, a pair of separate relationships can be checked for an overlap in the date ranges:

            Reservation::EquipID = ReservationByStartDate::equipID AND
            Reservation::StartDate > ReservationByStartDate::StartDate AND
            Reservation::StartDate < ReservationByStartDate::EndDate

            Reservation::EquipID = ReservationByEndDate::equipID AND
            Reservation::EndDate > ReservationByEndDate::StartDate AND
            Reservation::EndDate < ReservationByEndDate::EndDate

            ReservationByStartDate and ReservationByEndDate are new table occurrences of Reservation.

            Then,

            IsEmpty (ReservationByStartDate::equipID ) and IsEmpty ( ReservationByEndDate::equpId )

            will be true only if the selected equipment has not been reserved for the range of days starting with startdate and ending with end date.

            • 3. Re: Finding available dates (equipment rental program)
              MichaelVoccola

              I am working on a similar system involving equipment availability.

              In my situation, the Inventory::TotalAvailable field shows how many of the item are in inventory.

              On the Invoice layout, there is a portal showing Inventory records; each row has a button with a script to add it to the LineItemByInvoice table.

              I would like to show a field Inventory::QTYAvailablable, to display how many of each item is available for the current invoice record using the fields Invoice::StartDate & Invoice::EndDate

              Would I essentially be using some variation of Phil's last post in a calculated field Inventory::TotalAvailable? What other TO's and/or fields would I need to add?

              • 4. Re: Finding available dates (equipment rental program)
                philmodjunk

                The solution I've outline here was intended to track the availabilty of one specific item. (also works for checking the availability of reserved rooms.)

                It won't work as posted for a checking the availabilty of a group of items unless you track reservations on an item by item basis.

                I also left out a third way reservation date ranges overlap. The above two relationships identify reservations records when the start date falls within the start and end date interval, (first relationship) and when the end date falls within the specified start and end date interval (2nd relationship). It's also possible that the specified date interval could encompass both the start and end date interval of a conflicting reservation record:

                Reservation::EquipID = ReservationByEnclosure::equipID AND
                Reservation::EndDate > ReservationByEnclosure::EndDate AND
                Reservation::StartDate < ReservationByEnclosure::EndDate

                which means the complete test would be:

                IsEmpty (ReservationByStartDate::equipID ) and IsEmpty ( ReservationByEndDate::equpId ) and IsEmpty ( ReservationByEnclosure::equipID )

                If you do set up your system to track each individual item out, you could use a similar expression in an unstored calculation field that returns number and then a summary field or a Sum function (from a related table) could count the number of items in a group that are available for the specified time frame.

                 

                PS. Threads like this that are more than 3 months old no longer appear in Recent Items when someone posts to them. This makes them easy to miss and I almost missed this one. You may want to start a new thread that includes a link to this older thread so that your posts appear in Recent Items where more people will see them.

                 

                • 5. Re: Finding available dates (equipment rental program)
                  MichaelVoccola

                   

                  Thanks for the tip, I'll give it a try tonight!

                  • 6. Re: Finding available dates (equipment rental program)
                    MichaelVoccola

                    I am having quite the time wrapping my head around this one.

                    Could you elaborate further on how I could implement this in the system?

                    The portion of the database in question includes the following tables:

                    Inventory

                    Invoice

                    LineItems

                     

                    Currently, there is a portal on the invoice to add items from the table "InventorySearch", a T.O. of "Inventory", which is currently related through a SearchFilter type relationship. Each Invoice has StartDate & EndDate fields. I would like to view a field on each portal record that shows how many of each item is available for the dates of the current Invoice.

                    Items are not tracked individually; LineItems::QTY is the qty of that particular Item# on an invoice.

                     

                    Thanks!