On 03/09/18 07:29, 'Christian Giesen' chris@... [firebird-support]
wrote:

>   NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;

Christian ... there was many years ago a very nice article on this very
problem. The whole reason that will not work is when two or more people
are adding invoices at the same time! My own system generates ticket
numbers which ideally should not have gaps but if two counters press
'add ticket' at the same time they both see the same 'NextInvNo' ...
I've added a couple of solutions which almost get it right, but just
occasionally ... like once every few months ... we get two tickets with
the same number created within 0.0001 second of one another - what are
the chances of that - obviously not zero :(

Now a unique index should prevent that but in my case the ticket numbers
start at 1 every day across multiple sites so the index has to combine
several things and I never did work out who to handle the clash event.
In your case you can make the index unique, but need to handle if two
invoices are created at the same time ...

--
Lester Caine - G8HFL
-----------------------------
Contact - https://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - https://lsces.co.uk
EnquirySolve - https://enquirysolve.com/
Model Engineers Digital Workshop - https://medw.co.uk
Rainbow Digital Media - https://rainbowdigitalmedia.co.uk