Hello Maurice, and welcome –
The way I do this now is to determine in which bracket the rent is (in this example it's 830,=/week).
I then divide the rent by 7 (=rent/day) and then multiply by 9 days = 1067,14.
I had just written an elaborate explanation, when I realized that setting up a small sample database would have taken less time; so I decided to do both.
You'll find the explanation inside the database. Don't hesitate to ask if anything is unclear, or you need more information.
RateLookup_eos.fp7.zip 10.2 K
Wow, I am amazed.
Instead of just giving me an answer you built me a whole app!
I shall indeed study it carefully and revert back to you in case of anything being unclear.
Thank you VERY much ;-))
How about tagging erolst as the correct answer, rather than your own response to him. That way he gets the kudos. Cheers!
Good morning Keywords,
Yes, I see now what I've done. Verry sorry about that.
Of course i didn't mean to deprive erolst from his kudo's.
How may I correct this without making other goofs?
Good morning erolst,
I have 'dissected' your app and implemented it in my solution. Needless to say it works like a charm and I was very pleased with it.
You may have noticed the word 'was', because I've encountered yet another issue for which I hope you can steer me in the right direction to solve it.
When selecting a dateStart and dateEnd which are within the same date bracket of the property, the calculation works fine.
However when the dateStart and dateEnd do not fall in the same date bracket (which unfortunately very often is the case), then the calculation sticks to the rate from the dataStart bracket.
Let's look at the original example:
property rent x week valid from valid to
001 830,= 01-01-2014 31-03-2014
001 950,= 01-04-2014 30-06-2014
001 1200,= 01-07-2014 15-09-2014
002 550,= 01-01-2014 30-06-2014
003 750,= 31-03-2014 15-10-2014
If I get a rent request for 10 days, let's say from 28-03-2014 to 07-04-2014, I would need to calculate as follows:
4 days @ 830/week + 6 days @ 950/week (474,29 + 814,29= = 1.288,58
As the calculation is now, it simply takes 10 days @ 830/week = 1.185,71
So I wonder if I may pick your brain yet again to help me out with this. I can work it out by hand, but lack the FM skills to put it into a working calculation.
Thanks in advance,
I think the simplest approach is to create another table for the individual days and have each day look up its own rate. You only need to make sure that subsequent changes in the date fields of the RA are reflected in the related records (conditional formatting to alert the user to create a new set, or script trigger(s) on the date fields to capture changes and make the approach automated). You probably should also check if the rent period is completely covered by the rates for the property of the RA.
Oh, and you need to tweak this little sketch a bit; when I used your sample dates, I created 4 recs à 830, and 7 à 950, not 6; you need to decide how to handle this; ignore the final day in the summation, create one record less in the script …
RateLookup_v2_eos.fp7.zip 13.1 K