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
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