1 2 Previous Next 18 Replies Latest reply on Feb 13, 2012 9:43 PM by ariley

    Serial Number Generation

    tmilas

      My Fellow FMers,

       

      I've been mid level guy. However this time I stumbled on something and it seems can't go forward.

      Here is my dilemma:


      I created DB with records of products/items.

      My company has the strict rules for serial numbers:

       

      1VAL12B123ADVAC

       

      1VAL is always there, 12 means a year of manufacture, B means a second month, 123 is an actual serial number. ADVAC is a type of product.

      12 - year can be obtained from "date created" field (auto-enter today's date), so is the month B from the date created (calculation).

      Serial number starts for new month at 100, still OK and sequential number is done by adding 1.

       

      My problem is that I don't know how to do a test if for SN it is next number or it should be 100 if the month is new.

       

      Any suggestions?

       

      Much obliged (in advance).

       

      Tom

        • 1. Re: Serial Number Generation
          ariley

          The best thing in this case (in my humble opinion) is to script the serial number creation, as opposed to auto-enter calculation. You have more control over what gets set and how. 

           

          So, the script can check whether today is the first day of a month and if it is, start serial numbering from scratch.

           

          Hope this helps. 

           

           

          Regards,

           

          Agnes B. Riley

          FileMaker 10 and 11 Certified Developer

          http://www.zerobluetech.com

          ZeroBlue Technology Solutions

          where your data goes to work

           

          Member, FileMaker Business Alliance

          Member, FileMaker Technical Network

           

          FileMaker Advanced 11.3 on Mac OS X 10.6.8

           

          "Imagination is more important than knowledge..."

             /Albert Einstein/

          • 2. Re: Serial Number Generation
            tmilas

            Thank you Agnes... that was quick.

            Any example for the script for this.  I'm not good with scripting.

             

            Tom

            • 3. Re: Serial Number Generation
              ariley

              I'll create a demo file and post it on my blog this weekend. I know this has come up before, and I'm sure it'll resurface again. 

               

              Follow @agir on twitter to be notified or check blog.zerbluetech.com.

               

              Cheers,

              Agi

               

               

              Regards,

               

              Agnes B. Riley

              FileMaker 10 and 11 Certified Developer

              http://www.zerobluetech.com

              ZeroBlue Technology Solutions

              where your data goes to work

               

              Member, FileMaker Business Alliance

              Member, FileMaker Technical Network

               

              FileMaker Advanced 11.3 on Mac OS X 10.6.8

               

              "Imagination is more important than knowledge..."

                 /Albert Einstein/

              • 4. Re: Serial Number Generation
                KylePutzier

                Attached is a sample file that illustrates a way to create complex, month based serials. It requires no scripting, just a relationship. This technique is useful for creating a few thousand serials in any given month, after that, it may be to slow.

                 

                SerialTest.fp7

                 

                Note: This technique is not failsafe if used in a multiuser environment. See Michael's response and sample file.

                 

                Kyle

                • 5. Re: Serial Number Generation
                  johnhorner

                  i have a similar script for calculating serial numbers and had a similar test when i first created it.  however, it will fail if you don't happen to create a new product on the 1st day of the month (maybe it's a sunday, a holiday, or just a slow day, for example) but rather, create the first product for that month on the 2nd (or 3rd, etc) day of the month.  perhaps you already test for that some other way.  i worked around it by testing for a count of records in the current month rather than for the 1st day of the month.  if the count = 0, then i know this is the first record and i reset the serial number to 1 (...or 100 in your case).  sorry if this is redundant....

                  • 6. Re: Serial Number Generation
                    comment

                    Run the atached demo to see why this is not a good idea.

                     

                    See also:

                    https://fmdev.filemaker.com/message/68557

                    • 7. Re: Serial Number Generation
                      RayCologon

                      KylePutzier wrote:

                      Note: This technique is not failsafe if used in a multiuser environment. See Michael's response and sample file.

                       

                      Hi Kyle,

                       

                      Correct. And it's worth noting that scripted allocation of serials can suffer from similar issues unless the script is built around a strategy for conflict management.

                       

                      Regards,

                      Ray

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

                      R J Cologon, Ph.D.

                      FileMaker Certified Developer

                      Author, FileMaker Pro 10 Bible

                      NightWing Enterprises, Melbourne, Australia

                      http://www.nightwingenterprises.com

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

                      • 8. Re: Serial Number Generation
                        IreneVassos

                        Hi Tom,

                         

                        It was not clear in your original post if you are using this serial number as a key field. If so, might you want to consider keeping it an internal serial number for the purposes of your company protocol (whichever method for generating it you choose) but having a truly unique serial number as a key field, e.g. a UUID.  Ray Cologan has a free custom function generator for this (see other posts regarding key fields/serial numbers). This way you can insure that your relationships are solid while maintaining the company serial number for identification purposes. Remember the best practice that true key fields should not have "meaning" that you will use to parse later because the basis for that meaning can, and probably will, change over time.

                         

                        Irene

                        • 9. Re: Serial Number Generation
                          tmilas

                          Thank you Michael, and all of you trying to help me with this.

                           

                               However multiple user failure may be not that bad because I limit new record creation to one user at the time since the software exists on the floor computer and only one person at the time can create a new record. The file is on the server and I with admin privs can have access as a second user, so there is no risk.  Anybody else from the company (I'm pushing FM at work) would have read-only access from their logins.

                           

                          Also, Michael, the new month S/N starts with 100 and has 3 digits... so somewhere there I have to add 100 to new real S/N.

                           

                          Still hoping for that magic script from someone, because this (or rather my own lack of FM knowledge) just simply drives me crazy.

                           

                          Keep it lit, All...

                           

                          Tom

                          • 10. Re: Serial Number Generation
                            comment

                            See if something like the attached could work  for you. It runs a script on startup to check if the month  has changed since the last time the file was opened. If so, it resets the serial back to 100. Note that this assumes you are not running continuously during the night - otherwise you will need to find another way to run the script, e.g. as part of creating a new product.

                             

                            The System table should be limited to single record.

                            1 of 1 people found this helpful
                            • 11. Re: Serial Number Generation
                              RalphLearmont

                              Tom,

                               

                              Here's a demo file which shows a way of detecting when the next month comes along.  No scripts are involved; rather it uses a relationship which looks back to see when the first of a new family of Month-Year records begin, and uses that record in conjunction with the new record to calculate the next "categorized" serial value.  Rather than using a count-based method, it relies on a normal serial number.

                               

                              Regards

                              Ralph

                              • 12. Re: Serial Number Generation
                                comment

                                RalphLearmont wrote:

                                 

                                Using this method, if you delete earlier records, you will always get a higher "unused" number (as distinct from a count-based sequence number).
                                • 13. Re: Serial Number Generation
                                  RalphLearmont

                                  Thanks for pointing that out Michael.     You'd be good as a proof reader...   ):

                                   

                                  I've made a change and I now believe all is well.  That is, there's no longer a reliance on the first record of a family, so it can be deleted.

                                   

                                  Regards,

                                  Ralph

                                  1 of 1 people found this helpful
                                  • 14. Re: Serial Number Generation
                                    comment

                                    RalphLearmont wrote:


                                    You'd be good as a proof reader...   ):

                                     

                                    I hope not...

                                     

                                    Anyway, here's a way to generate a duplicate: delete all records, open a new window, create a new record, do not commit yet, switch back to the the original window and create a new record.

                                    1 2 Previous Next