1 Reply Latest reply on Mar 14, 2013 3:02 PM by philmodjunk

    Calculation with several variables



      Calculation with several variables


           The task here is to devise a formula that returns the proper 'day rate' based on two variables. There are four cottages (each  is one record in the table 'cottages') with two day rates based on seasonality. Also a related 'Reservation' table that selects dates and which cottage. No problem returning the correct rate for the first cottage using  

               Case(kf_cottageID="c1"and Arrives<Date(4;30;2013);"70";"100") 

           When selecting cottage 'c1' , 70 returns to dates prior to Apr 30 and 100 for dates after, correctly.

           But for cottage 'c3' a different rate schedule is in effect .140 before Apr 30 and 200 after. I have tried using OR and repeating the expression with c3 and the different rates but the result is not correct and c1 no loger returns correctly either.

           How  can these expressions be strung together to produce the correct day rate when cottage c3 or c5 (also with a different rate schedule)  is selected? Can it be done using only two tables?

           Many Thanks for replies



        • 1. Re: Calculation with several variables

               The rates need to be recorded in the cottages table and then the correct  rate should be copied from cottages to reservations for use in computing the cottage rental charge.

               The relationship:


               Cottages::__pkCottageID = Reservations::kf_cottageID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Then Cottages::Rate should be defined as a number field with this auto-enter calculation:

               Case ( Month ( Arrives ) < 5 ; Cottages::Rate1 ; Cottages::Rate2 )

               I'm recommending using an auto-enter calculation so that if the rates change in the cottages records, existing reservations records for previous years will not change the rates charged. By only looking at the month, instead of the entire date, you will not have to update this calculation for next year's rentals.