Solved divide one payment with many invoices

[Safari]

Dear All

i want to know how to divide one payment with 3 invoices for example :

my customer have 4 invoices :

1st invoice on 1-1-2022 with total 1000 $
2nd invoice on 15-1-2022 with total 2000 $
3rd invoice on 1-2-2022 with total 100 $
so all total invoices amount is 3100 S

then the customer paid only 1500 as a part of his due
i want to decrease the payment from the 1st invoice with 1000$ then decrease the payment rest of payment from the 2nd invoice with 500$
and when i make report i need to appear only :
2nd invoice with 1500 $
3rd invoice with 100 $

how can i do all of them

i attached the database to edit on it

 

[CJ_London]

your tables are not designed correctly - customer name should be in a separate table. and you don't appear to have a primary key for invoices unless that is supposed to be it. And your data fields are text when they should be numeric. So I won't be editing it since there is so much wrong with it.

But I can tell you you have a many to many relationship between invoices and payments so you need a third table to reflect this relationship

tblPaymentAllocation
PaymentAllocationPK
PaymentFK
InvoiceFK
Amount

 

[arnelgp]

here is a demo.

 

[Pat Hartman]

database is corrupt