ColdFusion - List Functions

I thought I would pass on a little trick to those programmers who also have to design the data structures behind their applications.

If you plan on using table primary keys (usually a sequential number) as something to use in sub-queries or list structures consider starting you numbers at 1000 instead of 1.

Typically when creating a table I start the primary key with 1:

CREATE TABLE SomeTable (
     TableID int NOT NULL PrimaryKey IDENTITY(1,1)
     )

This works great except when you want to put the result set of returned values from a SQL statement in a string:

<CFSET SomeString = #ValueList(QueryName.TableID)#>

And then search that string for matching records:

#ListFind(SomeString, ‘3’, ‘,’)#

Sometimes you run into a situation where the first three (3) that is found is actually in a value like 103 or 33. The Find functions consider everything an alphanumeric and cannot restrict itself to numeric value only.

OK… here is the trick.

Start your numeric value in the primary key field with “1000” instead of “1”.

IDENTITY(1000,1)

If your data table is going to have thousands or hundreds of thousands then consider using an initial number even higher than 1000.

This will truly make your FIND operations run more smoothly and take the opportunity to mix up numbers every once a while.

Adobe ColdFusion 9 * Functions by category