Since this is a POS, (Point of Sale) system, is this the case that invoices are always paid in full at time of sale? Are there partial payments? (Half on my credit card, half in cash...)
How have you structured your system to record payments? (Or is setting up that table and relationship the first thing we need to do here?)
How have you structured your system to record payments? (Or is setting up that table and relationship the first thing we need to do here?) Think setting up table and relationship would be a good idea. What i have now is lineItems table connecting to customers & products and lineitems has foreign keys for customers & a portal for products.
I have no payment type and would like to choose payment at the end of placing all my products on the current invoice
After all the items have been accounted for , i need to do a payment amount, less discount(if any), and deposit(if any) then leave
a BALANCE amount. After that is done, i would like to choose the payment type (cash, credit, check, charge) and then print the invoice with
all of that information on it.
Hope that breaks it down really clearly now PhilModJunk.
Normally a POS system has Clients, Invoices, LineItems and Products as the basic invoicing tables.
I don't quite follow how th line items table would have links to clients nor to a payment method as that would apply to a record in the invoices table--a table you don't identify here.
Since you describe a balance field here, as well as a deposit field (How is that different from a partial payment?), it would appear you need an payments table where you can track invoice totals and payments against them made by the client both at the time of sale and afterwards.
Does that match what you have in mind?
i have customers --> Orders --> LineItems -->Products currently ( forgot the Orders table)
I have an invoice total before payment now in my lineitems table. which is a calculation based on extended price after calculating
teh qty * price.
Just dont know when i should check payment type and when to put it in system but i guess tracking the invoice totals and payments make at time of sale and afterwards sounds like a good approach, but keeping everything simple is always tricky.
How would i start besides makeing a payments table and creating a relationship between payments & orders table?
You've got the right idea. A portal to a payments table on your orders layout can be used to track payments made against that invoice. A total of that portal can be subtracted from the order total to compute a balance. You can base this portal's relationship by InvoiceID. You'll likely also want to set up a relationship so that you can see an "account" for any specified client that shows all payments and invoices and a runnting balance to show what they currently owe.
One complication this business practice creates for you is that a customer could send in a single payment to pay off multiple balances due on different orders. Unless you expect the user to manually split up this payment amount and log it as several different payments (a good way to introduce errors), you'll want to set up a join table between payments and invoices so that a given payment can be logged against multiple orders as well as multiple partial payments being logged against a single order.