Field 1 = 2016
Field 2 starts from 0010 and increments itself of 10
what have to appen after 20169990 ?
if we get to 20169990, i get a raise, as that's a boatload of new students!
From 20160000 to 20169990 there will be 1000 records.
Anyway, a TEXT field ( auto-enter a serial ) can start from 0000 ( or 0010 ) with increments of 10.
But there will be problems when you'll chage the year ( Field 1 ), if you'll use the calculation: Field 1 & Field 2
ok...so for each year, i would like to have the ability to start over at 0000, so 2017 would start at 0010. and 2018 would have 0010. this solution wouldn't create that as an option.
The problem isn't the calculation ( that coud be easy to create ), but your request of unique ID.
Is your solution hosted ?
A script can reset the next serial value setting back to zero, but Raybaudi is adding some excellen cautions here. This system has a built in upper limit. Also, any time you use a field like this that is both a unique identifier AND it includes additional information, you have a field that should not be used as the primary key to link it to other related tables in your database. The risk you run is that future changes could break existing links to your records. The fact that this may appear very unlikely to you doesn't mean it can't happen.
If you need such a numbering system to keep the boss/client happy with you or to support existing identification methods, add this field to the main table only, but use a normal, separate serial number field as your primary key. Anywhere you need to display that value, you can just add this field to your layout and you can use it for searches and sorts as well. Only you need know that this field is not the actual value linking your related tables together.
I think i understand the caution. the PrimaryID is completely separate to identify the record and all of its relationships to other tables and DB's. This ID is something that would be exported, to fit in to our existing systems for billing etc. it would not have any other relationship other than an identifier.
Sorry if i didn't make that clear enough in the original post. i can certainly see the problems that would cause.
Good to know you aren't using this as primary key.
You can set up a script that runs when the file is opened that checks the date. When the current date indicates that this is a new year, set next serial value can be used in the script to reset the serial number field back to zero. If the script stores the date the date each time this is done in a record in a table somewhere in your system, it can check this date before doing making the change to make sure it only does this update once a year.
" reset the serial number field back to zero"
reset the serial text field back to "0010"