2 Replies Latest reply on Mar 16, 2013 7:21 AM by pmjd

    Calculate rental price based on different rental rates per day?

    pmjd

      Title

      Calculate rental price based on different rental rates per day?

      Post

           Hello everyone,

            

           I'm new to databases and evaluating Filemaker Pro to see if I could use it for our vehicle hire business. At the moment we still use paper rental agreements and a limited amount of very basic info is recorded in a spreadsheet to calculate our insurance payments (which is based on a % of revenue).

           Filemaker Pro has given me a lot of exciting (well to me they are lol) ideas that I would love to put in to practice but I need some help with a few basics first and I am looking for a few pointers in the right direction to help get me started

           I have a table called BOOKINGS. Within it are the start date/time and end date/time for a rental. I have set up a calculation that returns this as the number of days, with a minimum value of 1 day and increasing in 0.5 day increments depending on the time difference. I would like to be able to automatically calculate the cost of a rental, which is based on three criteria:

           1) Pricing Scheme - We have different pricing for different seasons and types of customer

           2) Vehicle Type - each rental vehicle class has it's own pricing rates

           3) Rental Daily Rate - for 1 - 7 days the rental rates are different and the daily rate decreases until 7 days where that is the lowest daily rate available.

           So I have a pricing table which has the following entries:

           scheme_name | vehicle_type | one_day | two_day | three_day | four_day | five_day | six_day | seven_day | over_seven |

       i.e. standard     |     economy    |       32      |       64     |            84     |       114   |        135   |     150    |         175     |         25         |

            

           So my plan was on the BOOKING table to have a pop up list to select the pricing scheme_name and the vehicle_type wanted to offer the correct rate from which to calculate the rental cost based on the rental days (calculated by the date & time entries).

           Is this possible? I was looking at conditional value lists to steer the calculation towards the correct rate but wasn't sure how to translate the calculated rental days number to correspond with the correct rate from the PRICING table, i.e. a result of 2 would lookup the two_day value in the table and return a result of 64. A result of 9 would return 25 and lead to a further calculation of  9 * 25.

           Could any one point me in the right direction?

      Also....

           I have a Renter for each agreement who may or may not be driving the vehicle (i.e. company the hire is to be charged to or a family member paying for the rental but cannot or doesn't want to drive) but we can also have additional drivers. I originally had a DRIVERS table but couldn't work out how to let there be one renter from the DRIVERS table but also allow other records/drivers from the DRIVERS table to appear on the same agreement. Do I need to use a joining table of some sort to accomodate this kind of relationship?

           Any help or hints would be greatly appreciatedsmiley

           Thanks, 

           Paul

        • 1. Re: Calculate rental price based on different rental rates per day?
          philmodjunk

               You can set up a rate field in bookings with a case function and an auto-enter calculation with a case function:

               The relationship:

               Bookings>----DailyRates

               Bookings::Scheme = DailyRates::Scheme AND
               Bookings::Vehicle_Type = DailyRates::Vehicle_Type

               Case ( DaysRented < 1 ; DailyRates::One_Day ;
                          DaysRented < 3 ; DialyRates::two_day ;
                          DaysRented < 4 ; DailyRates::Three_day;
                         and so forth.... )

               

                    I have a Renter for each agreement who may

               Yes, you need a join table between bookings and drivers.

               Bookings-----<Booking_Driver>-----Drivers

               Bookings::__pkBookingID = Booking_Driver::_fkBookingID
               Drivers::__pkDriverID = Booking_Driver::_fkDriverID

               A field in Booking_Driver can identify the Driver who will be responsible for paying for the vehicle. A second field in the join table could even identify a driver as "non-driving" if that is a useful thing to record.

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

          • 2. Re: Calculate rental price based on different rental rates per day?
            pmjd

                 Fantastic!, thank you very much PhilModJunk, I have now got the rental calculations working and if I get a chance today I'll start work on the join table.