1 2 Previous Next 22 Replies Latest reply on Mar 27, 2013 12:34 PM by LaRetta

    Serial Auto Numbers

    MarkNZ

      Hi all,

      Is it best practice to set the serial auto enter with several zeros before the record number

        • 1. Re: Serial Auto Numbers
          Malcolm

          Is it best practice to set the serial auto enter with several zeros before the record number

           

          No. The other day I received an invoice numbered 1. We were the first customer.

           

          There may be some business logic that says an ID must be, for example, eight digits. In that case, do it.

           

          Some people start the numbering at 1000 or 1000000. It gives a little bit of padding and it makes sure that all the low numbers sort the same in number and text sorting. There are lots of little reasons like that.

           

          If you start your numbering with zero padding make sure it is a text field. Otherwise you'll lose all your zeroes.

           

           

          Malcolm

          • 2. Re: Serial Auto Numbers
            RayCologon

            MarkNZ wrote:

             

            Hi all,

            Is it best practice to set the serial auto enter with several zeros before the record number

             

            Hi Mark,

             

            Yes, in general, it is.

             

            The reason is that if either a) your serial numbers reside in a text field, or b) there is a chance they will ever be exported to a text format at any time during the life of the solution, you'll then find that numeric values will not sort correctly without leading zeros. Eg in a text (alphanumeric) sort, 1,234 will sort before 22 - whereas 01234 will sort after 00022.

             

            Meanwhile, even within FileMaker, there is reason to consider storing serial numbers as text, since this allows them to be used in multi-key arrays without the vagaries of defining relationships between fields that use incompatible indexing principles (while this will work in many cases, it is definitely not best practice). In which case unless you have leading zeros you will forgo usable sorting on the serial field/s.

             

            In short, the 'cost' of including leading zeros is minuscule, whereas there are definitely potential costs and/or risks in not doing so.

             

            Regards,

            Ray

            ------------------------------------------------

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            Email: cologon@nightwingenterprises.com

            http://www.nightwingenterprises.com

            ------------------------------------------------

            • 3. Re: Serial Auto Numbers
              RayCologon

              Malcolm wrote:

               

              If you start your numbering with zero padding make sure it is a text field. Otherwise you'll lose all your zeroes.

               

               

              Hi Malcolm,

               

              This is not correct. A number field can indeed be defined to accept serial values with leading zeros, and they will be stored and will not be lost, either when the field is displayed (assuming the 'general' or 'as entered' data formatting masks are used) or when it's referenced, including in a calculation (except where the data-type of the calculation result is implicitly or explicitly numeric).

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              Email: cologon@nightwingenterprises.com

              http://www.nightwingenterprises.com

              ------------------------------------------------

              • 4. Re: Serial Auto Numbers

                Ray Cologon wrote:

                 

                A number field can indeed be defined to accept serial values with leading zeros, and they will be stored and will not be lost

                 

                Hi Ray!  It is very nice to see you around again!

                 

                The leading zeros may not be lost but they certainly can be transformed into trouble if left as number field.

                 

                Hi Mark,

                 

                As Ray says, leading zeros in number serials are safe whilst in FM but unfortunately FM data does not stay there and number serials with leading zeros are handled inconsistently once outside of FM and it is these differences which can bite you if the data is then re-imported and you attempt to match on keys.  Heck even FM is (seemingly) inconsistent … mer preserves leading zero but csv and Excel drops them.

                 

                Now… matching a number 1 to a number field with 00001 isn't a problem for FM … it will still match and import just fine.  The problem lies in human nature and changing things over time.  If you now change that serial to text, any csv or Excel files  (without leading zeros) will no longer match nor import - just keep that in mind. It could catch you off guard.  However the reverse is not true … if you have a text serial with 000001 and 3 years later you decide you want number serial of 1, you can change it to a number and all old files will continue to import fine.

                 

                If I needed to export a sorted text serial, I would create a field at that time; it just doesn't happen often.  Serial numbers are faster, have smaller footprint and because they are number, they consistently return to FM as a number, well, except for the floating decimal issue, LOL. Whether you should switch to text with leading zeros or leave as number without leading zeros only you can decide but number with leading zeros (in my estimation) is not safe because of this potential issue so I suggest that you step one direction or the other.  And remember that, if you change to text, consider your recover process and migration files.

                1 of 1 people found this helpful
                • 5. Re: Serial Auto Numbers
                  Lemmtech

                  I like using letters and leading zeros so I can keep track of all keys so invoices might be INV00001 and Customers CUS00001 and so forth. Makes is much easier when you have multuple foreign keys in a table

                  • 6. Re: Serial Auto Numbers
                    MarkNZ

                    Hi Malcolm Thanks for your reply..appreciated

                    • 7. Re: Serial Auto Numbers
                      MarkNZ

                      Thanks Ray You make very good points..cheers

                      • 8. Re: Serial Auto Numbers
                        MarkNZ

                        Hi LaRetta, Thanks os much for your imput..Your answers are all very informative  worthy of understanding ...so thanks

                        • 9. Re: Serial Auto Numbers
                          MarkNZ

                          Hi Lemmtech,

                          You reminded me that yes putting letters in front is another method..so thanks much apprecaited

                          • 10. Re: Serial Auto Numbers
                            Malcolm

                            Ray Cologon said

                             

                            Malcolm said

                             

                            If you start your numbering with zero padding make sure it is a text field. Otherwise you'll lose all your zeroes.

                             

                            Hi Malcolm,

                             

                            This is not correct. A number field can indeed be defined to accept serial values with leading zeros, and they will be stored and will not be lost, either when the field is displayed (assuming the 'general' or 'as entered' data formatting masks are used) or when it's referenced in a calculation.

                             

                            That's interesting to know.  It's obviously susceptible to transformation as I've lost the zeroes and my response was to handle these as text fields rather than number fields. LaRetta's post makes me think that import/export might have been the problem. So, while I'm happy to be corrected, we should ensure that the MarkNZ is aware that there are conditions under which the zeroes can evaporate.

                             

                            Malcolm

                            • 11. Re: Serial Auto Numbers
                              deninger

                              While I understand all of the arguments for and against, it is my policy to have serial numbers defined as a number. If I want to ensure sorting integrity, I start my serial numbers at (wait for it...)

                               

                              10000000000001 (ten trillion and 1)

                               

                              Now I have effectively 89 trillion records before I run into a new leading zero problem. For all of my solutions, I doubt I will be using FM Pro for a DB approaching 100 trillion records.

                              • 12. Re: Serial Auto Numbers
                                MarkNZ

                                Hi deninger.. good point you make..wouldnt this large number look visually obstrusive.. or is it possiable just to show less zeros initially

                                 

                                cheers

                                • 13. Re: Serial Auto Numbers
                                  robwoof

                                  Hi Mark,

                                  I think in this case, you would use the "really big numbers" as an internal ID for the sake of data relationships and not expose it to the user. Therefore the size of it is irrelevant when considering display.

                                  R

                                  • 14. Re: Serial Auto Numbers
                                    Lemmtech

                                    While I agree it's generally not necessary to show the key fields to users although some times I do I usually only use 5 digits since most of my solutions don't go beyond 100000 rows anyway sometime I use 6 to get me to 1M rows which is my case is very rare. I 'm pretty sure it just keeping going anyway but I don't like to make my numbers any larger then they need to be.

                                    1 2 Previous Next