Serial numbers are usually used to enter unique values into a field. If you restart the series back to 6000 this will not be the case. Are you sure that you want to do that? What would be the purpose?
It can be done. An OnRecordCommit trigger, for just one example, could perform a script to detect that the value is 7000, change the value to 6000 and then set the next serial value to 6001.
Maybe Serial ID is the wrong term. This field will contain a number used by the finance department for billing purposes. The records that I'm creating must have an ID that is between 6000 and 6999 to be recorded in the right account. The field needs to fall anywhere within that range. Although this explanation may seem like I could just use 6000 for all records, as records are created, the sequential number will somewhat "group" them together. For example: Style # 123456 will have 4 records associated with it. The IDs will be 6000, 6001, 6002, 6003. Another Style #678910 has 3 records associated with it and could have IDs 6010, 6011, 6012.
So in the end I need a field that will generate an ID anywhere between 6000 and 6999. What is the best way to do that?
That still can result in two entries with the same ID value--not generally a good idea in database design or in accounting.
Are values from 6000 to 6999 the only possible values in this field or will you have other number ranges for other accounts?
Please note that unsorted records will automatically appear in the order in which they were created and, if necessary, a separate, hidden auto-entered serial number can record the order in which the records are created. Thus you might only need an Account ID number with the value 6000 in it.
Well, here's a fun new twist. After conferring with my group (I'm just as frustrated and confused as I'm sure you are) I have another element to consider. It will require a more in-depth explanation.I'm in the process of trying to do what we call "automate shot note creation." This involves 6 fields that are either portals or drop-down menus that will eventually populate one field (the shot note). These fields consist of (field name / example of data):Record one:Style Number - 123456Category - 0043Media Code - A813USShot ID - 6000Shot Type - FShot Variation - aShot Note = 123456-0043-A813US-6000-FaRecord two:Style Number - 123456Category - 0043Media Code - A813USShot ID - 6000Shot Type - FShot Variation - bShot Note = 123456-0043-A813US-6000-FbRecord three:Style Number - 987654Category - 0023Media Code - AJ13USShot ID - 6001Shot Type - FShot Variation - a
Shot Note = 987654-0023-AJ13US-6001-Fa
Notice how the serial ID is the same for the same style number with different variations (variation a and b) but if it is a whole new style number, then that Shot ID number needs to generate a new 6000 number. Unfortunately I don't even know where to start with this. I hope it is possible or else this is one element that will need to be manually entered and I'm trying to avoid that.
What is the significance of this number? (what does it identify?)
Why do variations a and b need separate number sequences?
Why do you need all of this data in a single field? (For printing and display purposes, the data in "Shot Note" can be displayed without merging the data into a single field.)
Each record identifies a photograph of a product that needs to be taken. Variation a and b indicate that there is a front view and a side view. So they require the same shot note but the variation at the end changes. By keeping the ID number the same the finance group only counts cost for the first variation (we have determined that having the model turn so we can shoot the back does not require additional cost).This 6000 number serves a couple of purposes - it identifies it as an internet photograph so the finance department can bill it to that specific group. It needs to be unique for each style number in order to prioritize workload (records entered first should be handled first).I need this information in a single field because it is created in FileMaker and then imported into one field in another program. In fact, the shot note portion I describe here is only part of the component...there are other parts that will need to concatenate into one field to import into this other program, I'm just working one step at a time. The other components are a bit more complicated and will probably be another post on the forum.I hope that helps.
This is a very key, and yet still unanswered question: Will you ever have values in Shot ID that are NOT in the 6000...6999 range of values?
Does this have to be a number in this range to correctly interface with other systems?
Off hand, it looks to me like you have one table where you need two related tables.
Generate the Shot ID in a table based on Style Number and use a related table for the a and b variations. If there is only an a variation, you have just one record in this related table. If both a and b shots are taken, you have two records in this related table.
You'd then print and export data from a layout based on the related variations table, but your relationship will allow you to include data from the table based on Style Number.
At this time (and for the long range future) this database will only house internet photography - so there wil never be other values that are not in the 6000...6999 range.
Does this have to be a number in this range to correctly interface with other systems? - this number does not affect programming - it is simply a workflow and financial indicator.
I understand what you are saying about the two tables and had a feeling we were probably heading in that direction. I'll try to work something out and come back if I have questions. Thanks for leading me through this painful process.
Then why does it have to be a number in this range?
I realize that you are getting requirements handed to you, but part of the design process is to carefull, politely challenge the assumptions of the people making those requirements. It's easy to lock yourself into a "secret decoder ring" type of thinking that modern relational database design often makes completely unecessary.
A two field approach, a text field that says "internet" and an auto-entered serial number that is never "reset" would seem to serve the same purpose in a manner that is easier to implement and less cryptic to the average user.
Note that a script to reset the serial numbers must be used with care in a multi-user system or you may get two recently created records with exactly the same ID number.
Point taken. I've questioned this ID practice ever since I've been asked to develop this DB but I'm dealing with some very stubborn that have blocked any sort of creativity (or simplicity) I may have. I'll see what we can come up with.