11 Replies Latest reply on Jun 11, 2014 4:56 AM by alquimby

    Calculating rent

    maurice2307

      Good afternoon,

       

      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.

      OR

      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.

       

      Kind regards,

      Maurice

        • 1. Re: Calculating rent
          erolst

          I remember giving you a sample calculation last year for either this exact problem or one very similar to it, to which you never bothered to respond.

          • 2. Re: Calculating rent
            maurice2307

            Hi Erolst,

             

            If that is so (and if you say so, it must be) I apologise for that. Especially for not replying – that was not polite.

            The thing is that I am not a professional programmer and have lots of other business going on. Therefore some issues take precedence at some point in time.

             

            Still, my problems remain and I would appreciate your help in this.

             

            Kind regards,

            Maurice

            • 3. Re: Calculating rent
              erolst

              OK … are you still on FM11?

              • 4. Re: Calculating rent
                maurice2307

                No, using FM13.

                • 5. Re: Calculating rent
                  erolst

                  maurice2307 wrote:

                  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.

                   

                  Taking this into account, a simple script and a utility relationship is probably the cleanest method.

                   

                  I'm not sure how you plan to handle “broken” weeks; probably you'll have to compile a list of applicable dates and pass that to the script to loop through, instead of looping towards the end date.

                   

                  See if the attached helps you.

                  • 6. Re: Calculating rent
                    maurice2307

                    Thank you for your help.

                    I shall look at it later tonight and let you know.

                     

                    Kind regards,

                    Maurice

                    • 7. Re: Calculating rent
                      maurice2307

                      It's great! Thank you again.

                       

                      I shall follow the scripsteps and hope to learn from what is going on in there.

                      This gives me a good working base to further develop my requested functionality.

                      Taking this hurdle really will advance my app and my insight in FM.

                       

                      un saludo from sunny spain.

                      • 8. Re: Calculating rent
                        alquimby

                        Maurice,

                         

                                  I’m late to this party, but thought I’d chime in anyway.

                         

                                  One of the great things about FileMaker is that there are generally several ways to accomplish a task. When looking at a problem like yours, I seldom think of the looping script approach created by erolst because my limited mind usually fails to grasp the logic of those scripts (including the one in his sample file). Rather, I usually take the join table approach.

                         

                                  Using erolst’s sample file as a starting point, the attached has a join table that looks up day rents (and copies the next lower if the agreement end date exceeds what exists in the rate periods table). If rates are changed after the agreement is created, or the agreement end date is extended, reentering the end date will delete the existing agreement’s related records and create a new set. Using a join table allows you to change any rent within the period as in agreement 3 on July 4 (say you have to give a refund, for example).

                         

                                  If you never have to worry about changing a rent after the agreement is created, erolst’s solution is best because it makes for a much smaller file.

                         

                                  I just noticed you are apparently in Spain––apologies for the attached’s US currency settings.

                         

                        Al Quimby

                        • 9. Re: Calculating rent
                          maurice2307

                          Hi Allen,

                           

                          Thank you very much for your comments and file.

                          I'll explore your suggestions and revert.

                           

                          Kind regards,

                          Maurice

                          • 10. Re: Calculating rent
                            maurice2307

                            Hi Allen,

                             

                            Thank you for empathising with the question at hand. While it is true that certain clients may apply for a discount in rent, or that rental periods may change during the course of a RFQ, I have got that covered in another part of my solution.

                             

                            The calculation routine I did by hand, as well as your ingenious solutions, yields a number (the rent). I can always change that number in the quotation to the client by either applying a percentage or a fixed amount as a discount. So provisions for deviation from the calculated rent does not have to take place within the solutions Erolst and yourself so graciously offered.

                             

                            I thought you’d like to know that while ‘dissecting’ your solution and Erolst’s, I am learning a lot. How you gentlemen approached the issue of ‘walking’ through the number of days and fetching the appropriate rate is exactly what I wanted to learn.

                            My issue with FM (or with any other programming language/framework) is the translation of the business rules into scripts or code. It’s interesting to watch how you and Erolst approached the question at hand and it’s comforting to know that there are more solutions to one and the same problem.

                             

                            Thank you both again.

                             

                            Kind regards from sunny Spain.

                            Maurice

                            • 11. Re: Calculating rent
                              alquimby

                              Glad I could help.

                               

                              Al