A function called GetAgingDate() is available to help you create your own Aging reports. When used, this function will prompt for the Aging date, and then return the entered date until the report finishes. You can use this function to create Aging Formulas using the Formula Editor (such as "0-30", "31-60").

Here's how you do it.

  • Create a Quick Report.

  • In Step 2 of the Quick Report Wizard, select the fields you want to appear in the report.

  • In Step 2, click the Formula button. Select the table you want the new field to go into (Sales Order Header in our example) and give the field a name (Invoice Total (0-30)).

  • We want to create an Aging field for the SLHEADER.TOTALAMT field using SLHEADER.ORDRDATE as the date to compare our Aging Date to. Since this field is Aging from today to 30 days ago, enter an expression of: iif(between(SLHEADER.ORDRDATE, GetAgingDate() - 30, GetAgingDate()), SLHEADER.TOTALAMT, 0.00)

  • This expression will evaluate to the "Order Total Amount" if the "Date Ordered" field is between 0 and 30 days prior to the Aging Date entered (the "0-30" column).

  • You can use similar expressions for the "31-60", "61-90", "91-120", "121+" etc... columns:

    31-60: iif(between(SLHEADER.ORDRDATE, GetAgingDate() - 60, GetAgingDate()-31), SLHEADER.TOTALAMT, 0.00)

    61-90: iif(between(SLHEADER.ORDRDATE, GetAgingDate() - 90, GetAgingDate()-61), SLHEADER.TOTALAMT, 0.00)

    91-120: iif(between(SLHEADER.ORDRDATE, GetAgingDate() - 120, GetAgingDate()-91), SLHEADER.TOTALAMT, 0.00)

    121+: iif(SLHEADER.ORDRDATE < GetAgingDate() - 120, SLHEADER.TOTALAMT, 0.00)

  • You can now use these new "Invoice Total (XX-XX)" fields in any Report just by adding them to the Report in step 2.

Here is an example of what the report preview would look like:

© Stonefield Software Inc., 2022 • Updated: 01/13/22
Comment or report problem with topic