I use two different fields, an auto-generated serial number as my Primary Key (which doesn't need to be consecutive, or even visible, as it's only used as a unique and consistent ID), and another field where I can calculate the next sequential number, which I use as the main quote number that everyone can see and use.
I call them __pk_QuoteID and _QuoteNumber, respectively (the underscores just helps me find them quicker when the fields are sorted alphabetically).
So I can delete quotes as I please, and when I create a new quote the next number is calculated by using a self join relationship:
__pk_QuoteID <──┤X├──> __pk_QuoteID
and the calculation:
c_NextQuoteNumber (from Quotes) = Max ( QuotesSelfJoin::_QuoteNumber ) + 1
Hi Phil - thank you for your concise reply. I will give it go.