6 Replies Latest reply on Aug 16, 2013 3:09 AM by erolst

# automatically calculate rent between dates

Dear forum members,

As a very new member (today actually) I am so bold to launch a challenge I am facing.

I am a real estate agent on the Costa Blanca in Spain. For several owners, we run a total of 11 real estate properties which are rented out to holidaymakers.

Each property has different rental rates (which are per week), divided over different periods.

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

etc.

How can I let Filemaker automatically calculate the rent for any given property between certain dates.

Example:

calculate the rent for property 001 from 22-02-2014 until 03-03-2014.

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.

Any tips and suggestions are appreciated.

Kind regards,

Maurice

• ###### 1. Re: automatically calculate rent between dates

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.

• ###### 2. Re: automatically calculate rent between dates

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 ;-))

• ###### 3. Re: automatically calculate rent between dates

Hi Maurice,

How about tagging erolst as the correct answer, rather than your own response to him. That way he gets the kudos. Cheers!

• ###### 4. Re: automatically calculate rent between dates

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?

Kind regards,

Maurice

• ###### 5. Re: automatically calculate rent between dates

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.

What's happened?

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.