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!