This must be easy, but I just can't figure out the best way to do it. I'm creating an invoicing db, and I want to assign the next sequential invoice number in a field. Like this:

1. New record created

2. invoice_number field is populated with the next available invoice number (largest number from this field in the entire data base + 1)

Is there an easy way?

Just set your Invoice Number field up as a serial number. You can choose whatever start number you wish and choose "Increment by 1" and that should do it.

Hi,

Also remember from an accounting standpoint, all sequential invoice numbers must be present to produce a good record trail.

Set the invoices so that they cannot be deleted, rather place a status field in the invoice table and set the status for the ones not used as voided with a zero amount. The control of a sequential number is great, if it is unique, required, cannot be changed nor deleted and all numbers can be accounted for. I would also add a void date, voided by & reason for voiding fields to record some history on the invoice status change.

Your accountant will thank you if you are ever audited.

My2Cents,

Tim

Also remember from an accounting standpoint, all sequential invoice numbers must be present to produce a good record trail.

This is only true AFTER it becomes a true Invoice which usually doesn't happen until the moment of sale or shipment (presented to a customer or printed). We create many 'invoices' a day which are deleted before finalized; they are actually proposals until moment of sale. Once an invoice is presented (in whatever fashion) then yes, lockdown should occur. But the 'holes in Invoice numbers theory' originated in the days when it wasn't an Invoice until posted and that's why accountants were against deletions.

If you void an invoice, remember to omit the stock items from your product-count summaries. I actually handle this differently than simple void because these voids keep appearing and must be hidden. Instead, I write the Invoice and it's LineItems to mirror tables for holding and delete them from the true tables. I prefer NOT to move data around but it is much easier than constant filtering in relationships (or omitting in finds) etc. I do this because 1) my move scripts are tight, 2) the 'proposals' aren't extremely important; more for reference, and 3) it's easier than omitting them throughout my solution. We NEVER delete a finalized invoice - we create an adjustment for offset and 'return' the lineitems to stock. Because once they are finalized, they are a sale - there IS no backout allowed.

Auditors don't mind holes if they know that the process is controlled properly.

LaRetta

Edited December 7, 200619 yr by Guest