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

    automatically calculate rent between dates

    maurice2307

      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
          erolst

          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
            maurice2307

            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
              keywords

              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
                maurice2307

                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
                  maurice2307

                  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.

                   

                  Thanks in advance,

                  Maurice

                  • 6. Re: automatically calculate rent between dates
                    erolst

                    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 …