Sub-summary data embedded in body of an invoice

[SueD]

I have a client who has thrown me a curve ball...

Simple request... a posting at the bottom of his monthly invoice to his lawn care clients with 0~30, 30~60 and 90+ summaries for overdue accounts... not so simple!

because he only wants to bring up the current month's invoices, not all unpaid invoices...

I gave him the standard: script to find and print current invoices, script to find and sort all past due invoices and print individually by sub-summary aggregate...

Am I missing something?

[Oldfogey]

Try a self-join on Customer Number.

To make life simple, you probably need to create three new calc fields containing the invoice amount based on the date of that invoice.

Under_30 = Case(date-today < 30, Invoice Amt, 0)

ThirtySixty = Case((date-today) > 20 and (date-today) < 60, Invoice Amt, 0)

Your 0-30 field on your invoice is

Sum(Cust::Under_30) - Invoice Amt

and so on for the other two. You need to subtract the amount for the current invoice because the Sum() will collect all of the records in the relationship, including itself.

[SueD]

I have a self-join calc with CoName&CO.ID#&"N" (for not paid) as well as a portal with all the unpaid invoices and a subsummary with 30, 60 and 90 based on a calc of the # of days (Today - Date sent) out the invoice is, so that is fine, but the sub summary 30,60,90 summary fields dont show up unless all the records are present in the found set... : which he doesnt want to have to wade through when sending out this month's invoices... pant pant...