Successive records

[jrRaid]

I have a table with invoices and a field with the outstanding balance for each invoice.

I need a field (calculation) a sort of 'customerflag', when the balance on 3 successive invoices of a given customer exceeds a given amount.

I found a way to have it with a script (search/found set/loop/flag), but I wonder if there is a way without user interaction (no button click to activate script)

TIA

[BobWeaver]

Assuming you have the following fields:

CustomerID - A unique field identifying the customer

InvoiceNumber - A unique field identifying the invoice in sequential order (ie, a serial number of some sort)

InvoiceAmount - The total amount of the invoice

Create a selfjoin relationship called "AdjacentInvoices" with the following two parameters:

CustomerID = CustomerID

InvoiceNumber <= InvoiceNumber

Have the relationship sort by InvoiceNumber (descending).

Then you can create a new CustomerFlag calculated field with this formula:

Let([

n=Count(AdjacentInvoices::CustomerID);

Amount2=GetNthRecord ( AdjacentInvoices::InvoiceAmount ; 2 );

Amount3=GetNthRecord ( AdjacentInvoices::InvoiceAmount ; 3 );

TotalAmount=Case(n<3;0;InvoiceAmount+Amount2+Amount3)

];

Case(TotalAmount>500;"Warning";"")

)

[jrRaid]

Perfect.

Thank you.