AnsweredAssumed Answered

Calculation not auto calculating

Question asked by Guildy on Nov 23, 2012
Latest reply on Nov 23, 2012 by philmodjunk


Calculation not auto calculating



     An unusual one!

     I have a FM pro database which calculates rentals on property (real estate) and the formula is straightforward in that it first calculates the number of months since they moved in and multiplies that by the rent. I have similar calculations for weekly, four weekly, quarterly etc. and then a simple IF statement to choose which calculation to use depending on the period of the rentals.

     For some strange reason, if a rent is due today (say 23rd of each month) the database won't show the rent owing. What I have to do is go into the database and to the appropriate field (e.g. number of months from start), untick the "do not evaluate if all referenced fields are empty" box, click ok, go back in and the rent will be shown as due. Then, tomorrow say another rent on another property falls due (24th of every month), I will have to go back in and this time tick the same box, come back out and it works.

     It seems to me it's not auto calculating and my ticking or unticking of that box is doing is not really doing anything other than refreshing the calculation?

     By watching the data using the data viewer, it's defiantly the counting of the months / weeks etc. that's the problem and not calculations further down the line (such as multiplying by the rent etc.). For example:

     We have a weekly tenancy which falls due on a Friday. So today, using the following calculation:

     Case (payable_from > Get(CurrentDate) ; "" ; DayOfWeek (payable_from) = DayOfWeek (Get(CurrentDate)) ; Ceiling ((Get(CurrentDate) - payable_from) /7+1) ; Ceiling ((Get(CurrentDate) - payable_from) /7))

payable_from is 02 November 2012 and the data viewer showed the number of weeks to today as 3 (which of course is wrong, there are 4 weeks including today because you also include the payable_from date for rental purposes). All I did though was untick the box described above and now the rent is correct and data viewer shows number of weeks as 4.

     Next Friday, I will have to re-tick the box again to get this to work!

     The calendar monthly number of months calculation is: 

     If ( IsEmpty (payable_to) ; (Year (Get (CurrentDate)) - Year (payable_from)) * 12 + (Month (Get (CurrentDate)) - Month (payable_from)) - If(Day (Get(CurrentDate)) < Day(payable_from) ; 1 ; 0 ) ; (Year(payable_to) - Year (payable_from)) * 12 + (Month (payable_to) - Month(payable_from)) - If(Day(payable_to) < Day(payable_from) ; 1 ; 0 ))

     And exactly the same problems occur.

     Finally, I should add that I have a FMP 10 database which uses exactly the same formula (which is where I copied it from) and this works perfectly. No problems whatsoever. Also, I have lots of other calculations doing other things in this database and they all seem fine.

     I did re-write the formula in case when it was imported into FMP 12 it had become corrupted but it is exactly the same.

     Any help would be greatly appreciated.

     Many thanks