Ajax with Double Bind

Ok this was pretty tricky but I figured it out :-)

Here are the requirements I needed to fulfill:

1. Display a select box of categories
2. Based on the category selected, do an autosuggest of institutions
3. Populate a hidden field with the institutionID based on the institution selected in #2.

Ok so here is my first SELECT:

<cfselect name="categoryID"
          bind="cfc:ajaxlookup.getInstitutionCategories()"
            bindonload="true" />

It called the getInstitutionCategories function in the ajaxlookup CFC.

Here is the method:

<cffunction name="getInstitutionCategories" access="remote" returnType="array">
        <!--- Define variables --->
        <cfset var data="">
        <cfset var result=ArrayNew(2)>
        <cfset var i=0>

        <!--- Get data --->
        <cfquery name="data" datasource="#request.dsn#">
             SELECT         strInstitutionCategory, intInstitutionCateogryNumber
            FROM          tblInstitutionCategories
            where intInstitutionCateogryNumber IN (2,3,1,6,7,8,9)   
            ORDER BY strInstitutionCategory   
        </cfquery>

        <!--- Convert results to array --->
        <cfloop index="i" from="1" to="#data.RecordCount#">
         
            <cfset result[i][1]=data.intInstitutionCateogryNumber[i]>
            <cfset result[i][2]=data.strInstitutionCategory[i]>
        </cfloop>

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


When the user selects the category, I take the category ID and search for institutions in that category using a call to the getInstitutions method in ajaxlookup CFC.

 <cfinput type="text" name="institution"
        autosuggest="cfc:ajaxlookup.getInstitutions({cfautosuggestvalue},{categoryID})"   value="#form.institution#"  size="60" showAutoSuggestLoadingIcon="false">

As you can see, two values are passed to the function: the value typed, and the category ID that the user selected in the first drop down.


Here is the getInstitutions function:

<cffunction access="remote" name="getInstitutions" output="false" returntype="array">
<cfargument name="search"     type="any" required="true" default="">
<cfargument name="catID"     type="numeric" required="true">

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

<!--- Do search --->
<cfquery datasource="#request.dsn#" name="data">
SELECT     strInstitutionName
FROM tblInstitutions
WHERE intInstitutionCategoryID = #catID#
AND strInstitutionName LIKE '#search#%'
ORDER BY strInstitutionName
</cfquery>

<!--- Build result array --->
<cfloop query="data">
<cfset ArrayAppend(result, strInstitutionName)>
</cfloop>

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


And for step 3, I want to populate a hidden field with the institutionID of the institution the user selected in step 2:


<cfinput type="hidden" name="institutionID" id="institutionID" bind="cfc:ajaxlookup.getInstitutionIDs({institution})">

It simply passes the institution name to the getInstitutionIDs function with a bind.


Here is the getInstitutionIDs function:

<cffunction access="remote" name="getInstitutionIDs" output="false" returntype="array">
<cfargument name="institution"     type="string"     required="true">

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

<!--- Do search --->
<cfquery datasource="#request.dsn#" name="data">
SELECT intInstitutionID
FROM tblInstitutions
WHERE strInstitutionName = <cfqueryparam value="#institution#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>


<!--- Build result array --->
<cfloop query="data">
<cfset ArrayAppend(result, intInstitutionID)>
</cfloop>

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


Enjoy!

Bind data to an input field via autosuggest using data from another input field.

 here is my first input:

   <cfinput type="text"
        name="country" value="#form.country#" validateat="onSubmit" size="60" showAutoSuggestLoadingIcon="false"
        autosuggest="cfc:ajaxlookup.getCountries({cfautosuggestvalue})">

This is a simple CF autosuggest. It calls the getCountries function in the  ajaxlookup cfc. The CFC is in the same directory as my file. I don't know if you have to do this but it just works better for me. :)

This call will suggest countries as the person types the name of the country.

(showAutoSuggestLoadingIcon="false" means that little loading icon won't show.)

Here is the getCountries function:

 <!--- Country Lookup used for auto suggest --->
<cffunction access="remote" name="getCountries" output="yes" returntype="array">
<cfargument name="search" type="any" required="false" default="">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(1)>
<!--- Do search --->
<cfquery datasource="#request.dsn#" name="data">
SELECT     strCountry
FROM tblCountries
WHERE strCountry LIKE '#arguments.search#%'
ORDER BY strCountry
</cfquery>
<!--- Build result array --->
<cfloop query="data">
<cfset ArrayAppend(result, strCountry)>
</cfloop>
<!--- And return it --->
<cfreturn result>
    </cffunction>
   

So I want to send the country ID to the database, not the country name. So I am going to retrieve the country ID based on the country that the user selected in the above input.


Here is my 2nd input:


<cfinput type="hidden" name="countryID" id="countryID" bind="cfc:ajaxlookup.getCountryID({country})">

It uses the bind attribute to call the getCountry method while passing in the value of the country input.

Enjoy!