1 Reply Latest reply on Aug 24, 2009 9:40 AM by philmodjunk

    Advances against an invoice



      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. 

        • 1. Re: Advances against an invoice

          You already have a table where each check written is a single record and all advances represent a check that is written?


          If so I'd add at least one field to that table to record the number of the related invoice the check is written against. The total amount of all checks with a given invoice number will give you the total to date disbursed against that invoice.


          If one check can represent multiple invoices, you'll need a Child table that breaks down each check into 1 record recording the sub amount for each invoice. Then all records of this child table with the same invoice number will give you the desired total.