Calculate rental price based on different rental rates per day?
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?
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?