Twitter: raymondcamden


Address: Lafayette, LA, USA

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

04-21-2008 10,363 views ColdFusion 45 Comments

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:

view plain print about
1<form>
2<table>
3    <tr>
4        <td>Call Sign:</td>
5        <td><input type="text" name="callsign" id="callsign"></td>
6    </tr>
7    <tr>
8        <td>First Name:</td>
9        <td><input type="text" name="firstname" id="firstname"></td>
10    </tr>
11    <tr>
12        <td>Trained Spotted:</td>
13        <td><input type="checkbox" name="trainedspotter" id="trainedspotter"></td>
14    </tr>
15    <tr>
16        <td>Car License Plate:</td>
17        <td><input type="text" name="licenseplate" id="licenseplate"></td>
18    </tr>
19</table>
20</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:

view plain print about
1<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:

view plain print about
1<cfajaxproxy cfc="radiopeople" jsclassname="radiopeopleservice">

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

view plain print about
1<script>
2var radioPeopleService = new radiopeopleservice();
3
4function loadit(cs) {
5    var data = radioPeopleService.getProfile(cs);
6    if(data.FIRSTNAME != null) document.getElementById('firstname').value = data.FIRSTNAME;
7    if(data.TRAINEDSPOTTER != null) if(data.TRAINEDSPOTTER) document.getElementById('trainedspotter').checked = true;
8    if(data.LICENSEPLATE != null) document.getElementById('licenseplate').value = data.LICENSEPLATE;
9    console.dir(data);
10}
11</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:

view plain print about
1<cffunction name="getProfile" output="false" returnType="struct" hint="I return information based on a call sign" access="remote">
2    <cfargument name="callsign" type="string" required="false" default="">
3    <cfset var result = {}>
4    
5    <cfswitch expression="#arguments.callsign#">
6        <cfcase value="iceman">
7            <cfset result = {firstname="Raymond",trainedspotter=false,licenseplate="XXX11"}>
8        </cfcase>
9        <cfcase value="maverick">
10            <cfset result = {firstname="Tom",trainedspotter=true,licenseplate="AAA11"}>
11        </cfcase>
12        
13        <cfcase value="goose">
14            <cfset result = {firstname="Fred",trainedspotter=false,licenseplate="GGG11"}>
15        </cfcase>
16
17    </cfswitch>
18
19    <cfreturn result>    
20</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:

view plain print about
1<cfform>
2<table>
3    <tr>
4        <td>Call Sign:</td>
5        <td><cfinput type="text" name="callsign" id="callsign" autosuggest="cfc:radiopeople.getCallSigns({cfautosuggestvalue})"></td>
6    </tr>
7    <tr>
8        <td>First Name:</td>
9        <td><input type="text" name="firstname" id="firstname"></td>
10    </tr>
11    <tr>
12        <td>Trained Spotted:</td>
13        <td><input type="checkbox" name="trainedspotter" id="trainedspotter"></td>
14    </tr>
15    <tr>
16        <td>Car License Plate:</td>
17        <td><input type="text" name="licenseplate" id="licenseplate"></td>
18    </tr>
19</table>
20</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:

view plain print about
1<cffunction name="getCallSigns" output="false" returnType="string" hint="I suggest call signs" access="remote">
2    <cfargument name="callsign" type="string" required="false" default="">
3    <!--- create a fake query --->
4    <cfset var q = queryNew("callsign")>
5    <cfset var r = "">
6    
7    <cfset queryAddRow(q)>
8    <cfset querySetCell(q, "callsign", "iceman")>
9    <cfset queryAddRow(q)>
10    <cfset querySetCell(q, "callsign", "maverick")>
11    <cfset queryAddRow(q)>
12    <cfset querySetCell(q, "callsign", "goose")>
13    
14    <cfquery name="r" dbtype="query">
15    select    callsign
16    from    q
17    where    upper(callsign) like <cfqueryparam cfsqltype="cf_sql_varchar" value="#ucase(arguments.callsign)#%">
18    </cfquery>
19    
20    <cfreturn valueList(r.callsign)>
21    
22</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.

45 Comments

  • Commented on 04-22-2008 at 4:54 AM
    Love the call signs: iceman, maverick, and goose. Surprised you didn't use Red Leader!
  • Commented on 04-22-2008 at 6:03 AM
    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.
  • Commented on 04-22-2008 at 9:45 AM
    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
  • Michael White #
    Commented on 04-22-2008 at 3:17 PM
    I'm surprised he left out Viper. I'm gonna use this in my application too. Thanks Ray!
  • shag #
    Commented on 04-22-2008 at 7:35 PM
    @john, i was on target for that one. i figured the license plate would be T-16 skyhopper or T-65 x-wing.
  • Commented on 04-23-2008 at 5:21 PM
    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.
  • Commented on 04-23-2008 at 8:09 PM
    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.
  • Commented on 04-24-2008 at 8:44 AM
    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,
  • Commented on 08-26-2008 at 1: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>
  • Commented on 08-26-2008 at 1:40 PM
    <cfset name = ' #ContactLastName#, #ContactFirstname# #ContactID#'>

    Stupid me, should be...

    <cfset name = ' #ContactLastName# #ContactFirstname# #ContactID#'>
  • Gregory #
    Commented on 02-10-2009 at 5:53 PM
    Can you please supply the entire code? I am running into problems.

    Thanks!


    Gregory
  • Commented on 05-05-2009 at 2: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/loose.dtd">;
    <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="cfsqlvarchar" value="#ucase(arguments.callsign)#%">
    </cfquery>

    <cfreturn valueList(r.callsign)>

    </cffunction>
    </cfcomponent>
  • Commented on 05-05-2009 at 7:41 PM
    What does Firebug tell you?
  • Commented on 05-06-2009 at 9:14 AM
    I found one error, html tag out of place. Fixed that and now no errors, but there is no functionality.
  • Commented on 05-06-2009 at 6:18 PM
    So you see no XHR requests?
  • Commented on 05-07-2009 at 6:45 AM
    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.
  • Commented on 05-07-2009 at 5:01 PM
    Oh wow, you will kick yourself. You used <input>, not <cfinput>
  • Adam #
    Commented on 09-29-2009 at 12: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?
  • Commented on 09-29-2009 at 4:46 PM
    You can't. On post, you would need to check the value against the db and find the related ID.
  • Adam #
    Commented on 09-30-2009 at 2:31 PM
    I'm using a CFC similar to what Charles Higgins is using (http://www.coldfusionjedi.com/index.cfm/2008/4/21/...)

    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.
  • Commented on 09-30-2009 at 3:49 PM
    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.
  • Adam #
    Commented on 10-02-2009 at 3:25 PM
    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.
  • Commented on 10-02-2009 at 9:54 PM
    That depends on your data. If you are selecting from a table, check the table columns and see which one is the primary key.
  • Terry #
    Commented on 12-14-2009 at 1:29 PM
    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??
  • Commented on 12-14-2009 at 1:44 PM
    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.
  • Anthony #
    Commented on 01-18-2010 at 3:08 PM
    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/DTD/xhtml1-transitiona...;
    <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"}&cfnodebug=true and I still get the same response.
  • Commented on 01-18-2010 at 11:06 PM
    Does your Application.cfc use an onRequestStart that auto adds HTML?
  • Anthony #
    Commented on 01-19-2010 at 7:03 AM
    No. My Application.cfc is blank...
  • Commented on 01-19-2010 at 7:05 AM
    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?
  • Anthony #
    Commented on 01-19-2010 at 8:06 AM
    Doh!! My fault... Application.cfc was not blank... It had the html in it. Sorry.... But thanks for the quick response and great blog!
  • Shishir Sharma #
    Commented on 03-01-2013 at 5:34 AM
    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.
  • Commented on 03-01-2013 at 6:14 AM
    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.
  • Andy Idema #
    Commented on 04-29-2013 at 9:01 AM
    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?
  • Commented on 04-29-2013 at 9:05 AM
    There is an argument for this: showAutosuggestionLoadingIcon=true/false.
  • Andy Idema #
    Commented on 04-29-2013 at 9:17 AM
    Yep. Found that in the livedocs and came back to update my question... but you beat me to it! Thanks again for this.
  • Andy Idema #
    Commented on 04-29-2013 at 11:26 AM
    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="getd" datasource="mydsn" maxrows="1">
       SELECT *
       FROM passengers
       WHERE passenger
    firstname = '#arguments.callsign#'
       </cfquery>
       
       <cfset result = {firstname="#get
    d.passengerfirstname#",trainedspotter=false,licenseplate="GGG11"}>

    </cffunction>
  • Commented on 04-29-2013 at 11:38 AM
    And that didn't work for you? I notice you forgot to actually return anything.
  • Andy Idema #
    Commented on 04-29-2013 at 11:41 AM
    Ha! Oops. I suppose that's necessary. Working now.
  • Andy Idema #
    Commented on 05-01-2013 at 11:29 AM
    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.
  • Commented on 05-01-2013 at 1:53 PM
    Do you see an error thrown in IE?
  • Commented on 05-01-2013 at 1:54 PM
    You know, that may be an issue with IE8. Can you try a super simple autosuggest demo - like from the CF docs?
  • Andy Idema #
    Commented on 05-01-2013 at 3:17 PM
    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.
  • Commented on 05-01-2013 at 4:44 PM
    It may just be the library used by CF. You should try the jQueryUI autocomplete in IE8 and see if it is compatible.
  • Andy Idema #
    Commented on 05-01-2013 at 7:19 PM
    @Ray - I'm sorry I don't understand. Do you mean rewrite the code using jquery instead of cf's built in autosuggest?
  • Commented on 05-01-2013 at 7:51 PM
    jQuery or another vanilla JS solution.

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty