David asks what I think is a pretty interesting question concerning ColdFusion, Ajax, and auto-complete style functionality.
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?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.
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 = {}>
<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>
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 = "">
<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>
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.
Archived Comments
Love the call signs: iceman, maverick, and goose. Surprised you didn't use Red Leader!
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.
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
I'm surprised he left out Viper. I'm gonna use this in my application too. Thanks Ray!
@john, i was on target for that one. i figured the license plate would be T-16 skyhopper or T-65 x-wing.
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.
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.
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,
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>
<cfset name = ' #ContactLastName#, #ContactFirstname# #ContactID#'>
Stupid me, should be...
<cfset name = ' #ContactLastName# #ContactFirstname# #ContactID#'>
Can you please supply the entire code? I am running into problems.
Thanks!
Gregory
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>
What does Firebug tell you?
I found one error, html tag out of place. Fixed that and now no errors, but there is no functionality.
So you see no XHR requests?
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.
Oh wow, you will kick yourself. You used <input>, not <cfinput>
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?
You can't. On post, you would need to check the value against the db and find the related ID.
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.
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.
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.
That depends on your data. If you are selecting from a table, check the table columns and see which one is the primary key.
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??
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.
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.
Does your Application.cfc use an onRequestStart that auto adds HTML?
No. My Application.cfc is blank...
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?
Doh!! My fault... Application.cfc was not blank... It had the html in it. Sorry.... But thanks for the quick response and great blog!
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.
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.
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?
There is an argument for this: showAutosuggestionLoadingIcon=true/false.
Yep. Found that in the livedocs and came back to update my question... but you beat me to it! Thanks again for this.
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>
And that didn't work for you? I notice you forgot to actually return anything.
Ha! Oops. I suppose that's necessary. Working now.
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.
Do you see an error thrown in IE?
You know, that may be an issue with IE8. Can you try a super simple autosuggest demo - like from the CF docs?
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.
It may just be the library used by CF. You should try the jQueryUI autocomplete in IE8 and see if it is compatible.
@Ray - I'm sorry I don't understand. Do you mean rewrite the code using jquery instead of cf's built in autosuggest?
jQuery or another vanilla JS solution.