how to create a customer statement (1 Viewer)

[saudkazia]

i am trying to create a statement for my customers which basically will tally what each owes and how much they have paid.

i have an invoice and invoice payments table which has been linked via invoice.id and invoiceid

the invoice is also linked to the invoice_details table also via invoice.id and invoiceid.

for each invoice_id i have a one or more records in invoice_payments and invoice_details

now i can tally up all the payments for the customer regardless how many invoices but how do i tally up the invoice amount

structure

invoice
ID, idate, customerid, total (need this as a field due to being able to reference it - might change this to calculated via a query later on but i'll leave it like this for now), discount, note etc

invoice_details
id, invoiceid,description, productid, unit cost, qty

invoice_payments
payment_id,invoiceid,payment_type,payment_date, payment_amount, payment_mode

 

[isladogs]

You say you know how to get a total amount for each customer

Use the same method but filter by invoiceId instead of customerid