The math in your Let function assigning values to m, w and d seems to track. Where do the results fail for you?
Using a case statement like this with "hard wired" references to each vendor by name is very inflexible. Even if you get it to work, every time you add vendors or a vendor changes their name, you'll have to rewrite this expression.
It will be a much simpler and vastly more flexible expression if you use a vendor ID to look up only one set of rates for this calculation.
That makes sence I have a vendor table and equipment table no i just have to make a prices table!! Thanks for the help file. I will make it work!!!
THANKS A BUNCH!!!!
I just thought of something, I'm still going to have to make the rates work. 4 days=to 7 days and 3 weeks = to one month and one month is 28 days. What was happening before is i would have a rental for 9 days and it would be daily rate times 9. I need it to be weekly rate for first 7 days plus two extra days. 4 to 7 days is the same rate and 1 week and 4 days is the same price as 2 weeks. That was the problem for my first posting so Im going to make a new price list but I think Im going to have the same problem.
I copied your original calculation and then adapted it to the vendor specific rate lookups and it appeared to do what you want. If you specify 9 days, it should compute the weekly rate for one week and then add on two days worth of daily rate. Test my demo, but with your rates and see if it gives you the values you expect. If it doesn't, report back here and we'll take a closer look.
Well it didn't work, sorry to say, 4 days is equal to a week but so is day 5, 6, and obviously the 7th day. Its just adds the daily rate for days 5 and 6. Day six days ends up with a high rate more than seven days.
I do not have any time to assist right now but, for one thing, your calculation is off:
Case ( vendor="Company A";
m * equipment::company a_price monthly + w * equipment::company a_price weekly + d *equipment::company a_price daily;
( vendor="Company B"; m *equipment::company a_ monthly+ w *equipment::company b_weekly + d *equipment::company b_price daily;))
Remove the portions in red.
I took out the case function I went with a seprate price list table. I just need this calculation...
Let ( [
n = RentalDays ;
m = Div ( n ; 28 ) ; // month
w = Div ( n - m * 28 ; 7 ) ; // week
d = n - ( m * 28 + w * 7 ) // day
m * MonthRate + w * WeekRate + d *DayRate )
to show the same rate for days 5 and 6. If something cost 1 dollar for one day, its 4.00 for 4 days or 4.00 for one week
right now its showing 5.00 for 5 days 6.00 dollars for 6 days but showing 4.00 for one week. I wouldnt want to pay more for 5 days than 7 days. Ill work the same calculation for the weekly rate to the monthly rate seeing that 3 weeks is equal to a month its like you get that week free.
"If something cost 1 dollar for one day, its 4.00 for 4 days or 4.00 for one week"
The file I provided allows for a daily cost, weekly cost and monthly cost table. The calculation only determins which price to use. If you have $1 for each price and $4 for week price, that is what you will get if days are less than 7, ie, $6. That's why a User should select the week price - because they get a price break. They can't get a price break if they don't hit the range (7 days).
Of course it would be cheaper for the person to grab the larger price break - tell them to keep the item for 7 days then. I suggest that you re-think what you want to accomplish. You can possibly just display the three price ranges when you select a product and then the renter can decide how long to keep the item to get the best price.
Yea I see what your saying.....I think...... I'm going to have to have two date ranges one for rental period and one for rental rate.
Or have check boxes for which rate to choose?
They wont want it any longer than needed but they would like the price break after the fourth day.