How to calculate the ageing of transaction records?
I am trying to work out how to set up a field or fields that calculate how long a payment has been unpaid. eg 30+ days, 60+ days, 90+ days prior to current date. It would be working off a global date field that calculates the current date when the my related files open using "InsertCurrentDate" in my open file script.
I have a related transaction file that generates transactions for each client but have no way of tracking how long a transaction has been unpaid for. It's known as "account ageing".
Any ideas on whether there are calculations that can be done in filemaker pro 7 to put together a script that would calculate a result based on how many days/weeks/months have elapsed from the date when any given transaction was generated, up to the current date?
There are WeekOfYear(date) and WeekOfFiscalYear(date) functions that calculate the results as a number relating to the no of weeks elapsed since the year start date but I can't figure out how to work them in a calculation.
eg. No of weeks from CurrentDate working back to a given Transaction Date calculated as a result.
If current date = 12/8/2010 and transaction date = up to 1 month prior then set as 30 days.
Where result 1 = 30+ days category (1 month) ; Result 2 = 60+ days (1-2 months) ; Result 3 = 90+ days (2 months+)
Can filemaker calculate dates in this way? Any help will be much appreciated.