### Title

How to calculate the ageing of transaction records?

### Post

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.

There's function called Get ( CurrentDate ) that you can use so you don't need a global field with a script that inserts the date each time. You can just refer to it.

This calculation will return the number of days a transaction is past due:

Get ( CurrentDate ) - Transaction Date

If you have a field in the table that identifies whether the item is paid or not by storing a 1 if paid and a 0 if not, you can enhance the calculation this way:

If ( not PaidField ; get ( CurrentDate ) - Transaction Date ; "" )

( ; "" added for clarity, if you leave that term out, you get the same results )

This must be an unstored calculation to reevaluate correctly with each new day, which can slow down searches and sorts on this field, but using a global field in place of get ( currentdate ) will also make the calculation unstored.