7 Replies Latest reply on Nov 20, 2013 5:35 PM by keywords

    alphabetical serial numbers

    dvdlghtn

      Hi everyone

       

      I have built a data base that will eventually list well over 1 million items (maybe even several millions) with each item needing a unique serial number.

       

      I am hoping that there is a way to create serial numbers which also includes an alphabet sequence, such as - starting at AAA001 which would change once the numerical number reached 999 and then it would become AAB000 until the next thousand which would then become AAC000, and so on throughout the alphabet.

       

      Being a newbe I am at a loss as to how to create such a serial number, or, indeed even if it is possible at all. If not, then there maybe there is another solution entirely that I am unaware of. I am simply trying to find a simpler system of serial numbers so that I do not have to keyin very long strings of numbers for looking up items etc as the list of items gets bigger.

       

      Any suggestions?

       

      Thanks for any help.

       

      David

        • 1. Re: alphabetical serial numbers
          RubenVanDenBoogaard

          Hi David,

           

          I would use a field to auto enter a serial number and then convert that number in the alphabet field using the formula:

           

          Let([

          s="ABCDEFGHIJKLMNOPQRSTUVWXYZ";

          th=Int(number/1000);

          r=Mod(number;1000);

          l1=Int(th/26^2);

          l2=Int((th-l1*26^2)/26);

          l3=Int(th-l1*26^2-l2*26)

          ];

           

           

          Middle(s;l1+1;1) & Middle(s;l2+1;1) & Middle(s;l3+1;1) & Right("00"&r;3)

          )

           

          Hope that helps,

           

          Best regards,

           

          Ruben van den Boogaard

          Infomatics Software

          ruben@infomatics.nl

          • 2. Re: alphabetical serial numbers
            LyndsayHowarth

            Hi David,

             

            I suspect that the best way for you to tackle this is via a series of separate calculations being stitched together as 1. Divide the Letters from the numbers to begin with and create the rules.

             

            You may also find a Custom Function on Brian Dunning's site www.dunning.com which does close to what you want and may be able to adapt.

             

            To calculate the NEXT, you have to know what the LAST one used was. With numbers, FileMaker does this for you with Serial numbers. Make use of this.

            If you enter Text before it...like AAA... it will increment nicely to end up with AAA999.... but the next number will be AAA1000... so that is not the answer alone.

             

            If you have a serial number with no text... but make it a text field and start the numbering at 001.

            Then in a second field calculate that :

             

            -----

            case(

            serial number < 1000; "AAA";

            serial number < 2000; "AAB";

            serial number < 3000; "AAC";

            serial number < 4000; "AAD";

            <snip etc>

            serial number < 26000; "ABA";

            serial number)

            & right(serial number, 3)

            -----------

             

            I hope that makes sense...

             

            There are many ways to do this sort of thing... This is just one which is simple enough to help you understand how functions work.

            The case() function just goes down the list until it finds a case that matches... and the last one says that if no conditions are met it should just put the seriel number in. (That is a problem for you to solve... as you will not want it to also put in the last 3 digits of the serial number... as the right() function is doing.

             

            - Lyndsay

            • 3. Re: alphabetical serial numbers
              GaryTate

              I'd just like to add that you should never be keying these strings in if they are primary keys. These should never be seen or used by the user.

               

              I think you might need 2 numbers, primary key (sequential number would be fine for this) and a product ID, which could follow the methods above. This Id would then be displayed and possibly keyed in.

               

              Gary

              • 4. Re: alphabetical serial numbers
                LyndsayHowarth

                Cool Ruben!!!!

                I got as far as 26 and realised I was too tired.

                Well done!

                 

                - Lyndsay

                • 5. Re: alphabetical serial numbers
                  dogden

                  Hi David,

                   

                  I modified ruben's code, his original code didn't loop very well (ie AZ999->BA000). Also, I only needed 2 letters instead of 3, since 2 letters provides 676000 variants.

                   

                  Let(

                  [s="ABCDEFGHIJKLMNOPQRSTUVWXYZ";

                  th=Int(Raw SN/1000);             %  indicator of thousand’s place or l1

                  r=Mod(Raw SN;1000);            % indicates 1-999, “mod” makes it loop

                  l1=Mod(Int(th/26);26);              % increments every 26000, “mod” makes it loop

                  l2=Mod(Int(th-l1*26^2);26)       % increments every 1000, “mod” makes it loop

                   

                  ];

                  Middle(s;l1+1;1) & Middle(s;I2+1;1) & Right("00"&r;3))

                   

                  Damon

                  • 6. Re: alphabetical serial numbers
                    deninger

                    If your solution does not require "sequential" serial numbers but only unique serial numbers, then set the serial number of a field to calculation and within that calculation use the FM Funciton Get ( UUID )

                     

                    This function gives a unique serial number in the format of: F9D789E3-37D9-4EB7-941A-CF33161360F9

                     

                    From the FM docs:

                    Get(UUID) returns text representing a Universally Unique Identifier (UUID).

                    When using Get(UUID), FileMaker Pro will returns a unique 16-byte (128-bit) string. For example, you can use this function  as an Auto-Enter Calculated value to generate a unique ID for a record when it is first created.

                    Note:  Unstored calculations will return a new string each time Get(UUID) is evaluated.

                    • 7. Re: alphabetical serial numbers
                      keywords

                      I agree with deninger; I believe you would be better off using Get(UUID) if the field in question is to be used as a match field. I am certainly impressed with Ruben's calc (and Domon's modified version) and can see a place for it as a product ID, say (which may be what David requires), but I reiterate the general caution against making FM match fields anything human readable or meaningful.