The biggest issue that I've seen when people try to get sequential invoice numbers is getting it when they first start entering the data. You don't need it then. You don't need it until you save the data. So, enter the data, save the header, get the invoice number at that time. Then save the detail items and do the calculations.

This brings up the next question, "How do I get the invoice number?". IMO, it should not be the primary key. Use a table that has only one record and one column, the last used invoice number. USE the table, FLOCK() it, increment then number and store in both memory and the table, UNLOCK the table. Don't do anything else between those steps. It will be fast and should produce invoices in sequential order and not duplicates.

>I'm asking this on behalf of a friend here, who thinks his written english wouldn't get his problem across.
>
>There's a requirement that invoice numbers should be unique and continuous, i.e. no gaps at all. So this requires a mechanism which would guarantee no two users get the same number, and that no numbers are skipped.
>
>Presently, he's using a cursor adapter, wraps the whole thing in a transaction, has a table with counters (a dbf). He assumes that the invoice number would be acquired and written at the end of transaction (so if anything goes wrong while saving the parent record and line items, it doesn't even ask for a new number). This should avoid error 1585.
>
>Problems: the number of items may be large; calculation of totals may be complicated (rebates, taxes, transportation etc) and may take some time. Stock should be up-to-date after the transaction (i.e. whatever was sold should not appear as available) and there may be popular items which may appear on many invoices entered on different workstations at the same time.
>
>The trouble is that the record in the table with counters may be locked by someone else; he's getting errors 130 (can't rlock()) or 1585 (update conflict). So he's introduced a 15 times loop, wherein he does a tablerevert() and refresh() of the counter table and tries to rlock() again, waiting for a free pass. Now if he'd lose the loop and just issue a "system busy, try again" message (i.e. introduce an user-timed delay until retry), the users would probably want his head on a platter.
>
>Is there an elegant way to handle this?