This example code is provided by a colleague in Switzerland. It provides an alternative way of creating an automatically incrementing field value. The result of the code is to create an incrementing number that includes the current "year" as a prefix and then "-" plus a number that will automatically increase by '1'.

This will look like

2009-1
2009-2
2009-3

and so on

For example consider the Person entity.

Add a new field on the entity Person (pers_insurancenr)
Add this field on the screen (PersonBoxLong)

Go at the screen definition of this field and add the following code into the create script

now = new Date();
Query = CRM.CreateQueryObj("select max(pers_insurancenr) as Value from person");
Query.SelectSQL();
if (Query("Value") == null)
{
DefaultValue = now.getFullYear() + "-1";
}
else
{
TmpValue = Query("Value");
TmpValue = TmpValue.substring(TmpValue.indexOf("-") + 1,
TmpValue.length);
TmpValue = Number(TmpValue) + 1;
DefaultValue = now.getFullYear() + "-" + TmpValue;
}

This will only start when you are creating a new person.

I have discussed in a previous article the use of the StoredProc entry type that creates the automatically incrementing values for the fields "case_referenceid" and "soln_referenceid" that are found in the cases and solutions tables. For a full discussion of that technique and how it can be extended for use on custom entities please see the article "The Sage CRM StoredProc Data Type".