6 Replies Latest reply on Jun 25, 2013 8:25 AM by TomGrasmeyer

    Serial numbering

    TomGrasmeyer

      Title

      Serial numbering

      Post

           We are a print shop. I have developed a 'docket system' which has served us well over the past 3 years. It contains all the info about each job that goes through the shop.

           When a docket/job is started, it is given a four-digit job number (0000-9999), incrementing by 1 and then with a 'series' letter attached to it. So, job numbers look like this:"1234c" or "8745c". When we reach 9999c, we need the series to re-start into '0001d'. 

           Fiels I currently have:

             
      1.           docket number (0000 - 9999) <-- auto-entered number field, incrementing by 1
      2.      
      3.           docket series (c, d, e, etc.) <-- auto-entered series field (set to enter 'c' all the time)
      4.      
      5.           unique identifier (a hidden field that combines the docket number with the series so that we can have duplicate docket numbers but not within the same series (i.e. 1234c and 1234d are OK, but we cannot have two 1234c dockets)

            

           I want the docket system to flip over to 'd' series when '9999c' is reached, or to 'e' when '9999d' is reached, and so on. This is what I think I have to do:

             
      1.           set up a new field called 'current series'
      2.      
      3.           set up field 'docket series' to auto-enter the contents of 'current series'
      4.      
      5.           set up a calculation that will somehow check to see if 9999 of a given series has been reached, and if it has, then change the 'current series' field to the next alphabetical letter <--this is the step that has me stumped. How do I do this?

           I could probably change the series letter manually easily enough since a series lasts us about 2 years. But I don't know how long I'll be here and I want this system to do the work itself in case I'm not here…no one else in the shop knows how to work with Filemaker.

           Thoughts?

        • 1. Re: Serial numbering
          philmodjunk

               Is this a system where more than one user might be creating new jobs/dockets at the same time?

               Any time you move beyond the basic auto-etnered serial number for a unique identifier you have to use care in multiple user situations or you can get two records with the same identifier due to the users creating the new records nearly at the same time.

               For relationship use in your database, I recommend that you use a pure serial number field and not this unique identifier. You can have such a field in one of your tables so that you can continue to use it, but don't set it up as your primary key in relationships.

               A calculation field can convert the pure serial number into your existing unique identifier:

               Let (  [ S = serialNumberField ;
                          n1 = Mod ( S ; 10000 ) ;
                         n2 = Div ( S ; 10000 ) ] ;
                          Right ( "000" & n1; 4 ) & If ( n2 ; Char (  Code ( "a" ) + n2 - 1 ) )
                       )

          • 2. Re: Serial numbering
            TomGrasmeyer

                 Thanks. All relationships are based on something like this:

                 bvpdocketsystem::docketnumber = [othertable]::docketnumber 
                 AND bvpdocksetsystem::docketseries = [othertable]::docketseries

                 So the 'unique identifier' is not being used for relationships. It's strictly there to allow duplicate [docketnumber] field values but not within the same series. The serializing is happening with the simple auto-entered docket number field, the 'uniqueidentifier' field is just an auto-entry based on a calculation that looks at the other two fields (docket number and docket series). 

                 I will try the calculation you specified. It looks like a more elegant solution than what I had thought of. Thanks again for the help.

            • 3. Re: Serial numbering
              philmodjunk
                   

                        All relationships are based on something like this:

                   

                        bvpdocketsystem::docketnumber = [othertable]::docketnumber 
                        AND bvpdocksetsystem::docketseries = [othertable]::docketseries

                   And this is what I recommend that you NOT do. A single auto-entered serial number is much simpler and secure.

              • 4. Re: Serial numbering
                TomGrasmeyer

                     OK sorry I'm back to this. I think I need a more explicit explanation.

                     We create docket numbers in series. 0001a - 9999a, 0001b - 9999b, etc. Series 'c' should end sometime in July at the current rate.

                     My fields:

                       
                •           docketnumber: auto-entered serial number, increments by 1. Validates in range from 0 to 9999.
                •      
                •           docketseries: auto-entered as 'c' right now. Validates as member of value list 'docketseries' (a,b,c,d)

                     These fields are used in relationships to a number of other tables (where both docketnumber and docketseries must match a related record).

                     When we reach 9999c I need the docket system to roll over to 0001d. That means that the auto-enter serializing needs to restart back to 0001 and the docketseries needs to auto-enter 'd' now instead of 'c'.

                     Any thoughts? I'm a graphic designer, not a database guy. I know enough to have built this docket system and to be dangerous. Please make instructions plain.

                • 5. Re: Serial numbering
                  schamblee

                       It's recommend to use a generated serial number for Primary and Foriegn keys. (RelationShips - serialized field)   Your custom docket number would be use on your layout for tracking your customer / customer items (internal use not app use).  You don't want key field to be modified by user because this can lead to mislinked records and is just not good design practice. The other problem I see with your method is that It would be almost impossible to track prior orders because the customers key field is going to change with each new docket number.     

                  • 6. Re: Serial numbering
                    TomGrasmeyer

                         Thanks for your reply. I'm sorry, though…I don't quite follow. Keep in mind that I'm a graphic designer, not a database designer.

                         I am using a generated serial number…the field is set up as a auto-entered 'serial' number. See below. This number forms the primary key. The user is not able to modify this number. We've never had a broken relationship in any of the nearly 8,000 dockets we have made.

                         I need to know how to get the serial number fo roll over from 9999c to 0100d.