ColdFusion in Context: Filtering and Selecting Records

Most users want to enter just the beginning of a key and let the application bring up matches, but the syntax for the "like" function - the method used in most examples - is not portable across database engines. We'll look at a portable method for this kind of search.

When generating the resulting list, it would be nice to let the user pick the desired item in a single step without having to click on the item and then find the select button. We'll explore a single-click method.

Finally, when many options are available, it's useful to be able to show which items have already been selected and let the user unselect some and select others as desired.

Get Items Beginning with a Search String

As mentioned earlier, the implementation of SQL's "like" function is not portable across database engines. Oracle's "like" function for partial matches uses a percent sign (where key like 'abc%'); Microsoft Access' "like" function for partial matches uses an asterisk (where key like 'abc*').

However, if you want to find keys whose beginning matches a user-supplied string, you can do it without using the "like" function. The following example works just fine as long as you don't have any keys with more than five consecutive "Z"s in them. (You could screen for this on entry or just use more "Z"s.) 

<!--- Get Vendors beginning with the search string --->
<cfif isDefined("URL.Vendor") and ("#URL.Vendor#" is not 0)>
  <cfset attributes.Vendor="#URL.Vendor#">
<cfelse>
  <cfset attributes.Vendor="">
</cfif>
<cfquery name="listVendor" datasource="myDB">
select * from Vendor
where Vendor >= '#attributes.Vendor#'
and Vendor <= '#attributes.Vendor#zzzzz'
</cfquery>

Do One-step Selection from a List

Radio buttons would seem to be the natural method to employ when the user has to pick one option from a list, but if the list fills a screen or two, it's awkward to manuver the mouse cursor to the small radio button and then scroll to the top or bottom of the logical page to find and then click the submit button.

However, suppose you made every selection into a submit button instead of a radio button. Then the user could just click the submit button whose label is the desired key to pick the item with one click instead of two. 

<!--- Pick a Vendor from the resulting list --->
<table border="1">
<cfform name="listVendor" action="frmVendor.cfm" method="post">
<tr>
<td>PICK</td>
<td>VENDOR</td><td>Email</td></tr>
<td><input type="submit" name="VendorID" value=0></td><td>(none of these)</td><td> - </td></tr>
<cfoutput query="listVendor">
<tr><td><input type="submit" name="VendorID" value="#round(VendorID)#"></td><td>#Vendor#</td><td>#Email#</td></tr>
</cfoutput>
</cfform>
</table>

If this looks too crude, you could put the Vendor name into the submit button instead of the VendorID (assuming the Vendor name is unique).

The previous scripts could go in the same file; I named mine frmListVendor.cfm. They would feed one of two queries; the choice of query depends on whether a useful value was picked by the user. If a Vendor is selected, the query brings back the rest of the row. [Here's an example that returns rows whose Vendor begins with "car": frmListVendor.cfm?Vendor=car.] If a Vendor is not selected, the query brings back an empty row instead. 

<cfif isDefined("URL.VendorID") and ("#URL.VendorID#" is not 0)>
  <cfset attributes.VendorID="#URL.VendorID#">
<cfelse>
  <cfif isDefined ("form.VendorID") and ("#form.VendorID#" is not 0)>
    <cfset attributes.VendorID="#form.VendorID#">
  <cfelse>
    <cfset attributes.VendorID=0>
  </cfif>
</cfif>
<cfif attributes.VendorID is not 0>
  <cfquery name="qryGetVendor" datasource="myDB">
  select * from Vendor
  where VendorID = #attributes.VendorID#
  </cfquery>
<cfelse>
  <cfquery name="qryGetVendor" datasource="myDB">
  select * from VendorAdd
  </cfquery>
</cfif>

Make a Pick List Showing Current Selections

A query that shows all possible options is not the same as a query showing currently selected options, but to let the user unselect some options and select others as desired, both types of queries need to be used together somehow in the same pick list. One way to do this is to query for selected items (product lines in this case), store the results in a list structure, and check the list while displaying each item in the larger query that shows possible items.

Here's an example of building a list structure from a query. "ListAppend" essentially pastes new values to an existing string and places a comma between the values. In this case, the values are LineIDs already associated with this Vendor. 

<cfquery name="getLineVendor" datasource="myDB">
select * from LineVendor
where VendorID = #attributes.VendorID#
</cfquery>

<cfset newLineIDList=''>
<cfloop query="getLineVendor">
<cfset newLineIDList=ListAppend(#newLineIDList#, #LineID#)>
</cfloop>

Here's the query of possible product lines; the group of lines already associated with the Vendor is a subset of this list. The user will pick from the larger list knowing which lines are already selected (associated with the Vendor) and can select and de-select lines at will. 

<cfquery name="getLine" datasource="myDB">
select * from Line
</cfquery>

Here's where the query of all Lines and the query of the Lines associated with this Vendor come together. The following code puts a checkbox next to every Line and checks the ones already associated with this Vendor.

It's important to use "listFindNoCase" instead of "contains" or "listContains"; because, the "contains" functions will match pieces of keys. If you use "contains" functions, then if 18 is selected (for example), 1 and 8 will also become checked and will be associated with the Vendor user submits the form; that's not what you want. Use "listFindNoCase" so only complete matches will count.

Finally, the Exit button uses Javascript to break out of the endless loop presented by this form and the page it feeds. It will error out if you don't have an index.cfm in this directory. 

<cfform name="frmVendor" action="chgVendor.cfm" method="post">
<table>
<tr><td>Vendor:</td>
<td><cfinput type="text" name="Vendor" value="#qryGetVendor.Vendor#"></td></tr>
<tr><td>E-mail:</td>
<td><cfinput type="text" name="Email" value="#qryGetVendor.Email#"></td></tr>
<cfoutput query="getLine">
<cfif listFindNoCase(#newLineIDList#, #LineID#)>
  <cfset qual="CHECKED">
<cfelse>
  <cfset qual="">
</cfif>
<tr><td><input type="checkbox" name="newLineIDList" value="#round(LineID)#" #qual#></td>
<td>--#Line#--</td>
</cfoutput>
<cfoutput>
<input type="hidden" name="VendorID" value="#attributes.VendorID#">
<tr><td></td>
<td><input type="button" name="dothis" value="Exit" onClick="document.location='index.cfm'; return false;"><input type="reset" name="" value="Reset"><input type="submit" name="formaction" value="Submit"></td><td></td></tr>
</cfoutput>
</table>
</cfform>

All of the scripts presented so far (except for the first two which went into frmListVendor.cfm) can fit into a single file; I called mine frmVendor.cfm. It gives you a list of product line IDs, some of which are associated with the Vendor, and passes along the VendorID to be used. The user can check and uncheck a few options and feed the result to the following set of queries.

These queries update or insert the Vendor, delete all product lines for this Vendor, and then add back all selected product lines for the Vendor. I put them all in a single file: chgVendor.cfm. This file calls the form that called it - frmVendor.cfm - and the cycle continues until the user presses the Exit button. 

<cfif isDefined("form.VendorID") and ("#form.VendorID#" is not 0)>
  <!--- Update --->
  <cfquery name="chgVendor" datasource="myDB">
  update Vendor set
  <!--- (leave VendorID alone) --->
  Vendor = '#trim(form.Vendor)#',
  Email = '#trim(form.Email)#'
  where VendorID = #form.VendorID#
  </cfquery>
  <cfset ID1="#form.VendorID#">
<cfelse>
  <!--- Make next VendorID and add vendor with it --->
  <cfquery name="getMax1" datasource="myDB">
  select max(VendorID) as Max1
  from Vendor
  </cfquery>
  <cfif len(getMax1.Max1)>
    <cfset ID1=getMax1.Max1+1>
  <cfelse>
    <cfset ID1=1>
  </cfif>
  <cfquery name="addVendor" datasource="myDB">
  insert into Vendor (VendorID, Vendor, Email)
  values (<cfoutput>#ID1#, '#form.Vendor#', '#form.Email#'</cfoutput>)
  </cfquery>
</cfif>

<cfif isDefined("form.newLineIDList")>
  <!--- Delete all Product Lines for this Vendor --->
  <cfquery name="delLineVendor" datasource="myDB">
  delete * from LineVendor
  where VendorID = #ID1#
  </cfquery>
  <!--- Insert Selected Lines for this Vendor --->
  <cfloop index="ID2" list="#form.newLineIDList#">
  <cfquery name="addLineVendor" datasource="myDB">
  insert into LineVendor (LineID, VendorID)
  values (<cfoutput>#ID2#, #ID1#</cfoutput>)
  </cfquery>
  </cfloop>
</cfif>

<cfset ref="frmVendor.cfm?VendorID=#ID1#">
<cflocation URL="#ref#">

Summary

Although it took a good bit of supporting code to round out the demonstration, the focus of this session was to show that one can search in a portable fashion for keys that begin with a given search string, build a one-click list to pick the desired item (or none at all), and readily show in a list of values which ones are currently selected.

The techniques for doing these tasks ranged from using inequalities in SQL to using multiple submit buttons in a form to packing a query into a list for later ready reference. =Marty=

[For the demonstration of this tip, the exit point was changed from index.cfm to frmListVendor.cfm, and a sample link was added to demonstrate filtering the list.]