3 Replies Latest reply on Aug 13, 2010 10:26 AM by philmodjunk

    How to calculate the ageing of transaction records?



      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.

        • 1. Re: How to calculate the ageing of transaction records?

          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.

          • 2. Re: How to calculate the ageing of transaction records?

            Brilliant  - thanks. And it allows the setting up of unpaid categories as a secondary calculation!

            • 3. Re: How to calculate the ageing of transaction records?

              A follow up suggestion. Say you have over 10,000 records and you want to find all unpaid transactions that are 60 days or older. Entering search criteria in the days past due calculation field forces filemaker to first index the field then perform the find and this can take a long time.

              Instead, perform find that just uses the paidfield--which can be a stored, indexed field to find all unpaid transactions. Then enter find mode again, specify the > 60 criterion in the past due field and use Constrain Found Set to narrow the records down to just those that are unpaid, 60 days past due. You get to the final result, many times quicker!