10 Replies Latest reply on Aug 2, 2011 1:13 PM by philmodjunk

# How to generate an 8 digit ID number...

### Title

How to generate an 8 digit ID number...

### Post

I would like to generate a unique 8 digit ID number that starts with a specific 4 number set (year of graduation, and in this example, field 1).  I would like the last four digits of the ID number generate automatically, from creation, in increments of 10 (field 2), so the final number would be 20160010, 20160020, etc.  or, {(field1,field2)}.

My first thought was to have two separate fields, then a concatenation field bringing the two results together, but I can't figure out how to force field two to be four digits in length...thoughts?

thanks!
Jesse

• ###### 1. Re: How to generate an 8 digit ID number...

If:

Field 1 = 2016
Field 2 starts from 0010 and increments itself of 10

what have to appen after 20169990 ?

• ###### 2. Re: How to generate an 8 digit ID number...

if we get to 20169990, i get a raise, as that's a boatload of new students!

• ###### 3. Re: How to generate an 8 digit ID number...

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

• ###### 4. Re: How to generate an 8 digit ID number...

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.

• ###### 5. Re: How to generate an 8 digit ID number...

The problem isn't the calculation ( that coud be easy to create ), but your request of unique ID.

• ###### 6. Re: How to generate an 8 digit ID number...

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.

• ###### 7. Re: How to generate an 8 digit ID number...

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.

• ###### 8. Re: How to generate an 8 digit ID number...

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.

• ###### 9. Re: How to generate an 8 digit ID number...

" reset the serial number field back to zero"

reset the serial text field back to "0010"

• ###### 10. Re: How to generate an 8 digit ID number...

Yep.