Jump to content

serial number by year

This topic is 7216 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi everyone,

I have a question (who doesn't):

I want to make a serial number field that increments by 1 every time a new record is created and resets itself when a new year starts. How can i do that?


[ January 16, 2002: Message edited by: nowayback ]

Link to comment
Share on other sites

First, I presume you are not using this serial number as a primary key for a record. If you are, then don't reset it of course.

Have a script that checks to see if the current date is the date on which you wish to reset the serial number. If it is, use the Set Next Serial Value script step and have it reset the field to whatever initial value you want it to have.

I'd be careful with this.


Old Advance Man

Link to comment
Share on other sites


Here is what I have done:

Make a Numberfield: "YearSerialNumber"

Make a Globalfield: "YearSerialNumberGlobal"

Make a Calculationfield: "YearSerialNumber2"

Then a script:

Set(YearSerialNumber) ; If(Left(YearSerial number;4)< Year(Today)or YearSerialNumber = "" ; Year(Today)&1 ; YearSerialNumberGlobal + 1)

Set(YearSerialNumberGlobal ; YearSerialNumber)

This make:

If the YearSerialNumber is empty you will have your first number.

Then it set the global field for use in next record.

If the first 4 digits (year) in the YearSerialNumber is lower than todays system year the script will set the new year and start with 1.

Instead of 1 you can add several zeros or whatever you want between the year and serial number.

You can also use only 2 digits for the year but then you need a additional field to get it work.



3 = Starts with 3 rd digit in 2002 = 0

x = Length of longest serial number: 20020001 = 3;6

If you always want 6 digits you can make follow calculation in the "YearSerialNumber2" field:

If(Length(YearSerialNumber)=5;Middle(YearSerialNumber;3;2)& "000" & Middle(YearSerialNumber;5;1); "" ) &

If(Length(YearSerialNumber)=6;Middle(YearSerialNumber;3;2)& "00" & Middle(YearSerialNumber;5;2); "" ) &

If(Length(YearSerialNumber)=7;Middle(YearSerialNumber;3;2)& "0" & Middle(YearSerialNumber;5;3); "" ) &

If(Length(YearSerialNumber)=8;Middle(YearSerialNumber;3;2)& Middle(YearSerialNumber;5;4); "" )

Good luck


Link to comment
Share on other sites

This topic is 7216 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.