Alpha Numeric Serial Number
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.
Try this expression:
Let (N = Div ( SerialNum ; 1000 ) ; Middle("ABCDEFGHIJKLMNOPQRSTUVWXYZ"; N ; 1 ) & Right ( "00" & Mod ( SerialNum ; 1000 ) ; 3 ) )
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 )
The right function adds in the needed leading zeroes.
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.
Answered my own question.
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)
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.
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)
Really clever and it worked great. I'm always learning something new with this amazingly cool program.
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 ) )
Retrieving data ...