Advances against an invoice
What's the secret on setting up a database for writing advances on an invoice?
I've got an invoice table, and invoice line table, bank table, bank account table, product table... I mean I've got a whole lot of tables at this point. So if a Vendor from the vendor table presents an invoice from the invoice table, and you want to give an advance, then what tables are we talking about here. At least I understand that when a check is written, that I need to write the script so that the opposite amount is reduced from the bank account table. In this case, when writing a check we're using the invoice lines (extended into process lines by a one to one relationship) to as the check lines. This means we can look at an invoice, and at it's invoice lines, and see which check a certain line was attached to.
Where I lose it, is, where and how does one work with advances. Are advances to be treated as item lines, as if they were another product?. If one assigns the open (unpaid) invoice lines to a check, then you see what the amount is, but if an advance was given (and if this is the balance check) then we need to deduct this previous advance from the check. I just can't seem to see clearly what table and table occurrence arrangement I'm going to need.