Create a report which shows data from multiple date ranges

January 24, 200818 yr

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!

January 24, 200818 yr

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, 200818 yr by Guest
Used better field names.