Serial Numbers That Reset Automatically Every Year

Image (used with permission) by Zoe Peleti.
http://www.zoe-aurora.com

It’s come up in conversations a few times amongst my colleagues and I now… “What exactly is the best way to generate serial numbers that will reset themselves every year?”.

My approach is a rather simple one, requiring no scripting at all, and it shouldn’t introduce any potential for record-locking problems either. This technique will allow for the same serial number to get recycled though. What this means is that if the newest record gets deleted, its serial number could be given again to the next record created. Because of this gotcha, I want to urge anyone considering this technique to ensure that their cascading deletes are properly configured. Without taking this precaution, there will remain a possibility that orphaned records will get adopted by newly created records.

To begin, let’s establish the business rules we’re going to use, and keep it simple by saying that the serial number is going to be the 4 digit year followed by a 6 digit serial number. (Example: “2013000001″). Now that this has been established, let’s get into the details.

This technique will require the creation of 2 new fields, and at least 1 relationship.

We’ll be dealing with 2 tables:

• Widgets –> this is the table that will have the new serial number
• Defaults –> this is a table that will only ever have one record

First, we’ll create the field that will act like a serial number (in the Widgets table). In my example, it’s called “serial_number”. We’ll keep it as a plain text number for now. Once it’s been created, it’ll be a good idea to create a new record in this Widgets table, and to manually fill it in with your starting value “2013000001″.

Next, we’re going to want to set up a relationship between the two tables. Its sole purpose will be to allow us to know what the biggest serial number to date is. You can use whatever criteria you want to in order to relate the widgets table to the defaults table. If your solution is simple, and you don’t expect it to have a ton of records, it might be simplest to use the Cartesian Product [X] relationship between the two of them. Your relationship will need sorting on the Widgets side of the relationship, so let’s sort by “serial_number” in descending order.

Now that this relationship has been created, it should be easy for us to determine what the biggest serial_number used so far is. We will be determining exactly what that value is using a new calculation field in the Defaults table (“next_serial”). Not only is our calculation going to get us that number, but it’s also going to determine whether it’s going to add 1 to that value, or if it’s going to reset the value for the new year.

Here’s the formula I use:

Let
(
	[
		_possibleNumber = Widgets::serial_number + 1;
		_possibleYear = Left ( _possibleNumber ; 4 );
		_currentYear = Year ( Get(CurrentHostTimeStamp) );
		_yearMatch = _possibleYear  ≥  _currentYear 
	];
	If ( _yearMatch ; _possibleNumber ; _currentYear & "000001" )
)

All the heavy lifting is done.
Now, all that remains is changing your Widgets::serial_number field to get its value from the Defaults::next_serial field. An auto-enter calculation might be the simplest way to do this.

Items of note:
Get ( CurrentHostTimeStamp ) was used so that the server’s year is used in evaluations. This could be helpful if you deal with different time zones, and you expect records to be created at the end of the year.