Ask a Jedi: Extending ColdFusion's auto-complete feature

This post is more than 2 years old.

David asks what I think is a pretty interesting question concerning ColdFusion, Ajax, and auto-complete style functionality.

I know you have covered the CF8 autocomplete tag, but I am looking to take it one step further, but haven't been able to find any good direction.

I have been building an application for our amateur radio emergency services group that allows us to track our weather spotters while they are in the field. Logging ham operators to the net requires collecting the same information from many time the same people over and over.

I would like to use a form that when the first field is enter (the callsign of the operator) it does a trip to the database and if the ham has checked into a net before, bring back his information and auto complete the remaining form fields.

Example, the first field is Call Sign, I would enter mine, N9CTO and the next three fields would become populated. Those fields would be First Name, Trained Spotter, and my car information to be verified.

I know this is a very specific example, but with CF8, Autocomplete, AJAX I figure this can problem be completed. If I knew Javascript more, I could probably do that as well, just curious to know if the autocomplete can be extended to not only suggest in the first field, but drive answers in the remaining fields. Obviously, if the first field doesn't match in the database, we would enter the details then in the future, our weather spotter would be in the database.

So this was a pretty interesting question. I decided to start it simply first. Instead of worrying about the auto-complete, how can we use ColdFusion 8 and AJAX to simply say, if I do callsign foo, prepopulate these values?

Let's start with our form:

<form> <table> <tr> <td>Call Sign:</td> <td><input type="text" name="callsign" id="callsign"></td> </tr> <tr> <td>First Name:</td> <td><input type="text" name="firstname" id="firstname"></td> </tr> <tr> <td>Trained Spotted:</td> <td><input type="checkbox" name="trainedspotter" id="trainedspotter"></td> </tr> <tr> <td>Car License Plate:</td> <td><input type="text" name="licenseplate" id="licenseplate"></td> </tr> </table> </form>

We have four form fields. Our call sign is first, then we have three related fields. The first thing we want to do is notice a change to our callsign field. This is easily achieved with cfajaxproxy:

<cfajaxproxy bind="javaScript:loadit({callsign})">

This says - when callsign changes, run a JavaScript function called loatit. We want to talk to the back end - and while we have multiple ways of doing it, I'm just going to use another cfajaxproxy. As I've said before, cfajaxproxy really has two very disparate styles. One acts like a binding (as you see above) and another creates a connection between your JavaScript code and a CFC. Like so:

<cfajaxproxy cfc="radiopeople" jsclassname="radiopeopleservice">

So now that we have a connection, let's look at the JavaScript:

<script> var radioPeopleService = new radiopeopleservice();

function loadit(cs) { var data = radioPeopleService.getProfile(cs); if(data.FIRSTNAME != null) document.getElementById('firstname').value = data.FIRSTNAME; if(data.TRAINEDSPOTTER != null) if(data.TRAINEDSPOTTER) document.getElementById('trainedspotter').checked = true; if(data.LICENSEPLATE != null) document.getElementById('licenseplate').value = data.LICENSEPLATE; console.dir(data); } </script>

We begin by creating an instance of our proxy. Once we have this proxy, we can run any method on the CFC that has access=remote. Even without looking at the CFC yet you can guess what is going on here. We have a CFC method, getProfile, that will look up a call sign and return a structure of data. The rest of the code is simply JavaScript.

Now take a look at the CFC method:

<cffunction name="getProfile" output="false" returnType="struct" hint="I return information based on a call sign" access="remote"> <cfargument name="callsign" type="string" required="false" default=""> <cfset var result = {}>
&lt;cfswitch expression="#arguments.callsign#"&gt;
	&lt;cfcase value="iceman"&gt;
		&lt;cfset result = {firstname="Raymond",trainedspotter=false,licenseplate="XXX11"}&gt;
	&lt;/cfcase&gt;
	&lt;cfcase value="maverick"&gt;
		&lt;cfset result = {firstname="Tom",trainedspotter=true,licenseplate="AAA11"}&gt;
	&lt;/cfcase&gt;
	
	&lt;cfcase value="goose"&gt;
		&lt;cfset result = {firstname="Fred",trainedspotter=false,licenseplate="GGG11"}&gt;
	&lt;/cfcase&gt;

&lt;/cfswitch&gt;

&lt;cfreturn result&gt;	

</cffunction>

As you can see, the code recognized 3 call signs: iceman, maverick, and goose. For each it returns a structure of data. So that's it. If you run the form, enter iceman, you will see the form populate. If you change to goose, you will see other values. You can see an online demo of this here.

Ok, so that's halfway there. David had asked about using autocomplete as well. Luckily I don't have to do much more. First off - I just changed my form to a cfform. Then I changed my first form field to be an autosuggest field. Here is the new, complete, form:

<cfform> <table> <tr> <td>Call Sign:</td> <td><cfinput type="text" name="callsign" id="callsign" autosuggest="cfc:radiopeople.getCallSigns({cfautosuggestvalue})"></td> </tr> <tr> <td>First Name:</td> <td><input type="text" name="firstname" id="firstname"></td> </tr> <tr> <td>Trained Spotted:</td> <td><input type="checkbox" name="trainedspotter" id="trainedspotter"></td> </tr> <tr> <td>Car License Plate:</td> <td><input type="text" name="licenseplate" id="licenseplate"></td> </tr> </table> </cfform>

As you can see, the autosuggest calls the same CFC we used before, now using a method named getCallSigns. This method looks like so:

<cffunction name="getCallSigns" output="false" returnType="string" hint="I suggest call signs" access="remote"> <cfargument name="callsign" type="string" required="false" default=""> <!--- create a fake query ---> <cfset var q = queryNew("callsign")> <cfset var r = "">
&lt;cfset queryAddRow(q)&gt;
&lt;cfset querySetCell(q, "callsign", "iceman")&gt;
&lt;cfset queryAddRow(q)&gt;
&lt;cfset querySetCell(q, "callsign", "maverick")&gt;
&lt;cfset queryAddRow(q)&gt;
&lt;cfset querySetCell(q, "callsign", "goose")&gt;

&lt;cfquery name="r" dbtype="query"&gt;
select	callsign
from	q
where	upper(callsign) like &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.callsign)#%"&gt;
&lt;/cfquery&gt;

&lt;cfreturn valueList(r.callsign)&gt;

</cffunction>

Obviously I'd use a real query for this method (and the other one), but you can see that basically I'm creating a query and filtering the results based on the text typed into the autosuggest. You can see an example of this here.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by John Whish posted on 4/22/2008 at 1:54 PM

Love the call signs: iceman, maverick, and goose. Surprised you didn't use Red Leader!

Comment 2 by CV posted on 4/22/2008 at 3:03 PM

I've seen this idea in place before on HGTV.com and there various contest(s) and always wondered how it was done. (Cookies, JS, or Ajax.) Thanks for this excellent example.

Comment 3 by David posted on 4/22/2008 at 6:45 PM

Ray, thanks for answering my question. Never too late. I modified the code last night for our weather net software following your excellent tutorial, needless to say, we will save a lot of key strokes at critical times this season while we watch the weather.

Regards,

Dave
N9CTO

Comment 4 by Michael White posted on 4/23/2008 at 12:17 AM

I'm surprised he left out Viper. I'm gonna use this in my application too. Thanks Ray!

Comment 5 by shag posted on 4/23/2008 at 4:35 AM

@john, i was on target for that one. i figured the license plate would be T-16 skyhopper or T-65 x-wing.

Comment 6 by BJ posted on 4/24/2008 at 2:21 AM

Ray,

To follow up on this. I'm also working with autosuggest where the user can search by last name. The problem I have is when I have two last names that are the same I need the ID of the record to grab the data for the selected person. Also I would like to show the first name on the same line. So the line might look like:
Ray, Terri
Ray, Mike
I've tried different things and I have also looked around for examples on how to do this. I don't really want to show the ID in the autosuggest list I just need to pass it.

Comment 7 by Raymond Camden posted on 4/24/2008 at 5:09 AM

You could work with multiple records. So if the first field is last name, and you type in Camden, and 2 rows returned, you could show them both (if you had a select box with multiple entries), or just pick the first one.

Comment 8 by BJ posted on 4/24/2008 at 5:44 PM

Oh in this case since I need more than one field so I need to just use the regular select statement and not the cfinput and autosuggest
thanks,

Comment 9 by Charles Higgins posted on 8/26/2008 at 10:37 PM

Okay, i had a similar problem on wanting to stack a Lastname and Firstname autosuggest in one box.

This seemed to work a treat, by stacking the results the user can type in Lastname then Firstname then an ID, and while they are doing it auto predicts and so shows the options; All i had to do was add a little bit of text to the standard example.

<cfcomponent output="false">

<!--- Lookup used for auto suggest --->
<cffunction name="lookupContact" access="remote" returntype="array">
<cfargument name="search" type="any" required="false" default="">

<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(1)>

<!--- Do search --->
<cfquery datasource="#application.datasrc#" name="data">
SELECT ContactLastName, ContactFirstname, ContactID
FROM Contact
WHERE UCase(ContactLastname) LIKE Ucase('#ARGUMENTS.search#%')
ORDER BY ContactLastname, ContactFirstname
</cfquery>

<!--- Build result array --->
<cfloop query="data">
<!--- this the new line, all it needed --->
<cfset name = ' #ContactLastName#, #ContactFirstname# #ContactID#'>
<cfset ArrayAppend(result, #name#)>
</cfloop>

<!--- And return it --->
<cfreturn result>
</cffunction>

</cfcomponent>

Comment 10 by Charles Higgins posted on 8/26/2008 at 10:40 PM

<cfset name = ' #ContactLastName#, #ContactFirstname# #ContactID#'>

Stupid me, should be...

<cfset name = ' #ContactLastName# #ContactFirstname# #ContactID#'>

Comment 11 by Gregory posted on 2/11/2009 at 4:53 AM

Can you please supply the entire code? I am running into problems.

Thanks!

Gregory

Comment 12 by Lyndon posted on 5/5/2009 at 11:24 PM

I'm having trouble with the example. No errors, but it does not function. Autosuggest does not even work. I'm very new to Ajax, maybe I have something out of order. Any help appreciated.

html file:
----------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/...">
<html>
<head>

</head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>

<br><br>

<body>
<form>
<table>
<tr>
<td>Call Sign:</td>
<td><input type="text" name="callsign" id="callsign" autosuggest="cfc:radioPeople.getCallSigns({cfautosuggestvalue})"></td>
</tr>
<tr>
<td>First Name:</td>
<td><input type="text" name="firstname" id="firstname"></td>
</tr>
<tr>
<td>Trained Spotted:</td>
<td><input type="checkbox" name="trainedspotter" id="trainedspotter"></td>
</tr>
<tr>
<td>Car License Plate:</td>
<td><input type="text" name="licenseplate" id="licenseplate"></td>
</tr>
</table>
<cfajaxproxy bind="javaScript:loadit({callsign})">
<cfajaxproxy cfc="radiopeople" jsclassname="radiopeopleservice">
</form>

</body>
</html>

<script>
var radioPeopleService = new radiopeopleservice();

function loadit(cs) {
var data = radioPeopleService.getProfile(cs);
if(data.FIRSTNAME != null) document.getElementById('firstname').value = data.FIRSTNAME;
if(data.TRAINEDSPOTTER != null) if(data.TRAINEDSPOTTER) document.getElementById('trainedspotter').checked = true;
if(data.LICENSEPLATE != null) document.getElementById('licenseplate').value = data.LICENSEPLATE;
console.dir(data);
}
</script>

radioPeople.cfc File:
-----------------------------
<cfcomponent hint="Owner database functions">

<cffunction name="getProfile" output="false" returnType="struct" hint="I return information based on a call sign" access="remote">
<cfargument name="callsign" type="string" required="false" default="">
<cfset var result = {}>

<cfswitch expression="#arguments.callsign#">
<cfcase value="iceman">
<cfset result = {firstname="Raymond",trainedspotter=false,licenseplate="XXX11"}>
</cfcase>
<cfcase value="maverick">
<cfset result = {firstname="Tom",trainedspotter=true,licenseplate="AAA11"}>
</cfcase>

<cfcase value="goose">
<cfset result = {firstname="Fred",trainedspotter=false,licenseplate="GGG11"}>
</cfcase>

</cfswitch>

<cfreturn result>
</cffunction>

<cffunction name="getCallSigns" output="false" returnType="string" hint="I suggest call signs" access="remote">
<cfargument name="callsign" type="string" required="false" default="">
<!--- create a fake query --->
<cfset var q = queryNew("callsign")>
<cfset var r = "">

<cfset queryAddRow(q)>
<cfset querySetCell(q, "callsign", "iceman")>
<cfset queryAddRow(q)>
<cfset querySetCell(q, "callsign", "maverick")>
<cfset queryAddRow(q)>
<cfset querySetCell(q, "callsign", "goose")>

<cfquery name="r" dbtype="query">
select callsign
from q
where upper(callsign) like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.callsign)#%">
</cfquery>

<cfreturn valueList(r.callsign)>

</cffunction>
</cfcomponent>

Comment 13 by Raymond Camden posted on 5/6/2009 at 4:41 AM

What does Firebug tell you?

Comment 14 by Lyndon posted on 5/6/2009 at 6:14 PM

I found one error, html tag out of place. Fixed that and now no errors, but there is no functionality.

Comment 15 by Raymond Camden posted on 5/7/2009 at 3:18 AM

So you see no XHR requests?

Comment 16 by Lyndon posted on 5/7/2009 at 3:45 PM

No, the dropdown does not appear when I type in the text input and if I complete the text the other inputs do not populate. I wonder if there is some basic setup on the server that I'm not aware of.

Comment 17 by Raymond Camden posted on 5/8/2009 at 2:01 AM

Oh wow, you will kick yourself. You used <input>, not <cfinput>

Comment 18 by Adam posted on 9/29/2009 at 9:25 PM

I am using this method to load a list of clients for consultants to choose from. The list loads the client lastname, firstname, email, organization, and ID. All elements are pertinent to the consultant except ID. I need the ID to update the database record. How may I show all data except the ID in the autosuggest, but post only the ID when the form is submitted?

Comment 19 by Raymond Camden posted on 9/30/2009 at 1:46 AM

You can't. On post, you would need to check the value against the db and find the related ID.

Comment 20 by Adam posted on 9/30/2009 at 11:31 PM

I'm using a CFC similar to what Charles Higgins is using (http://www.coldfusionjedi.c...

My index page is calling the CFC to populate the cfinput textbox. Do you suggest using the ID as a hidden field that is passed when the form posts? If so, do I obtain the ID from the CFC using <cfinvoke> to return the ID? I have not used CFCs before and have been examining the docs to better understand.

Thanks in advance.

Comment 21 by Raymond Camden posted on 10/1/2009 at 12:49 AM

If you can set it up so that your data returns IDs and labels, and can then stuff the ID in a hidden field when you select the right value in the auto complete, then sure, that would work fine.

Comment 22 by Adam posted on 10/3/2009 at 12:25 AM

I'm completely in the dark and don't mean to be. I don't understand how to obtain the ID property from the CFC.

Comment 23 by Raymond Camden posted on 10/3/2009 at 6:54 AM

That depends on your data. If you are selecting from a table, check the table columns and see which one is the primary key.

Comment 24 by Terry posted on 12/15/2009 at 12:29 AM

The autosuggest is goofy with IE 8. After entering a call sign, it works fine. However, if I attempt to use a different call sign, then the autosuggest box sticks and the text fields do not populate with the new data.. However, it works great in FF.. Is anyone else experiencing the same thing in IE 8 or other browsers? Is there a fix for this??

Comment 25 by Raymond Camden posted on 12/15/2009 at 12:44 AM

I think it is the console.log messages I left in the code. That's the error I saw when I tried it in IE8. Try removing them.

Comment 26 by Anthony posted on 1/19/2010 at 2:08 AM

I am getting html in my response from the getProfile function:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1...">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
</body>
</html>
{maverick}

Any idea why? I posted the url directly in a browser, radiopeople.cfc?method=getProfile&argumentCollection={"callsign":"m"}&_cf_nodebug=true and I still get the same response.

Comment 27 by Raymond Camden posted on 1/19/2010 at 10:06 AM

Does your Application.cfc use an onRequestStart that auto adds HTML?

Comment 28 by Anthony posted on 1/19/2010 at 6:03 PM

No. My Application.cfc is blank...

Comment 29 by Raymond Camden posted on 1/19/2010 at 6:05 PM

Use Firebug to note the URL of the request. Take that URL and open it in a new tab. View source. Is it the same?

Comment 30 by Anthony posted on 1/19/2010 at 7:06 PM

Doh!! My fault... Application.cfc was not blank... It had the html in it. Sorry.... But thanks for the quick response and great blog!

Comment 31 by Shishir Sharma posted on 3/1/2013 at 4:34 PM

Hi, I tried implementing autocomplete functionality using your autosuggest attribute of cfinput tag and a cfc. When I used it in a new page, it worked just fine, but when I included in my application I am getting this error:

window:global: 'containerCollapseEvent' is null or not an object

Did not understand this. Kindly assist.

Comment 32 by Raymond Camden posted on 3/1/2013 at 5:14 PM

I'd have to take a guess that maybe the default JS files CF uses are not loading. Open your Chrome Dev Tools and see if you notice any 404 errors with the JS files.

Comment 33 by Andy Idema posted on 4/29/2013 at 6:01 PM

Thank you for this Ray. Works like a charm. The only thing I'd like to change is to get rid of the static.gif next to the autosuggest box. When idle it just looks like a crummy image of a starish type thing. Suggestions?

Comment 34 by Raymond Camden posted on 4/29/2013 at 6:05 PM

There is an argument for this: showAutosuggestionLoadingIcon=true/false.

Comment 35 by Andy Idema posted on 4/29/2013 at 6:17 PM

Yep. Found that in the livedocs and came back to update my question... but you beat me to it! Thanks again for this.

Comment 36 by Andy Idema posted on 4/29/2013 at 8:26 PM

Ray,

How would I make the getProfile function more dynamic so that I query a table (in my example, passengers) when the callsign field has been selected? Here's what I attempted...

<cffunction name="getProfile" returnType="struct" access="remote">

<cfargument name="callsign" type="string" required="false" default="">
<cfset var result = {}>

<cfquery name="get_d" datasource="mydsn" maxrows="1">
SELECT *
FROM passengers
WHERE passenger_first_name = '#arguments.callsign#'
</cfquery>

<cfset result = {firstname="#get_d.passenger_first_name#",trainedspotter=false,licenseplate="GGG11"}>

</cffunction>

Comment 37 by Raymond Camden posted on 4/29/2013 at 8:38 PM

And that didn't work for you? I notice you forgot to actually return anything.

Comment 38 by Andy Idema posted on 4/29/2013 at 8:41 PM

Ha! Oops. I suppose that's necessary. Working now.

Comment 39 by Andy Idema posted on 5/1/2013 at 8:29 PM

I took this example and built it so that I can have several different rows doing the autosuggest and autopopulate. It works well in Firefox and Chrome but in ie8... no dice. It works on the first row in ie8, but any other rows don't autopopulate.

Comment 40 by Raymond Camden posted on 5/1/2013 at 10:53 PM

Do you see an error thrown in IE?

Comment 41 by Raymond Camden posted on 5/1/2013 at 10:54 PM

You know, that may be an issue with IE8. Can you try a super simple autosuggest demo - like from the CF docs?

Comment 42 by Andy Idema posted on 5/2/2013 at 12:17 AM

At this point I'm almost certain it's an ie8 issue. I've tested it successfully in Firefox and Chrome. How irritating because this is a really slick use of cf and js. Unfortunately they are forcing ie8 in this office so I can't use what I put together based on this tute. It's a shame because testing in Chrome it's freegin awesome.

Comment 43 by Raymond Camden posted on 5/2/2013 at 1:44 AM

It may just be the library used by CF. You should try the jQueryUI autocomplete in IE8 and see if it is compatible.

Comment 44 by Andy Idema posted on 5/2/2013 at 4:19 AM

@Ray - I'm sorry I don't understand. Do you mean rewrite the code using jquery instead of cf's built in autosuggest?

Comment 45 by Raymond Camden posted on 5/2/2013 at 4:51 AM

jQuery or another vanilla JS solution.