Payment of several unpaid invoices

Nick said:
The tables are: Customers, Invoices, Receipts with the corresponding relationships between them.

Can you upload a test DB?

Hi Nick

Your receipt is linked to the invoice. You need a many to many Allocations table that sits between your Invoices and Receipts tables.  Change the Balance to be the sum of the Allocations Amount.

sum(Allocation.Amount)

Your button then loops through the invoices that still have a balance and creates an Allocation record and sets the amount to the minimum of the balance and the amount remaining to allocate.

This code will work from your receipt record. It's untested but gives you a flavour of the solution

let a := this;
let b := Amount;
for c in (Customer.Invoices[Balance>0] order 'Invoice No') do
    if b > 0 then
        let d := min(b,c.Balance);
    (create Allocation).(
        Invoice := c;
        Receipt := a;
        Amount := d
    );
    b := b - d
    end;
end

If you find you have overpayments from time to time you may wish to move this button out to the customer so that it also loops through each receipt that still has a balance.

Hi Nick

Sorry, rushed that a bit. It should be Change the Balance to be the TOTAL less the sum of the Allocations Amount

TOTAL - sum(Allocation.Amount)