AnsweredAssumed Answered

How to calculate the ageing of transaction records?

Question asked by DanielHirtenstein on Aug 13, 2010
Latest reply on Aug 13, 2010 by philmodjunk


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.