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.
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!
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.
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 RecordRecord requires 3 WorkordersWorkorder ID 1: 0024-AD12US-6000Workorder ID 2: 0024-AD12US-6001Workorder ID 3: 0024-AD12US-6002User 1 - Second RecordRecord requires 2 WorkordersWorkorder ID 1: 0016-AG13US-6003Workorder ID 2: 0016-AG13US 6004User 2Record requires 1 workorderWorkorder ID 1: 0036-AG12US-6001User 2Record Requres 2 workordersWorkorder ID 1: 0024-AF12US-6002Workorder ID 2: 0024-AF12US-6003As 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.
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::__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 ).
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?).