6 Replies Latest reply on Dec 11, 2012 2:04 PM by Terri

    Rolling Serial Number

    Terri

      Title

      Rolling Serial Number

      Post

           Hi,

           I've got a field called Serial ID thatI have set to start at 6000 and add increment of 1. I need to make sure that when it reaches 6999 that it rolls back to 6000. Is there an easy way to do this?

        • 1. Re: Rolling Serial Number
          philmodjunk

               I would think that serial numbers would be unique--which wont' be the case if they "roll over" like this.

               If you use a script to create new records, the script can check the value of this field in the newly created record and if it is 6999 it can use set next serial value to reset the serial number back to 6000. But this might get a bit tricky to do in a database hosted so that multiple users might be creating records in this table all at the same time.

          • 2. Re: Rolling Serial Number
            Terri

                 I know this is deceiving, it is a serial number but it is being created for another application, not for the traditional serial number use that you see in a FM database. In fact, it might be easier to call it a naming convention. All records related to a certain "category" need a serial number that is in the 6000's. 

                 But you have a good point when other users are working in the same database. With that said, how can I make it user specific and also start over when they reach 6999. Is that even possible?

                 I've reached out to the user to gain clarification on this number/naming convention as your response leads to more questions than answers. Please weigh in while I gather more information. Thanks!

            • 3. Re: Rolling Serial Number
              philmodjunk

                   What do you mean by "user specific"? Each user has their own series of values from 1 to 6999?

                   If you just need to ensure no values past 6999, you can define a validation rule that rejects all records greater than 6999, this will force them to try again if that happens and they should get what they need on the second try.

                   But if you have multiple categories and a unique number series for each category, then you will need to use a different approach in order to manage all the different number sequences.

              • 4. Re: Rolling Serial Number
                Terri

                     What I thought was simple has now been complicated - but only because you ask great, necessary questions. I appreciate that.

                     Hopefully I do a better job of explaining. 
                      
                     Each user will be creating work orders for specific records. By the use of scripts and calculation fields, when they push a button a "workorder ID" will be generated for that record. Within that ID is a serial number that must be in a the 6000 range. Sometimes a specific record will need more than one work order. So that user needs to have workorder IDs for that record that are in sequential order. So...
                      
                     User 1 - First Record
                     Record requires 3 Workorders
                     Workorder ID 1: 0024-AD12US-6000
                     Workorder ID 2: 0024-AD12US-6001
                     Workorder ID 3: 0024-AD12US-6002
                      
                     User 1 - Second Record
                     Record requires 2 Workorders
                     Workorder ID 1: 0016-AG13US-6003
                     Workorder ID 2: 0016-AG13US 6004
                      
                     User 2
                     Record requires 1 workorder
                     Workorder ID 1: 0036-AG12US-6001
                      
                     User 2
                     Record Requres 2 workorders
                     Workorder ID 1: 0024-AF12US-6002
                     Workorder ID 2: 0024-AF12US-6003
                      
                     As you can see, they can use the same number (so it isn't really a serial ID) but it is important that as the individual works on their specific orders the serial part of the ID goes in sequential order. So it would be user specific.
                • 5. Re: Rolling Serial Number
                  philmodjunk

                       I realize that this is probably being driven by a client's or employer's requirements but it really makes no sense to number them that way. I strongly recommend that you not use this "work order ID" as a key field in relationships. Keep the field in your database as a regular text field and use a serial number field as your primary key for your work orders. You probably have that detail figured out already.

                       I think it's just a typo on your part, but user 2's sequence started at 6001 and User 1's at 6000. I will assume that this difference is not significant.

                       You'll need a related table where you have one record for each user such as:

                       MainRecord---<WorkOrders>-----WOSerialNumbers

                       MainRecord::__pkMainID = WorkOrders::_fkMainID
                       WorkOrders::UserID = WOSerialNumbers::UserID

                       The 4 records in your example would be records in Main. The 8 different work orders would be 8 records in WorkOrders and a number field in WOSerialNumbers would be incremented each time a new WorkOrders record is created via a script and this script can "roll" values back to 6000 when 6999 has been reached.

                       You'll have to figure out what value to use for UserID and how to put that value into WorkOrders::UserID, one option might be to define it as an unstored calculation field: Get ( AccountName ).

                  • 6. Re: Rolling Serial Number
                    Terri

                         This is definitely not a key field in any relationships and really has no use in this database other than to create an ID that is then imported into another program (this ID is driven by another user's requirement).

                         Your solution makes sense. I need to clarify with my external user that I understand the need of this number before I implement. Be prepared for new questions just in case they make it even MORE complicated (is that possible?). 

                         Thanks!