You need an Invoices "self join" that matches to the records you've specified. Such a relationship uses a second occurrence of invoices so that you can relate the invoices table to itself.
In manage | database | relationships, select Invoices and click the duplicate button (2 green Plus Signs). This creates a new occurrence of the same table. It does not duplicate the table. You can then double click the new occurrence to name it something more descriptive than Invoices 2, such as: OutstandingInvoices.
Then you can set up this relationship:
Invoices::debtornumber = OustandingInvoices::debtornumber AND
Invoices::const61 > OustandingInvoices::InvOutstandingDays AND
Invoices::cDate12Mo > OustandingInvoices::Invoicedate AND
Invoices::InvoiceID ≠ OustandingInvoices::InvoiceID
const61 would be calculation field that returns the constant value: 61.
cDate12Mo would compute your "past 12 months" date. It could be computed a number of ways depending on what you mean by that:
Exactly 12 months from today's date:
Let ( today = Get ( CurrentDate ) ; Date ( Month ( today ) + 12 ; day ( today ) ; year ( today ) ) )
12 months from today's date, adjusted to the first of that month:
Let ( today = Get ( CurrentDate ) ; Date ( Month ( today ) + 12 ; 1 ; year ( today ) ) )
Exactly 12 months from the invoice's date:
Let ( d = Get ( InvoiceDate ) ; Date ( Month ( d ) + 12 ; day ( d ) ; year ( d ) ) )
12 months from invoice's date, adjusted to the first of that month:
Let ( d = Get ( InvoiceDate ) ; Date ( Month ( d ) + 12 ; 1 ; year ( d ) ) )
If you use a calculation with Get ( CurrentDate ) in it, make sure to make this an unstored calculation rather than stored.
Then, Sum ( OustandingInvoices::InvoiceAmount ) will produce the total you want.
thxs for the quick answer and your comments solved the problem completely. Also the concept of a self-join is now much clearer to me.