4 Replies Latest reply on Feb 10, 2015 2:10 PM by philmodjunk






      In my invoice, I want a field to display "price per night", this figure will be either one of two, based on the arrival date of the booking. I have two prices, High season price per night & low season price per night.
      My prices table is
      Accommodation Type                    High Season Price per Night                 Low Season Price per Night

      I want a price per night field within my invoice to display either the high season price or low season price based on the arrival date of the booking

      High season is 01/04 - 30/09. So if the arrival date was within that range, I'd want the cost per night to display the price per night for the accommodation type specified for the High Season.

      How would I do this as a calculation field? I've tried several ways and it either only displays the low season price, regardless of the arrival date or it returns a "?".

      Thanks, Daniel Jenkins

        • 1. Re: Invoice

          Can anyone help me with this please?

          • 2. Re: Invoice

            First a simple solution and then a better but more complex solution:

            If ( Month ( BookingDateField ) < 10 ; HighSeasonRateHere ; LowSeasonRateHere )

            A better solution:

            Set up a table, BookingRates, with at least these two Records, with these fields and values:

            LastMonth          9
            Rate                   HighSeasonRateHere

            LastMonth          12
            Rate                   LowSeasonRateHere

            In your invoice table, define a calculation field cBookingMonth as Month ( BookingDateField ) so you can use it in this relationship:
            Invoice::cBookingMonth < BookingRates::LastMonth

            And then you can use an auto-enter field option (calculation or looked up value) to copy the value of BookingRates::Rate into a field in your Invoice record. With this second approach, you get several advantages:

            1) You can update your rates by editing records in the BookingRates table instead of modifying your calculation. Thus, you have a data entry task that can be performed by any authorized user not just someone who knows how to mocify the database design.

            2) If you ever decided to modify your definition of High and Low seasons, you could adapt this again by modifying data in records instead of redefining your calculation.

            3) If you have more than one type of accommodation with different rates for each, you can add an "accommodation type" field to both Invoice and Booking rates and modify the relationship to also match by these new fields and now your table can manage booking rates for any number of different accommodation types.

            • 3. Re: Invoice

              How would I account for the fact that high season is only between months 04-09

              if i did it that way, months 01-09 would be high season.

              Currently I have a prices table storing the data using these fields
              Accommodation Type                Price per night (High Season)                    Price per night (Low Season)

              The price per night field in the invoice table is a calculation field
              I have this:

              If ( Bookings::Arrival Date = "01/04/2015" & "..." & "30/09/2015" ; Accommodation Prices::Price per night High Season ; Accommodation Prices::Price per night Low Season )

              However it only displays the low season, regardless of the month currently.
              Do you have any idea as to what is wrong with my calculation?

              Thanks, Daniel


              • 4. Re: Invoice

                Sorry, misread your dates as I normally think in terms of Month/Day rather than Day/Month.

                The price per night field in the invoice table is a calculation field

                Not a good idea. At some point in the future, you'll need to change your rates and when you do, older invoices will show incorrect totals when they automatically update to use the new rate instead of the rate that was actually used at the time the invoice was created. Use an auto-entered calculation or use a look up table--both methods can be adjusted for the date range that you specified.

                There are several problems with your current calculation. "01/04/2015" evaluates as text, not a date. It would also need to be changed every year--a major hassle for you that can be avoided using other means. and the "01/04/2015" & "..." & "30/09/2015" expression might work as find criteria but is not valid syntax in a calculation.

                Sticking with the calculation for this post (let me know if you want to try the look up table method):

                Let ( m = Month ( Bookings::Arrival Date ) ; If ( m > 4 and m < 9 ; HighSeasonRate ; LowSeasonRate ) )

                A look up table method could be used where you match to a range of values from 4 to 9 for high season and 1 to 3 or 11 to 12 for low season.