Triple Related Selects (and interesting CFDIV trick)

This post is more than 2 years old.

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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Alexander posted on 9/15/2008 at 4:57 AM

Great post - i'd like to see this be extended in the following way. Lets say the related selects are in a form with other inputs. If the form is submitted but is there a way to remember my selections on those related selects?

Comment 2 by Raymond Camden posted on 9/15/2008 at 4:59 AM

I've blogged before about pre-selecting values (mostly sure I did, use the search ;), but the short answer is that it tends to be a pain in the rear. I've yet to see a framework that makes that simpler. (Which doesn't mean it doesn't exist, just that I haven't seen it.)

Comment 3 by Rigo posted on 9/16/2008 at 8:17 PM

Ray: the cfdiv tip was taken from the FarrarĀ“s book the same as the idea of related selects with AJAX. But in the book, the author uses arrays to fill the selects and, like you say: "it's nice to be able to use the exact same code for your Ajax code as you would for other code", then I wanted to use just cfqueries.

Thanks for the article and the advices. Rigo

Comment 4 by Russ posted on 10/31/2008 at 3:04 AM

If you like that, you might like another twist as well. In one form I was working on I needed to call a JS function after some content loaded into a cfdiv, so I used something like this:

<cfselect name="Select1" id="Select1" bind="cfc:GetSelect1Arr()" bindonload="yes" />
<cfdiv name="Select2" id="Select2" bind="cfc:GetSelect2Arr({Select1})" bindonload="no" />
<cfinput type="hidden" name="JSTrigger" bind="javascript:YourJSFunctionHere({Select2.innerHTML})" bindonload="no">

Comment 5 by los posted on 6/18/2009 at 5:32 AM

At long last, I am delving into the CF8/Ajax world. I need to do exactly what the OP did (i.e. 3 related selects). I downloaded the code and ran it but none of the selects get populated. I was able to cfinvoke the methods with out a problem. What gives?

Comment 6 by Raymond Camden posted on 6/18/2009 at 6:25 AM

What does Firebug show you?

Comment 7 by Rigo posted on 6/18/2009 at 5:29 PM

los:

The data displayed are taken from the QryPlaces.cfm file. Check the cfinclude tag at the beginning of the Places.cfc referencing to QryPlaces.cfm

Comment 8 by los posted on 6/18/2009 at 5:46 PM

@Ray & @Rigo
Thanks for the quick response. In Firebug under Console, at the very bottom, I do get the data from the CFC returned:

{"COLUMNS":["IDDIVISION","DSDIVISION"],"DATA":[[1,"Pacific"],[3,"South Atlantic"],[2,"West South
Central"]]}

While I am not new to ColdFuison, I am new to the world of Ajax. So it's entirely possible that I have missed some trivial detail.

Comment 9 by Bill posted on 9/14/2009 at 6:56 PM

I've been challenged by using cfselect,hope someone can help.
I have multiple drop-downs that are populated from same database table, each by selecting distinct record. I first set it as the example in this thread when you select Country, populate State, when select State populate City etc. It works perfect. But the user wants that selection can be started at any drop-down, not like you have to select Country first as in this thread example. The drop-downs are related but they are not always hierarchy. And user want flexibility.
So my trouble is to display drop-downs so user can start selection at any one and populate the rest drop-downs.
I tried to add all arguments to each cfc and make bindonload true, using @change and @none when calling cfcs,
but could not get anywhere. Hope someone could point me to the right direction.

Thanks,
Bill

Comment 10 by Sebastiaan posted on 10/29/2009 at 2:28 AM

Hi Ray, I've been Googling until my fingers turned painful, but to no avail. No jQuery or CF-script out there that'll give me two selectboxes where it is possible to drag (or click via buttons) items from one selectbox to the other (without duplicating). Have you seen something like that anywhere, preferably a script that can be used several times on the same page ;-)

Comment 11 by Raymond Camden posted on 10/29/2009 at 5:50 PM

Have you tried the Sortable widget from jQuery UI?

http://jqueryui.com/demos/s...

Comment 12 by Sebastiaan posted on 10/30/2009 at 2:39 PM

Hi Ray, that URL doesn't seem to work for me, the whole domain jqueryui.com doesn't work for me, I keep getting "Unable to connect" in Shiretoko on Ubuntu (Karmic Koala - wow, that's some sweet piece of OS)... http://dev.jquery.com/ seems to work though, I'll see if I can find the same stuff there...

Comment 13 by Sebastiaan posted on 10/30/2009 at 3:09 PM

Probably my bad, had a fresh install of Karmic Koala and Shiretoko refused to cooperate. Now after some additional packages having been installed, the URL works for me. And yes, your example is somewhat what I need, at least the dragging and dropping between the two lists. Sorting within the list is not an issue right now.

I just need two selects, one with all available items (minus the ones already chosen) and one with the chosen items. And the ability to move those back and forth, preferably several at a time ;-)

Comment 14 by Brian Chignoli posted on 3/3/2010 at 12:10 AM

The sample code is no longer available for download. Could you post another link?

Comment 15 by Raymond Camden posted on 3/3/2010 at 12:14 AM

Eh? I just tried the Download link and it worked fine. Did you mean another link?

Comment 16 by Brian Chignoli posted on 3/3/2010 at 1:28 AM

You're right, I was clicking the link under "TweetBacks" by accident. Sorry. :)

Comment 17 by Albert posted on 3/16/2010 at 11:25 PM

Raymond,
I am glad to find this code, it is very useful.
just in case, how can I do blank first row in the first select if I use a result from the database query ?
Thanks a lot

Comment 18 by Raymond Camden posted on 3/18/2010 at 6:59 PM

I believe you would need to add a fake row in your query, or switch to using just JS to get/fetch the results. That would give you more control and allow you to add the blank row client side.

Comment 19 by Steve posted on 8/31/2010 at 9:05 PM

Hi Ray,

Have this working well (only using 2 chained selects).

However, I allow users to edit the form - and unfortunately I cannot get the 'selected = "xyz"' to work, so that if they come back to the form its highlighted/selected their previous answer.

Any suggestions?

Comment 20 by Raymond Camden posted on 8/31/2010 at 10:20 PM

That's a whole other issue. You have to write code that, on loads, updates the selects one by one. I've got an entry on the blog about that (with related selects), but don't remember the exact title. It's a pain. You can't just set the second drop down as it's values don't exist when the page is rendered.

Comment 21 by Andy posted on 8/4/2011 at 9:42 PM

Re: "You can download it below"... Where is the link to download?? There is an include <cfinclude template="QryPlaces.cfm"> that I'd like to see.

Comment 22 by Raymond Camden posted on 8/4/2011 at 10:11 PM

Right above the banner ad. In the gray-ish/dark-ish box.

Comment 23 by Andy posted on 8/4/2011 at 10:36 PM

Oops missed it. Got it now though... Thanks Raymond.

Comment 24 by Andy posted on 8/5/2011 at 12:06 AM

The .zip code works like a charm when I upload and test on my server... however when I try to customize I get three bind failed errors. I posted my problem to the Adobe CF Forums.

http://forums.adobe.com/mes...

Comment 25 by Marc posted on 2/22/2013 at 12:49 PM

Hi Ray, helpfull piece of code, thans for that :-)
How can i get the 3 values if i want store them in a database ?

Regards

Marc

Comment 26 by Raymond Camden posted on 2/23/2013 at 7:49 PM

Just use a regular form submit I suppose?

Comment 27 by Marc posted on 2/24/2013 at 12:20 AM

Hi,

I just copied the files on my server (CF8) but the dropdown lists are empty, any idea ?

Thanks

Comment 28 by Raymond Camden posted on 2/24/2013 at 3:55 AM

Nope, no idea. Have you done any basic debugging w/ something like Chrome Dev Tools? If you don't know what I mean, watch this preso:

http://www.raymondcamden.co...

Comment 29 by Marc posted on 2/24/2013 at 2:50 PM

Got this if i run FF debugger/check utility :

[10:51:31,684] SyntaxError: parseJSON @ http://********.com/CFIDE/scripts/ajax/package/cfajax.js:798

Comment 30 by Raymond Camden posted on 2/24/2013 at 9:22 PM

Look at the network tab and examine the XHR requests. They may have additional text in them breaking the JSON. I show a specific example of this in the video. If you tell me where your script is online, I can look quickly too.

Comment 31 by Marc posted on 2/24/2013 at 10:00 PM

sended you by email the url's

Comment 32 by Raymond Camden posted on 2/24/2013 at 11:01 PM

Marc, you *really* need to watch the video I pointed you too. I opened up Chrome Dev Tools. Switched to the Network tab. Looked at the XHR request and saw this in the response:

368?ms0?ms

HeadersPreviewResponseCookiesTiming

<link rel="stylesheet" href="css/ks.css" type="text/css" media="screen">
<link href='http://fonts.googleapis.com... rel='stylesheet' type='text/css'>

{"COLUMNS":["IDDIVISION","DSDIVISION"],"DATA":[[1,"Pacific"],[3,"South Atlantic"],[2,"West South Central"]]}

See the LINK tags? You probably have onRequestStart code in your App.cfc or stuff in an App.cfm automatically adding this to ALL requests. Therefore it breaks your JSON result.

Comment 33 by Sean posted on 1/30/2014 at 10:05 AM

Hi Raymond,

I am trying to submit the values from this select into a form.
As of now the values are id#'s
Is there a way, where the value="DsDivision.. actual text value".. can be submitted?

Thanks,

Comment 34 by Raymond Camden posted on 1/30/2014 at 7:46 PM

Sorry - what? Are you saying you want the text of a drop down to submit, not the value?