This is a skinny file with the bare minimum to illustrate a payment scheme.
It seems that you want to sum up payments by invoice.
This file does not sum by customer, but that is a simple extention By adding a relationship between customer to Invoices or customer to payments or both.
test Payments.fp7.zip 7.6 K
Heya Chuck,thanks for the sample file, but here is my issue.
- IineItems is where i store my LineItems total which is a summary of total of extendedPrice on a portal.
- Orders are just my name, but these are really iNvoices, The Invoice Total is really the LineItems total summary field. OK~~~
- In my payments table i would like to add the payment date, customer,etc later on, i am just trying to figure Basic understanding of how to do this with the above mentioned tables currently.
- Perhaps i just need to add a date to my payments field and add paymentID to current OrderID but i cant maintain the InvoiceTotal and Balance amounts to remain connected, perhaps because they are not related. I am still looking into that.
- Again what you did is great for simplicity sake, however, i just dont know how i will implement within my system although i am still trying.
Are your Orders and LineItems two separate tables? If not, they should be.
Once your Orders are in their own table you do not need to worry about summary values being correct, and you can easily apply Payments to any Order. (Payments should also be in its own table).
When you create a new Payment record, you will need to be able to related it to the Order -- and use the related payment amounts to see whether or not anything is still due on that Order.
Advanced: If you want to be able to 'split' Payments across multiple Orders, then you should really create a join table between the Payments and the Orders records -- so that you can apply fractions of any Payments to any Order.
You should however be able to log your Payments correctly against your Orders as long as you have Orders, LineItems, and Payments in three separate tables, and relate them to each other approriately.