Special Serial Number Calculation & Scrip

[AlanP]

Hi everyone, I'm a little lost on even how to begin doing this. here is what I need to do:

- Need to add to a table (using a script) a 'block' of serial numbers.

- Serial numbers follow a standard convention, only the last two characters change

(i.e.: ABC123456, ABC123457, ABC123458 or ABC1234A1, ABC1234A2, etc.) Also on the alphas, it ends at 9 and restarts, eg.: ABC1234A9, next up would be ABC1234B1, etc.)

- I don't mind breaking up entering straight numbers (at the end) vs alphanumerics at the end.

- All must end at the final position. For example, Alphanumerics end at "Z9", numerics end at "99".

So...ABC123401 through ABC123499

Alphanumerics: ABC1234A1 through ABC1234Z9 (but in 9 incriments at the last character).

Any ideas on how to go about doing this?

The user would have to specify the first 7 characters of the serial number (it changes based on what they need to add to the database).

THANK YOU VERY MUCH!!!!

[comment]

This is quite confusing. It seems like the "ABC1234" part is completely irrelevant, and you just want to increment from "01" to "Z9" (which, I think, would be limited to 324 combinations). And what should happen after that?

And what does this mean?

I don't mind breaking up entering straight numbers (at the end) vs alphanumerics at the end.

Edited April 13, 2010 by Guest

[AlanP]

It's hard to explain. Basically we have specified serial numbers to enter into the database, lots of them, that have a particular structure and sequence.

The ABC1234 was just an example, because eventually it would be ABC1235, ABC1236, or even AAA1111, etc. it's almost like a prefix. The suffix to it would be the one that changes (these are "blocks" of serial numbers).

So ABC1234A1, ABC1234A2, ABC1234A3, ABC1234A4, ABC1234A5, ABC1234A6, ABC1234A7, ABC12234A7, ABC1234A8, ABC1234A9, Then it goes to the next prefix, ABC1234B1, etc....all the way through Z9 prefix. Then the 'block' of serial numbers end.

Because there is so many, it's hard to enter them all in manually. The user needs to specify the prefix (such as ABC1234) then allow the script to do A1 all the way through Z9.

Does that make it clearer or as clear as mud? ;-) It's harder to write out than actually show.

Oh, and the other comment. I was just saying I don't mind if there is TWO scripts. (one for A1 through Z9, and another for the straight numeric prefixes of 01 through 99).

Edited April 13, 2010 by Guest

[TheTominator]

To generate the sequence A1...Z9 you can use this formula.

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Int( (record_id - 1)/9) + 1; 1) & (Mod(record_id -1 ;9) + 1)

A number from 1 to 234 is in the field record_id.

If you are given a starting serial number, e.g. C7, you need to figure out where to begin counting in the above formula. (C7 = 25 so you would need to start at 26 to get C8.)

If "C7" is in SerialStart, the following formula will give you the number 25.

(Position("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Left(SerialStart; 1); 1; 1) - 1) * 9 + Right(SerialStart;1)

Edited April 13, 2010 by Guest
Added inverse calc

[comment]

Do you mean something like this?

Gen.fp7.zip

[AlanP]

I could cry right now...THANK YOU BOTH!!! Yes, that is exactly what I need!

How do you guys know all this stuff?!?! :

[AlanP]

Had another question,

When the script executes, the alpha's start at 1 instead of 0

So it should look like A0, A1, A2, A3, A4, A5, A6, A7, A8, A9, B0, B1, B2, B3, B4, B5, B6, B7, B8, B9, etc. However it's skipping the 0 (e.g. A0) and going straight to 1 (e.g. A1).

Also saw this on the numbers as well, it will start off with 01, 02, 03, 04, etc. instead of starting off with 00, 01, 02, 03, 04, etc. (that being said the numerics work fine for everything else...e.g. in the teens such as going to 10, 20, 30, etc., so 0's appear there).

[comment]

Well, originally you said:

...ABC1234[color:red]01 through ABC1234[color:red]99

Alphanumerics: ABC1234[color:red]A1 through ABC1234[color:red]Z9[

Anyway, see if the attached fits.

Gen2.zip

[AlanP]

Thanks Comment!

Wow...I did say that originally! I'm all over the place with this script requirements. But it works perfectly, thank you so much for all your time and assistance on this! It is greatly appreciated!