Last week I helped a user who was having problems with a multi-related select application. You've probably seen the ColdFusion 8 related selects example before, where one drop down drives the content for another. His code was a bit more complex since he had three drop downs in play instead of two. The main problem he had though was in dealing with the second and third selects when the page loaded. After selecting items in his drop downs everything worked fine, but that initial load kept throwing errors.

Lets take a look at his front end code first:


<cfform name="Localiza">
<table>
<tr>
<td width="100">Division:</td>
<td width="150">
<cfselect name="SelDivision" bind="cfc:Places.GetDivision()"
		display="DsDivision" value="IdDivision" BindOnLoad="true"/></td></tr>
<tr>
<td width="100">State:</td>
<td width="150">
<cfselect name="SelState" bind="cfc:Places.GetState({SelDivision})"
		display="DsState" value="IdState" /></td></tr>
<tr>
<td width="100">County:</td>
<td width="150">
<cfselect name="SelCounty" bind="cfc:Places.GetCounty({SelDivision},{SelState})"
		display="DsCounty" value="IdCounty"/></td></tr>
</table>
</cfform>

Nothing too complex here. The first drop down asks a CFC for a list of divisions. The second drop down is bound to that value. The third drop down is then bound to the first two.

I had him simply debug that arguments being sent to the CFC methods, and as you can probably guess, the drop downs were getting null values for their arguments on the initial load.

To fix this, I recommended that both the getState and getCounty methods should look for empty values being passed. Here is the complete CFC. Notice in the two latter methods how he handles and responds to the empty values.


<cfcomponent>

<cfinclude template="QryPlaces.cfm">

    <cffunction name="GetDivision" access="remote" returnType="query">
        <cfquery name="LstDivision" dbtype="query">
            SELECT IdDivision, DsDivision
            FROM Division
            ORDER BY DsDivision
        </cfquery>
        <cfreturn LstDivision>
    </cffunction>

    <cffunction name="GetState" access="remote" returnType="query">
    <cfargument name="Division" type="any" required="true">
    <cfif ARGUMENTS.Division EQ "">
    	<cfset LstState = QueryNew("IdDivision, IdState, DsState", "Integer, Integer, Varchar")>
    <cfelse>
        <cfquery name="LstState" dbtype="query">
            SELECT IdDivision, IdState, DsState
            FROM State
            WHERE IdDivision = #ARGUMENTS.Division#
            ORDER BY DsState
        </cfquery>
    </cfif>
    <cfreturn LstState>
    </cffunction>

    <cffunction name="GetCounty" access="remote" returnType="query">
    <cfargument name="Division" type="any" required="true">
    <cfargument name="State" type="any" required="true">
	<cfif ARGUMENTS.Division EQ "" OR ARGUMENTS.State EQ "">
    	<cfset LstCounty = QueryNew("IdDivision, IdState, IdCounty, DsCounty", "Integer, Integer, Integer, Varchar")>
    <cfelse>
        <cfquery name="LstCounty" dbtype="query">
            SELECT IdDivision, IdState, IdCounty, DsCounty
            FROM County
            WHERE IdDivision = #ARGUMENTS.Division# AND
			IdState = #ARGUMENTS.State#
            ORDER BY DsCounty
        </cfquery>
    </cfif>
    <cfreturn LstCounty>
    </cffunction>

</cfcomponent>

Yes, he is missing var statements and cfqueryparam tags. So these changes did solve his initial load problem. I'd also probably recommend slimming down his queries a bit. So for example, the third drop down uses dsCount and IdCountry, but his CFC method is returning four columns. Now on one hand, it's nice to be able to use the exact same code for your Ajax code as you would for other code. But if this CFC is only being used for Ajax, he may want to trim down the query for performance reasons. While JSON is used to pass the data to the client and is pretty slim, there is no reason not to make that as small as possible.

Ok, so all in all, a rather simple issue, and I thought folks might like to see this in action. The user, Rigo, was kind enough to take his code and package it up with fake query data. You can download it below, and run a live demo here. Now for the kind of cool part. I noticed when running his demo that the three values for the drop downs were being displayed on the page. He did this with this code:


<cfdiv name="despliegue" bind="{SelDivision},{SelState},{SelCounty}"></cfdiv>

Note the bind statement. No URL, CFC, or JavaScript function is in use here. All he did was use the bound values. On a whim, I changed it to (and this is what you see in the live demo):


<cfdiv name="despliegue" bind="static - {SelDivision},{SelState},{SelCounty}"></cfdiv>

And this worked perfectly fine as well. I had no idea this would work, but I guess if you don't specify URL, CFC, or javascript, than ColdFusion treats it as a literal string result for the div. I'm not sure how I'd use that in production, but for debugging it's pretty useful.

Download attached file.