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

    Alpha Numeric Serial Number

    DavidLake

      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
          philmodjunk

          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.

          • 2. Re: Alpha Numeric Serial Number
            DavidLake

            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)

            • 3. Re: Alpha Numeric Serial Number
              philmodjunk

              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
                DavidLake

                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
                  philmodjunk

                  Try this expression:

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

                  • 6. Re: Alpha Numeric Serial Number
                    DavidLake

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

                    • 7. Re: Alpha Numeric Serial Number
                      user14348

                      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