[thelist] ColdFusion List Functions - tip

[David Shadovitz]

Let me add to Jeff's recent tip on ColdFusion's list functions.  Sorry
for the length.

<tip type="ColdFusion lists" author="David Shadovitz">

You can get all the values of a particular field in list format via the
ValueList function.  But ValueList handles null values in a way that can
cause trouble.

Scenario:
You've got an Employees table with fields EmployeeID, FirstName,
MiddleInitial, LastName.
It has this data:
EmployeeID=1, FirstName="Tom", MiddleInitial="R", LastName="Smith"
EmployeeID=2, FirstName="Dick", MiddleInitial="", LastName="Jones"
EmployeeID=3, FirstName="Harry", MiddleInitial="A", LastName="West"

Note that Dick Jones has no middle initial.

If you retrieve this data like so:
   <cfquery name="qEmployees">
   select EmployeeID, FirstName, MiddleInitial, LastName from Employees
   </cfquery>
...you may want to use the ValueList function to process it.

It works fine for FirstName:
  ValueList(qEmployees.FirstName) =  Tom,Dick,Harry
  ListLen(ValueList(qEmployees.FirstName)) = 3
...but look what you get for MiddleInitial:
   ValueList(qEmployees.MiddleInitial) =  R,,A <--- see those 2 commas?
  ListLen(ValueList(qEmployees.MiddleInitial)) = 2 <--- not 3!

So if you loop through these lists using ListGetAt(index), you won't
match up the correct first names, middle initials, and last names.  I've
seen code using the Replace function to replace that double comma ",,"
with ",-," but why not let the database do it?  Use this SQL statement:
  select EmployeeID, FirstName,
IIF(IsNull(MiddleInitial),'-',MiddleInitial) as MidInitial, LastName
from Employees
Now the ValueList for qEmployees.MidInitial is R,-,A with length = 3.
</tip>