Create a report which shows data from multiple date ranges

[lizzie]

Greetings

I am working on a Debtors report. I need to create a report which finds all invoices which are 30-, 60-, 90- and 120 plus days old and show these in a final report.

I am able to work out the date function (i.e. 30/60/90 etc) but how do I go about getting the data?

Would I write a script which performs multiple finds and posts the data into a layout after each find based on the number of days past?

Would I use a sub-summary report and if so, how would one summarise on different supposed date ranges?

I guess I just need assistance in the thought process and the design of this script.

Any help will be greatly appreciated!

Mucho gracias!

[Mikhail Edoshin]

You basically need to find all invoices that are at least 30 days late and then sort them into groups by how late they are. Sorting is simple and then you need a field in “Invoice”, say, “How late”, with a formula like:

Let( days late = Get( Current Date ) - Due On;

  Case( 

    days late ≥ 120,

      "120 days or more";

    days late ≥ 90,

      "90 days";

    days late ≥ 60,

      "60 days";

    days late ≥ 30,

      "30 days" ) )

Make sure this field is unstored so it will recalculate for every report. Sort by this field using a custom value list to ensure correct sort order, make a report with “Subsummary when sorted by How Late” part, place the “How Late” field there, place invoice fields in the body part and you're done.

Edited January 24, 2008 by Guest
Used better field names.

[lizzie]

Just popped in to see if I had a reply to my post! On my way to gym... tis 6am here in Jo'burg, South Africa!

Thank you for the response. Will look at it in more detail upon my return!