8 Replies Latest reply on May 18, 2010 5:08 PM by LaRetta_1

    Serial Number From 2 Text Field

    Lioncross

      Title

      Serial Number From 2 Text Field

      Post

      HI 

      I am kind a new user here , I am still learning, so I hope my question wont mind you guys.

       

      I want to create auto serial number which the Serial will be 

       

      GJP@XX00001

       

      @= First letter of Category of Product

      XX = Product Name (LEN 2 Character)

       

       

      Thanks in Advance

        • 1. Re: Serial Number From 2 Text Field
          LaRetta_1

          Hi Lioncross,

           

          Auto-enter serial numbers should be meaningless.  This primary key needs to be used to connect your relationships throughout your system.  If you use something with meaning and that meaning ever changes, it will break your relationships.  So stick to auto-enter serial numbers.  If you wish to display this specialized number for users to see then create another calculation (result is text) which concatenates them together as:  "GJP" & Left (categoryproduct ; 1 ) & Left ( ProductName ; 2 ) & serial :smileyhappy:

          • 2. Re: Serial Number From 2 Text Field
            Lioncross

            Thanks to Laretta again, well the serial number field will be like this ?

             

            autoserial (field with auto serial)

            serial_number = "GJP" & Left (categoryproduct ; 1 ) & Left ( ProductName ; 2 ) & autoserial

             

            using 2 field to make this ?

             

            • 3. Re: Serial Number From 2 Text Field
              Lioncross

              I did that

               

              "GJP" & Left (categoryproduct ; 1 ) & Left ( ProductName ; 2 ) & autoserial

               

              When I Create New Records

              its automaticly create the serial number first without waiting the category field and the productname field being filled.

               :smileysad:

              • 4. Re: Serial Number From 2 Text Field
                LaRetta_1

                "GJP" & Left (categoryproduct ; 1 ) & Left ( ProductName ; 2 ) & autoserial

                 

                This concatenation (which is only for display and never to be used as keys to bind relationships), should be type calculation and not text with auto-enter calculation

                 

                If straight calculation then it will adjust as people enter the rest of the information.  And if someone changes CategoryProduct or ProductName then this calculation will change accordingly.

                • 5. Re: Serial Number From 2 Text Field
                  Lioncross

                  Thanks for the reply , Its works.

                   

                  My next question is ,

                   

                  IF

                   

                  I have Item

                   

                  1. Ipod Shuffle   (Category - Media Player)   the item_code=GJPMIP0001

                  2. Mac Book (Category - Note Computer)    the item_code=GJPNMA0002

                  3. Ipod Nano (Category - Media Player)       the item_code=GJPMIP0003

                   

                   

                  Which I want to set the serial number sorted like this 

                   

                   

                  1. Ipod Shuffle   (Category - Media Player)   the item_code=GJPMIP0001  < first serial for the Media Player with first name contains IP >

                  2. Mac Book (Category - Note Computer)    the item_code=GJPNMA0001 <first serial for the Note Computer with first name contains MA>

                  3. Ipod Nano (Category - Media Player)       the item_code=GJPMIP0002 <second serial for the Media Player with the first name contains IP>

                   

                  this will be really help for me , Thanks


                  • 6. Re: Serial Number From 2 Text Field
                    LaRetta_1

                    I do not understand.  Your auto-enter serial could not have more than one 0001 if created as I suggested.  For display, you can combine your three regular fields ( auto-enter FM-generated serial number, product Category and Product Name) but you would sort by the pieces used to create that serial.

                     

                    Sort first by your primary key (the auto-enter FM-generated straight serial) ascending and then by the ProductName ascending.

                     

                     

                    • 7. Re: Serial Number From 2 Text Field
                      Lioncross

                      Auto Enter FM Generated serial number ? :smileysurprised:

                      I do not know this ....

                      • 8. Re: Serial Number From 2 Text Field
                        LaRetta_1

                        Then let's walk through it again. :smileyhappy:

                         

                        Create a field called ProductID.  I thought you already had an auto-enter serial as text (describing 00001) so I provided a calculation with that in mind.  But since you don't even have one yet, it is a good time to create it properly.  Make your auto-enter serial type NUMBER.  The click Options (bottom right).   You will see tabs at the top and the tab that opens is the auto-enter tab.   Third item down is Serial Number (on creation) so type a single 1 in that field.  Now every record in increment from 1 forward.  Use this new ProductID for all your relationships ... don't leave home without it. :smileyvery-happy:

                         

                        Now for your display ...

                         

                        You really could just simply display your Category and your Product Name and that is what I suggest.  Then simply sort by your Product Name.  When you sort (and if you don't specify a second field for sorting) then result will be sorted by product and then in natural order of creation.  What this means is that it will display as:

                         

                        Serial-----Product

                        1...............Ipod Shuffle

                        3...............Ipod Nano

                        2...............Mac Notebook

                         

                        I realize I keep shying away from providing a 'serial' like you request; it's because you will begin to depend upon it and use it when it should  shouldn't be used.  The serial number means nothing - you can't even use it to 'count' how many Media Players (Category) and you shouldn't.  You already have everything you need to know everything you need.

                         

                        Okay, for DISPLAY ONLY, then you can create a calculation (result is text) with:

                         

                        SerialIncrement ( "GJP" & Left (categoryproduct ; 1 ) & Left ( ProductName ;  2 ) & "00000" ; ProductID )

                         

                        ...  but you still won't want to sort by this text calculation because it won't sort as you indicate you wish it to.  You will still want to sort by serial and then Product Name.