Next Available Number that includes missing gap numbers

[gemma-the-husky]

Just to clarify, these are not table ID numbers at all. These are unique drawing numbers. We have gaps because we've never really had a great system for managing drawings. I don't even know how large the gaps will be. It's certainly easier to ignore them but I'm investigating NOT ignoring them.

@gemna-the-husky - Why avoid DMax?

@plog - Technically these would be strings because of the need for leading zeros. An Example Drawing number would be "FD-00056-01"

Why not use a dmax?
As I said, If you actually want to restart the sequence at a higher number, you can't with dmax

The other thing relates to sequencing issues if you have a next number table or a dmax, The timing of obtaining the next number needs to be left to the very last stage. Otherwise a user can get a next number and then decide not to use it, and you still get a gap. Either that or two concurrent users might get the same number. Therefore you still need to manage the timing of the process to obtain the next number. If you use a table, you can lock and increment the next number in the table. If you use a dmax, you might need to re-do the dmax if the number you get has just been taken by another user doing the same process.

I think what @plog meant is that incrementing a string is tricky. You can't easily do a dmax is your references run "FD-00056-01", then "GS-00057-01". You need to store the drawing count as a number on its own.

 

[JMongi]

@theDBguy - Thanks for the added detail!
@arnelgp - You underestimate our lack of any business procedures. There is no list. We didn't even have a drawing number standard before I started.
@The_Doc_Man - Good advice. Believe me, I'm not stressing out about it. The only real thing I'm worried about is ensuring that there are no duplicates (which I can check for easily enough).
@MarkK - Thanks for the suggestion!
@gemma-the-husky - I'm storing each individual part separately. I was going to store the drawing number as a string for the leading zeroes because I didn't know about Format(). I'm now going to store it as a number which simplifies things. Your DMax vs Table analysis makes sense. Doesn't storing the next number in a table add extra data? It seems a bit like storing a calculated value. Where would I store this information? Here's my structure:

The "RootNumber" is the field in question. Our situation is a tad more complex because any drawings associated with a unit number Use the construction XX-0000-XX where the four 0 are the unit number. If it is a detail drawing NOT associated with a unit number, then the construction is XX-00000-XX. I use some logic on the form to put either the unit number or the next available 5 digit number in the RootNumber field.

 

[arnelgp]

There is no list

that's bad. everything should be documented.

 

[JMongi]

Indeed it should! Preaching to the choir here

 

[gemma-the-husky]

Next Number

Just have a tblNextNumbers
Add a row for NextDrawingNumber
Set it to whatever you want the next one to be.

Now when you want the next number, read the number from this field, and then increment the number.
There are techniques to lock the table before reading in the event that two users try to get a number simultaneously, although it's very unlikely, but that's why you want this process to be as efficient as possible.