serial number by year

[nowayback]

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?

GreetZ

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

[Steven H. Blackwell]

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.

HTH

Old Advance Man

[per]

Hi,

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.

YearSerialNumber2:

Middle(YearSerialNumber;3;x)

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

-Per