Christopher
Stoll

jQuery and FlexBox Cause AJAX Autocomplete Mania

A while ago I converted the select boxes for one of our web applications to use an AJAX driven, autocomplete input that grabs suggestions from the database. I used jQuery and the FlexBox plugin as described in my post comparing the functionality of jQuery and MooTools. Since then I have had requests to change most of the other applications to use this technology; the users absolutely love it.

For our internal web apps, one of the most common fields is the employee select field. We are using single sign-on, so we know who is entering the data, but we need to also present a list of other users as well. Many of these applications are used at multiple facilities, and normally a user will only want to see information relevant to that facility. Which is good since it allows for the presentation of a select list filtered by location. Without this we would have to generate select drop-downs containing all users from all facilities, which is thousands of users. But, since the Great Recesion, more people are working in cross-facility roles and the need for a complete list has grown. The solution to this problem has been the AJAX driven autocomplete inputs.

I have been reusing the same snippet of code for multiple appliations, and decided to share it here as well. It can be found after the jump.

In the head of the HTML document:

<script type="text/javascript"
 src="js/jquery-1.3.2.min.js" >
 </script>
<script type="text/javascript"
 src="js/jquery.flexbox.min.js">
 </script>
<link type="text/css"
 rel="stylesheet"
 href="css/jquery.flexbox.css" />

<script type="text/javascript">
  $(document).ready(function() {
    $('#fb_user_clocknbr').
 flexbox('data/Central_Data/json_user_clock.cfm', {
      paging: false,
      showArrow: false,
      queryDelay: 400,
      minChars: 3,
      onSelect: function() {
        var selId = this.getAttribute('hiddenValue');
        $('#clockNbr').val(selId);
      }
    });
  });
</script>

Comments (newest first)

Christopher Stoll
Here is json_user_clock.cfm
<cfif isdefined('queryString')>
<cfset queryString = replace(replace(queryString, " ", "%"), ",", "%")>
<cfquery name="qCentralData" datasource="#Application.DSN#">
SELECT TOP 25 ClockNumber, LastName, FirstName
FROM Employees
WHERE (ClockNumber IS NOT NULL) AND
(UserName like '#queryString#%'
OR LastName like '#queryString#%'
OR LastName + '-' + FirstName like '#queryString#%'
OR FirstName like '#queryString#%'
OR FirstName + '-' + LastName like '#queryString#%')
ORDER BY LastName, FirstName, ClockNumber
</cfquery>
<cfset firstpass=0>
<cfcontent type="application/json; charset=utf-8">
{"results":[
<cfoutput query="qCentralData" maxrows="#queryLimit#"><cfif firstpass eq 0><cfset firstpass=1><cfelse>,</cfif>{"id": #XMLFormat(qCentralData.ClockNumber)#, "name": "#XMLFormat(qCentralData.LastName)#, #XMLFormat(qCentralData.FirstName)#"}</cfoutput>
],"total":<cfoutput>#qCentralData.recordcount#</cfoutput>}
</cfif>
some thoughts
can you post the code for json_user_clock.cfm
Published: 2009-11-05
BloggerJavaScriptAJAXjQueryCode