7 Replies Latest reply on Dec 7, 2009 9:20 AM by philmodjunk

    Serial Numbering



      Serial Numbering



      I would like to have the option to create a new JobID number when i create a new record. In my current data base i have my JobID's automatically created when i create a new record. However, sometime i need to create my own number based on the job type. 


      Is there a way go give me an option to use the next number in the sequence or use my own number when i create a new record?



        • 1. Re: Serial Numbering

          And how will you insure that "your own number" will be unique?


          Generally, adding "meaning" to a serial number used as a primary key causes trouble sooner or later.


          If you must do this, you might want three fields.


          Field1: Serial Number

          Field2: Your Number

          Field3: Calculation field: If (IsEmpty(Field2); Field1; Field2)


          Use Field 3 in your reports and you'll have the ability to override your auto-entered serial number with a different value.

          • 2. Re: Serial Numbering

            Thank you for responding.

            Currently, my serial numbing is pretty simple, 09-1001, 09-1002... Each number represents a new Job. However, sometimes we get a new job that is sort of a Sub-Job of the main job. So, we number it 09-1001.1, 09-1001.2 and so on. This works great for reports because when i look up the main job, all the sub jobs fall under it. I also have my JobID field set up to only accept unique numbers.


            What we are doing now is creating a new job then just changing the number. So, we end up with some holes in our numbering system and I want to eliminate that. 


            Forgive me, but im kind of new to this, but would your solution work for this and do I make a script?

            • 3. Re: Serial Numbering



              You wouldn't have to do additional scripting with Phil's method above.  For your reports, if you are using scripts you would just need to change the sort to use Field3 instead of your current serialized field.  Also make sure you add Field2 to your data entry layout, as this is where you will put your JobID override number.


              Hope this helps!

              • 4. Re: Serial Numbering
                   That does seem to help a little but, I am still stuck on how to write the script to add my JobID's in numerical order, on demand. 
                • 5. Re: Serial Numbering

                  You have to capture the previous value and add 0.1 (going by your example). There are several ways to "capture the previous value" depending on you layout design and even then you have a problem: What if the previous value was 09-1001.9? Your next value would be 09-1002 and this would violate your numbering scheme.


                  If the current record stores the previous value, this script will work:


                  If [ Right(table::NumberSubField; 2) ≠ "0.9" ]

                     Set Variable [$NextSerial ; Value: let ( N = table::NumberSubField ; left ( N ; 3 ) & Right ( N ; Length ( N - 3 ) ) + 0.1 ]

                     New Record/Request

                     Set Field [ table::numberSubField; $NextSerial ]


                     Show custom dialog ["Can't create additional job number"]

                  End If

                  • 6. Re: Serial Numbering

                    All I have to add to the already great replies is that, if this serial number is a matchfield in ANY context, then its creation should be automated and not left up to the user, even if the only user is  you! I speak from experience on this!



                    • 7. Re: Serial Numbering

                      Ditto on that RickWhiteLaw.