Sorry to bother you all on a Sunday, but I’m kinda stuck here and am in need of ideas by fellow toilers :-)
I am struggling with how to let FM perform some fairly simple business logic, but I cannot seem to get over the threshold to get Filemaker to do this.
Ok, here’s the situation.
I’m an agent for properties (villas) which are rented out.
Per given period in the season (date FROM… date UNTIL) there are different rates for the rent.
Example 1: from June 1st to June 14th the rent is 1.450,= per week, which translates to 207,14 per night.
Example 2: from June 15th to July 31st the rent is 1.750,= per week,which translates to 250,= per night., etc.
For each record in the property table there are corresponding (linked) records in the rent table.
Now I receive a RFQ for rent from let’s say June 7th until June 21st, and I (manually) do the following:
Calculate the number of nights, in this case: 14
Go and fetch the rent for this period.
Check if the rent is the same for all nights: no (otherwise: nr. of nights times rent x night; round-off; done)
Calculate nr. of nights until rate change: 7 nights
Apply rent for this period: 7 nights x 207,14 = 1.449,98
Continue calculation nr. of nights until the next rate change: 7 nights
Apply the rent for this period: 7 nights x 250,00 = 1.750
Total rent: 1.449,98 + 1.750,00 = 3.199,98, rounded-off to 3.200,= for the whole period.
As you can probably see it’s no big deal by hand, but it’s prone to (human) calculation errors and the sheer number of RFQ’s force me to have FM perform this automagically.
Now, for my – personal - Filemaker bottlenecks.
1. How can I get FM to start a loop whereby it checks if there is a rate change between the FROM and UNTIL date?
2. Then, if no change is detected, multiply the number of nights by the appropriate rate.
If a change in rate IS detected, multiply the number of nights with the appropriate rate and store that outcome somewhere.
Then continue to count the remaining nr. of night and multiply that with the corresponding rate and store that outcome somewhere.
Finally, add those two outcomes up to yield the total rent, round-off and display.
FTR, in this example there is one rate change within the requested period. But there may me more rate changes if the stay is long enough.
Also, this example is for 2 full weeks. But we often get RFQ’s for broken weeks, hence the calculation of rate x night.
Your comments are greatly appreciated.