I used to do customization for a great accounting software that sold more than 150 thousand copies in the United States. In their manual, they suggested to create IDs for your customers, vendors or products using the following criteria: 3 letters and 2 numbers. You should take the first letter of each word of the name to encode, and add a sequential 2-digit number according to the availability in your database. I never ran out of IDs, no duplicates, no conflicts using that criteria.

The software was written in FoxPro, and we had to create manually the IDs, which was a pain in the neck and time consuming. So I wrote a huge code to generate the IDs automatically when entering a new record. In 2000 I was far from Foxpro and already a ColdFusion developer for 3 years, so I decided to create a custom tag using the same principles of my old code. Later I wrote a component which I have been using in the company I work for since 2002.

You may download the component, instructions and a test template here.

The code will generate the ID based on the words in the argument name. It will take the first letter of each word and will append a serial number to it. If the name has less words than the number of characters you choose for the argument numChars, then it will take the following letters from the last word in the name, for example:
John B. Doe ==> JBDxx
John Doe ==> JDOxx
John ==> JOHxx
Jose da Silva ==> JDSxx
Jose Silva ==> JSIxx

The main function will receive the following arguments:

• Datasource : string, required, your datasource
• TableName : string, required, your table
• keyColumn : string, required, the column that will receive the generated ID
• charSize : string, optional, number of alphabetic characters (letters), default = “3”
• numSize : string, optional, number of numeric characters (numbers), default = “2”
• padChar : string, optional, character used to left padding, default = “0”
• appendNumber : boolean, optional, append numbers to the code (numSize), default = true
• insertRecord : boolean, optional, insert a blank record with the generated ID only (useful for multi-user), default = false
• resultUpper : boolean, optional, return the result upper cased, default = true

Here is a sample code to test it:<cfscript>
customerName = "John B. Doe"
cf = createObject("component", "IDGen").init(
datasource="myDatasource"
, tableName="myTable"
, keyColumn="ID"
, insertRecord=true);
CustomerID = cf.newID(customerName);
</cfscript>
Here is the component:<cfcomponent name="IDGen" displayname="IDgen">
<!---
Author: Ricardo Parente
Date: March 2000
Updated: July 2007 (converted from custom tag to component)
--->
<cffunction name="init" access="remote" output="false" returntype="Any" >
<cfargument name="datasource" type="string" required="false" default="myDatasource" hint="Replace this with your datasource" />
<cfargument name="tableName" type="string" required="false" default="myTable" hint="Replace this with your table" />
<cfargument name="keyColumn" type="string" required="false" default="companyCode" hint="Replace this with the column for the code" />
<cfargument name="charSize" type="string" required="false" default="3" />
<cfargument name="numSize" type="string" required="false" default="2" />
<cfargument name="padChar" type="string" required="false" default="0" />
<cfargument name="appendNumber" type="boolean" required="false" default="true" />
<cfargument name="insertRecord" type="boolean" required="false" default="false" />
<cfargument name="resultUpper" type="boolean" required="false" default="true" />
<cfscript>
this.datasource = arguments.datasource;
this.tableName = arguments.tableName;
this.keyColumn = arguments.keyColumn;
this.charSize = arguments.charSize;
this.numSize = arguments.numSize;
this.padChar = arguments.padChar;
this.appendNumber = arguments.appendNumber;
this.insertRecord = arguments.insertRecord;
this.resultUpper = arguments.resultUpper;
return this;
</cfscript>
</cffunction>
<cffunction name="newID" access="remote" output="false" returntype="Any">
<cfargument name="name" type="string" required="true" />
<cfscript>
arguments.name = reReplaceNoCase(arguments.name,"[^A-Za-z 0-9]","","all");
code = "";
pos = 1;
item = 1;
for (x=1; x lte this.charSize; x = x+1){
if (item gt listLen(arguments.name, " ")){
item = item - 1;
pos = pos + 1;
}
elem = listGetAt(arguments.name, item, " ");
if (pos eq 1){
code = code & left(elem,1);
} else {
code = code & mid(elem, pos, 1);
}
item = item + 1;
}
code = reReplace(code,"[ ]{2,}", "0", "all");
if (len(trim(this.padChar))) {
for (x = len(code)+1; x lte this.numSize; x = x+1){
code = code & left(this.padChar, 1);
}
}
if (this.appendNumber){
cnt = 1;
sCode = code & numberFormat(cnt, repeatString("0", this.numSize));
// loop to find a non existing code
while (getCode(sCode)){
cnt = cnt +1; // increment counter to assemble the new code
sCode = code & numberFormat(cnt, repeatString("0", this.numSize));
}
} else {
sCode = code;
}
// if add record option is true, call function to add a blank record with the ID only.
if (this.insertRecord) addBlankRecord(sCode);
if (this.resultUpper) sCode = uCase(sCode);
return sCode;
</cfscript>
</cffunction>
<cffunction name="getCode" access="private" output="false" returntype="Any">
<cfargument name="sCode" type="string" required="true" />
<cfquery name="get" datasource="#this.datasource#">
select #this.keyColumn# as myColumn
from #this.tableName#
order by #this.keyColumn#
</cfquery>
<cfreturn (listFindNoCase(valueList(get.myColumn), arguments.sCode) gt 0) />
</cffunction>
<cffunction name="addBlankRecord" access="private" output="false" returntype="Any">
<cfargument name="code" type="string" required="true" />
<cfset success = true />
<cftry>
<cfquery name="add" datasource="#this.datasource#">
insert into #this.tableName# (
#this.keyColumn#
) values (
'#arguments.code#'
)
</cfquery>
<cfcatch type="database">
<cfset success = false />
</cfcatch>
</cftry>
<cfreturn success />
</cffunction>
</cfcomponent>