7 Replies Latest reply on May 11, 2012 8:45 PM by user14348

# Alpha Numeric Serial Number

### Title

Alpha Numeric Serial Number

### Post

Looking for a slick way to produce an alpha numeric serial number where the alph charcter is in first position followed by a 3 digit sequential numeric.

• ###### 1. Re: Alpha Numeric Serial Number

Put the first character in a text field: FirstChar

Use an auto-entered serial number field of type number for the rest: SerialNumb

Then use a calcuation field to combine them for your Alphanumeric serial number:

FirstChar & Right ( "000" & SerialNumb ; 3 )

PS. This is not a good field to use as the Primary Key, but can be OK as a "label" field that people use to find and identify records in your database.

• ###### 2. Re: Alpha Numeric Serial Number

Created a serial number field starting with 1000.

Created a second serial number field for the alpha numeric SN with the following calculation

Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ";Int(Serial Number/1000);1) &
Middle(GetAsText(Serial Number);2;1) &
Middle(GetAsText(Serial Number);3;1) &
Middle(GetAsText(Serial Number);4;1)

• ###### 3. Re: Alpha Numeric Serial Number

I don't think that will produce a letter past I (the 9th letter) as your expression only returns the left most digit 1 - 9 in the first term of the calculation.

The above expression could be simplified as:

middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ";Div (Serial Number ; 1000);1) &
Right (GetAsText(Serial Number) ; 3)

But that still produces a series where the first letter will only be A through I. That may be sufficient for you. If so, I'd shorten the text in quotes to just A through I to avoid confusion in the future. If you want the entrie alphabet, you'll need an expression that returns a number from 1 to 26.

• ###### 4. Re: Alpha Numeric Serial Number

Oh shoot.  I really wanted A-Z.  Any suggestions?  A-I isn't sufficient. I want the alpha charater in position 1 followed by a 3 dig numeric.

I tried to modify something in the FMP answer database (ID 3260) which said the following...

This calculation provides a base 36 serialnumber, starting with numeric and then alpha, which will provide 1,295 unique serialnumbers with only two digits. These serialnumbers will range from O1 (zero one) to ZZ. You will still need a regular numericserialnumber field, which does not need to appear on any layout, assigning standard numericserialnumbers to each new record.

Define the following fields:

SerialNum = a number field with an Auto-Enter value of Serialnumber with next value as 1 starting with 1

Serial (calculation with result of text) =

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", Int(SerialNum/36)+1,1)  & Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ",  Mod(SerialNum,36)+1,1)

• ###### 5. Re: Alpha Numeric Serial Number

Try this expression:

Let (N = Div ( SerialNum ; 1000 ) ; Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; N ; 1 ) & Right ( "00" & Mod ( SerialNum ; 1000 ) ; 3 ) )

• ###### 6. Re: Alpha Numeric Serial Number

Really clever and it worked great.  I'm always learning something new with this amazingly cool program.

• ###### 7. Re: Alpha Numeric Serial Number

Nice calc Phil but wouldn't you need to add 1 to N to prevent a repeat of the A series for serials between 1 to 1999.

Without the '+ 1' you would get A001 where the serial is 1 and again for 1001, A002 for 2 and again for 1002 etc.

Changing it to this would prevent the repeat and work for the serial range 1 to 25999:

Let (N = Div ( SerialNum ; 1000 ) + 1 ; Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; N ; 1 ) & Right ( "00" & Mod ( SerialNum ; 1000 ) ; 3 ) )

Regards

Andrew