I have been using FMP for a few years now, but have used it for just tracking orders for our engineering group. So basically all my experience has been inputting data.
I have created a new database that requires some calculations and not sure how to formulate them.
My first calculation is an IF statement. I want to subtract one field from a second and if the number is negative I want it to replace the negative number with a 0.
The second calculation is an IF statement that would add a set fee to a number if it has not been paid in one year, but it will also need to add an additional fee for every year it goes beyond that. Example is $50 was due by April 1, 2009 and as of August 1, 2012 the amount has not been paid. The balance now due would now be $50 plus a $5 late fee for 2009, 2010, 2011 and 2012.
Any help would be greatly appreciated.
If ( Field1 > Field 2 ; Field 1 - Field 2 ; 0 )
FeeField + Penalty * int ( ( If ( DatePaid ; datePaid ; Get ( CurrentDate ) ) - DueDateField )/365.25 )
This will multiply the yearly penalty by by the number of complete years elapsed. This field will need to be unstored to evaluate correctly. datePaid and DueDateField would need to be fields fo type date.