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.
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?
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!
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.
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 ]
Set Field [ table::numberSubField; $NextSerial ]
Show custom dialog ["Can't create additional job number"]
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!
Ditto on that RickWhiteLaw.