4 Replies Latest reply on May 8, 2011 2:16 PM by NicolayFlaaten

    Calculating high season price

    MichaelA.

      Title

      Calculating high season price

      Post

      Hello there,

      in my case Filemaker is used for manage apartment reservation and booking. There is a formula used for calculating weekly and monthly price. (Look screenshot) So far no problem. Now i was ask if i can change the calculation that the formula consider high season prices for summer and easter holidays. The calculation is using the count of nights between check in date date check out date. My question now is how i check if the date between check in and check out overlaps with the high season period and add the higher price only for the nights which is in the period to the normal price? I hope i explain it clear.

      Thanks for help y saludos, Michael

      Bildschirmfoto_2010-08-29_um_12.46.47.png

        • 1. Re: Calculating high season price
          philmodjunk

          First issue: Since the exact dates that correspond to easter holidays will change with each year and the apartment management might choose to change what dates mark the beginning and end of summer holidays, how will you record these date ranges so that they can be maintained as easily edited data in your system? Once you have that set up, you can implement a change in your billing process to break up a given reservation entry into multiple line items that total the complete reservation, but use different rates based on which rate category a given reservation date falls.

          I'd use a "rate table" for you different portions of the calendar:

          RateID ( Serial Number Field )
          StartDate, EndDate (date fields)
          Monthly, Weekly  (Number )
          UnitID (ID number of apartment unit or group if you have differing rates for differing units)

          This can be set up for uniform rates or rates that differ for each apartment unit ( or group of units) as needed by your business model. You can put records for each holiday period where the rates are to be modified and leave non-holiday a default value, or you can include the non-holiday intervals a just another rate period in this table--I prefer the latter here.

          You'll then need to check for any reservation start and end dates that cross from one such interval into another. I'd use the following relationships to such a rate table to check for this:

          Reservations::StartDate > RatePeriod 1::StartDate AND
          Reservations::StartDate < RatePeriod 1::EndDate

          Reservations::EndDate > RatePeriod 2::StartDate AND
          Reservations::EndDate < RatePeriod 2::EndDate

          RatePeriod 1 and RatePeriod 2 would be two table occurrences of the same RatePeriod data source table.

          If RatePeriod 1::RateID ≠ RatePeriod 2::RateID then you know that the reservation spans two or more rate periods and you can use a loop to divide the reservation date interval into two or more intervals (A 30 day rental could start before easter and end after easter resulting in 3 different intervals)--each with a different rate looked up from this rate table.

          Note: if you have different rates for different units, then the above two relationships would use one more pair of fields to match rates by UnitID in addition to the date range.

          • 2. Re: Calculating high season price
            MichaelA.

            Thank you for the detailed answer Phil..

            i also thought that i need resolve it over a table relation. One thing is not so clear for me, (i work only from time to time with filemaker) that is this thing with the loop and how to get the different prices from the rate table records together. Each apartment has its own monthly and weekly price, so also the high season. The easter holidays i will manage manually. Setup the dates for the next 5 years for example with the option to continue.

            Thanks y saludos, Michael

            • 3. Re: Calculating high season price
              philmodjunk

              As I said in my last post, you can support different rates for different apartments by adding a pair of fields to your rates table.

              ReservationLineItems::StartDate > RatePeriod 1::StartDate AND
              ReservationLineItems::StartDate < RatePeriod 1::EndDate AND
              ReservationLineItems::UnitID = RatePeriod 1::UnitID

              ReservationLineItems::EndDate > RatePeriod 2::StartDate AND
              ReservationLineItems::EndDate < RatePeriod 2::EndDate AND
              ReservationLineItems::UnitID = RatePeriod 2::UnitID

              The exact details of your loop will vary with your database structure. Are you set up to bill with an Invoice-LineItem type structure where a single reservation record can link to multiple date intervals--each with a different rate?

              This script assumes this relationship:
              Reservations::ResID = ReservationLineItems::ResID

              If ( not IsEmpty ( ReservationLineItems::ResID ) /* Confirm there is a reservation line item record */ ]
                     Freeze Window
                     Go To Related Records [ Show only related records ; From table: "ReservationLineItems" ; Using Layout: "ReservationLineItems" ( ReservationLineItems)]
                     Loop
                             Exit Loop If [ RatePeriod 1::RateID = RatePeriod 2::RateID /* No more splitting needed */]
                             Set Variable [$EndDate ; Value: ReservationLineItems::EndDate ]
                             Set Field [ReservationLineItems::EndDate ; RatePeriod 1::EndDate ]
                             Set Variable [$StartDate ; Value: RatePeriod 1::EndDate + 1 ]
                             Set Variable [$UnitID ; Value: ReservationLineItems::UnitID ]
                             Set Variable [$ResID ; Value: ReservationLineItems::ResID ]
                             New Record/Request
                             Set Field [ReservationLineItems::ResID ; $ResID ]
                             Set Field [ReservationLineItems::UnitID ; $UnitID ]
                             Set Field [ReservationLineItems::StartDate ; $StartDate ]
                             Set Field [ReservationLineItems::EndDate ; $EndDate ]
                             Commit Record
                     End Loop
                     Go To Layout [Original Layout]
              End If

              • 4. Re: Calculating high season price
                NicolayFlaaten

                I am in the same genre with similar situation.

                I have got the two portals with the related priceseasons to work. Is there a way to create a calculated field that somehow uses a Let function or similar that counts weekdays and weekenddays within the rateperiod? I have weekprices, weekendprices, and day prices.

                Regards NIcolay