Multi-Selects Related with AjaxCFC

Posted on Feb 14, 2007

A friend of mine was wondering how you would do two selects related using AjaxCFC. While Rob has similar tutorials on his site, I didn't see this one specifically. It is pretty easy to accomplish since Rob includes utilities in the AjaxCFC scripts that handle things like removing and adding options to a select.First, let's look at the component:

<cfcomponent extends="ajax">        <cffunction name="getFillQuery" output="no" access="private" returntype="query">       <cfargument name="itemID" required="true" type="numeric" />              <!--- I am faking the query for test purposes --->       <cfset var selectQuery = queryNew("display,id") />       <!--- this would be where you do your real query --->       <cfset var i = 0 />       <cfloop from="1" to="5" index="i">          <cfset queryAddRow(selectQuery) />          <cfset querySetCell(selectQuery,"display","selected #arguments.itemID# option #i#") />          <cfset querySetCell(selectQuery,"id",i) />       </cfloop>              <cfreturn selectQuery />    </cffunction> </cfcomponent>

There is not much going on here. Basically, I have a function that returns a query that, if this were real, would be filtered based upon an ID (or whatever criteria you want to send). Now, let's look at the form:

<html> <head> <script type='text/javascript'> _ajaxConfig = {   '_cfscriptLocation':'/sandbox/com/selectBoxes.cfc',             '_jsscriptFolder':'/sandbox/js',             'debug':false}; </script> <script type='text/javascript' src='/sandbox/js/ajax.js'></script> <script language="javascript">    var formItemToFill;    function callFill(thisSelect,target) {       formItemToFill = target;       DWREngine._execute(_ajaxConfig._cfscriptLocation, null, 'getFillQuery',thisSelect.options[thisSelect.selectedIndex].value,fill);    }    function fill(r) {       var getValue = function (thisRow) {          return thisRow.id; // name of the value column    }       var getText = function (thisRow) {          return thisRow.display; // name of the display column    }       DWRUtil.removeAllOptions(formItemToFill)       DWRUtil.addOptions(formItemToFill, r, [getValue, getText], null);    } </script> </head> <body onload="callFill(document.forms['myForm'].foo1,'foo2');"> <form name="myForm">    <select name="foo1" onchange="callFill(this,'foo2');">       <option value="1">Option 1</option>       <option value="2">Option 2</option>       <option value="3">Option 3</option>    </select>    <br />    <select name="foo2" id="foo2"></select> </form> </body> </html>

My callFill() function, for lack of a better name, is called when a new option is selected in my foo1 select box. I also call this function on the page load to pre-fill my second select box (foo2) with the initially selected item. The callFill() function simple calls my getFillQuery() function in my cfc and set fill() as the callback function. Notice how I have set a global JavaScript variable here to allow you to easily set the target select box you would like to fill.

When the data is returned, we set functions to return the data from the query we want used as the value and text of our select box options. Next, we use a built in AjaxCFC utility function to remove all options from the target select box. Finally, we use another built in utility function to add new options to the target using our query data.

That's it! Pretty easy once you are familiar with AjaxCFC. You could also easily expand this to add additional related select boxes as necessary.

Comments

Glenn Gervais Thanks Brian,

This looks simple now that someone else has done the work for me! ;)

Glenn

Posted By Glenn Gervais / Posted on 02/14/2007 at 6:57 AM

Rene Thanx for the example. But I'm new to AJAX and not very experienced with Javascript. I'm also Dutch, so please forgive me my English. Maybe you can help me.

Your example makes use of a numeric ID (itemID) that get passed to the selectBoxes.cfc (I suppose the number of the selected row of the 1st select box in the form). But what I need is to pass the _value_ of the selected row and that's a string (UUID). I've struggling with your code for a few hours, but don't know how to make it work.

Basically, the first selectbox is a loop from a query that outputs something like:
&lt;select name=&quot;Parent&quot;&gt;
&lt;cfloop query=&quot;getParentIitems&quot;&gt;
&lt;option value=&quot;#ParentID#&quot;&gt;#ParentTitle#&lt;/option&gt;
&lt;/cfloop&gt;
&lt;/select&gt;

Then in selectBoxes.cfc I want to catch the value of the selection (#ParentID#&quot; for the second query, something like:
&lt;cfquery ...&gt;
SELECT *
FROM Child
WHERE ParentID = '#arguments.Parent#'
&lt;/cfquery&gt;

Does this makes sense?

rene

Posted By Rene / Posted on 02/23/2007 at 7:25 AM

Brian Rinaldi Rene, ItemID is a numeric id from the db not the items position in the options array. Therefore, the code *should* not care whether this is a uuid or a numeric id since the JavaScript never specifically references it as though it were numeric. You will just need to change the names of the columns to fit your column names in the getValue and getText methods. Some of the ColdFusion code would change because of the reference, but that should be fairly self-explanatory. What problem are you running in to.

Posted By Brian Rinaldi / Posted on 02/23/2007 at 10:15 AM

Rene Brian,

the 2nd selectbox returns &quot;undefined&quot; when a record was found in the CFC and nothing when no record was found.

you use &quot;id&quot; and &quot;display&quot; in the Javascript. When I query the database in the CFC I want to get the columns &quot;ID&quot; and &quot;Titel&quot;. So I replaced your code with:

return thisRow.ID; // name of the value column

and

return thisRow.Titel; // name of the display column

my query in the CFC is straight forward:

&lt;cffunction name=&quot;getFillQuery&quot; output=&quot;no&quot; access=&quot;private&quot; returntype=&quot;query&quot;&gt;
&lt;cfargument name=&quot;itemID&quot; required=&quot;true&quot; type=&quot;string&quot; /&gt;
&lt;cfquery name=&quot;selectQuery&quot; datasource=&quot;#application.config.DSN#&quot;&gt;
SELECT ID, Titel
FROM toolkit_subdoelgroepen
WHERE DoelgroepID = '#arguments.itemID#'
ORDER BY Titel
&lt;/cfquery&gt;
&lt;cfreturn selectQuery /&gt;
&lt;/cffunction&gt;

Posted By Rene / Posted on 02/23/2007 at 1:22 PM

Brian Rinaldi Rene,

Simply based upon what you have provided I cannot see anything obviously wrong with your code. I would throw something like alert(thisRow.titel) in the getText() JS method (same for the id). This should create a popup with the values. If they still come up undefined, my best guess would be that you are running into some case-sensitivity issue here. Try something like thisRow.TITEL because sometimes CF returns query rows in all caps.

Posted By Brian Rinaldi / Posted on 02/25/2007 at 3:38 AM

Roberto F. Hello, at first I'd like to thanks for sharrin, and say tht's a nice code.

So, I'm tryin to modify for my case, I need 3 select boxes...

The first I have parameters like size... 1/2/3

In the DB, I will select the colors of the tips of clothes that is in arguments.itemID

Ok, I had the 1st and 2nd steps, but Now I wanna a 3rd select box, that I wanna the values from the first and secund select

like

&lt;cfqyery&gt;
Select * from tbl where size in (1stfield) and color in (2fiel).
&lt;/cfquery&gt;


I dont know so much about JS, Ajax Neither, so, how can I return the next variables?

Posted By Roberto F. / Posted on 05/05/2008 at 12:44 PM

Brian Rinaldi @Roberto Just repeat the steps here with an onchange on the second select box that calls another JS function that calls another method on your CFC. If you are running CF8, I have also shown how to do this (a little easier IMO) with cfajaxproxy which you can find at http://www.remotesynthesis.com/blog/index.cfm/2008/3/4/Multiselects-Related-with-CFAjaxProxy

Posted By Brian Rinaldi / Posted on 05/07/2008 at 4:19 AM

Roberto F. Ow, Thanks again
I've done it and it's right, changed the type to string, this way I could concat 2 variables and separate the information to acess another database.

Thanks

Posted By Roberto F. / Posted on 05/07/2008 at 6:10 AM

Eddie Hi Brian,

Firstly thanks for some great code. Much appreciated!

I am working with three select boxes, and they are all three working when you select them, however there is a small problem. When the page loads I want the third select box to also show a value, like the second select. I tried adding an additional onLoad function call to the body tag, however this doesn't work, despite having created two separate call and fill functions for each combination (foo1&2 and foo2&3).

The problem also extends further once the page has loaded. When you select the second select box, the third box is filled with related values. However when you change the first select box only the second select box is updated, which means that the values now selected in the third select box is not at all related to the first and second select boxes.

To avoid this the first call and fill function should ideally always call the second call and fill function and use the queries first returned row as the selected/default value for the second call and fill function.

That way each time the first select box is changed related values are supplied for boxes two and three.

I have a working example here: http://www.divtech.co.za/ajaxcfc-multiple-select.cfm

Can this be done and if so how? Any ideas, examples or comments would be welcome.

Posted By Eddie / Posted on 09/28/2008 at 11:37 AM

Eddie After doing some research I tried adding:

callFillLang(r.getValue[1],'foo3');

to my first fill function, but it still doesn't want to work.

Posted By Eddie / Posted on 09/28/2008 at 12:02 PM

Brian Rinaldi I haven't done this in a while but you shouldn't need separate onload code for the third related select since the contents should be loaded when the second select is data is loaded and changed. Also I would look at using cfajaxproxy if you are on cf8 rather than AjaxCFC. You can find my post on that here: http://www.remotesynthesis.com/post.cfm/Multiselects-Related-with-CFAjaxProxy

Posted By Brian Rinaldi / Posted on 09/29/2008 at 6:21 AM

Eddie I also thought that the second function call will automatically trigger when it is loaded, but no such luck. The javascript that fills the second select box doesn't trigger the onChange javascript call.

Unfortunately not on CF8 yet...

Posted By Eddie / Posted on 09/29/2008 at 6:26 AM

Ed S. If anyone is still interested in this (like Eddie, I am stuck with CF7 @ the moment), I figured out how to make this work with a 3-stage chained select, and have it reload the 2nd and 3rd ones when the first one changes.

Basically the method I did was add a remove all options on the 3rd select in the function for the 2nd one, passing the info via an additional variable on the function call.

I can send/post code if anyone needs it, feel free to email.

Posted By Ed S. / Posted on 06/03/2009 at 12:32 PM

Eddie I'm still very interested and would love to see how you have accomplished this. Please can you post the code here.

Posted By Eddie / Posted on 06/18/2009 at 6:41 AM

Ed S. I actually have it working with a 4-part chained select now.

I've got 2 sets of 4 chained selects on the same page, both get set to the default value from the DB query when the page loads. For some reason, I can't get the same function to load both of them onload(), so I have a duplicate set of functions just for the page onload, to pre-load the 2nd set of chained selects. not ideal, I know, but I needed something working until I get to CF8.

here' the JS from the main layout file that gets included at the top of the page:


----------

<!--- begin ajax stuff for loading pulldown lists (people, addresses, etc.) --->
      <script type='text/javascript'>_ajaxConfig = {'_jsscriptFolder':'css/ajaxCFCjs','debug':false};</script>
      <script type='text/javascript' src='css/ajaxCFCjs/ajax.js'></script>
      <script type="text/javascript">
      
      <!---- use ajaxCFC to chain selects --->
          var formItemToFill; <!--- global JS variables --->
         var formItemToFill2;
        var formItemToFill3;
         var formItemToFill4;
         var formItemToFill5;
         var formItemToFill6;
         
         <cfif CurrentShippingAddress.OffCampus eq 0>
            var SelectedShippingAddressCampus = '<cfoutput>#CurrentShippingAddress.Campus#</cfoutput>';
            var SelectedShippingAddressBuilding = '<cfoutput>#CurrentShippingAddress.Building#</cfoutput>'; <!--- these queries exist in profile forms, can get them here --->
            var SelectedShippingAddressRoomNumber = '<cfoutput>#CurrentShippingAddress.Room_Number#</cfoutput>';
         <cfelseif CurrentShippingAddress.OffCampus eq 1><!--- on campus, reassign fields to correct values --->
            var SelectedShippingAddressCampus = '<cfoutput>#CurrentShippingAddress.State#</cfoutput>';
            var SelectedShippingAddressBuilding = '<cfoutput>#CurrentShippingAddress.City#</cfoutput>'; <!--- these queries exist in profile forms, so we can get them here --->
            var SelectedShippingAddressRoomNumber = '<cfoutput>#CurrentShippingAddress.Street_Address1#</cfoutput><cfif CurrentShippingAddress.Street_Address2 neq ''><cfoutput> #CurrentShippingAddress.Street_Address2#</cfoutput></cfif>';
         </cfif>
         
         <cfif CurrentBillingAddress.OffCampus eq 0>
            var SelectedBillingAddressCampus = '<cfoutput>#CurrentBillingAddress.Campus#</cfoutput>';
            var SelectedBillingAddressBuilding = '<cfoutput>#CurrentBillingAddress.Building#</cfoutput>';
            var SelectedBillingAddressRoomNumber = '<cfoutput>#CurrentBillingAddress.Room_Number#</cfoutput>';
         <cfelseif CurrentBillingAddress.OffCampus eq 1><!--- on campus, reassign fields to correct values --->
            var SelectedBillingAddressCampus = '<cfoutput>#CurrentBillingAddress.State#</cfoutput>';
            var SelectedBillingAddressBuilding = '<cfoutput>#CurrentBillingAddress.City#</cfoutput>';
            var SelectedBillingAddressRoomNumber = '<cfoutput>#CurrentBillingAddress.Street_Address1#</cfoutput><cfif CurrentBillingAddress.Street_Address2 neq ''><cfoutput> #CurrentBillingAddress.Street_Address2#</cfoutput></cfif>';
         </cfif>   
// these functions are called onload, when changing the campus select, and when processing the add-new-address popup form.
         
         ///////////
         function callFillCampuses(thisSelect,target,secondtarget,thirdtarget) { <!--- //ShippingAddressOnOffCampus, ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID - all select elements. --->
            
               formItemToFill = target;
               formItemToFill2 = secondtarget;<!--- secondary target, clear this too on changing first select --->
               formItemToFill3 = thirdtarget;<!--- tertiary target, clear this too on changing first select --->
               DWREngine._execute('getCampuses.cfc', null, 'getCampusQuery',thisSelect.options[thisSelect.selectedIndex].value,fillCampuses);
            }

         function fillCampuses(r) { <!--- //shippingaddress --->
               var getValue =
                  function (thisRow){
                        return thisRow.campus; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.campus; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill);
               DWRUtil.addOptions(formItemToFill, r, [getValue, getText], null);
               DWRUtil.removeAllOptions(formItemToFill2); <!--- //secondtarget = ShippingAddress_Building - clear this when changing the primary select. --->
               DWRUtil.removeAllOptions(formItemToFill3); <!--- //thirdtarge = ShippingAddressID - clear this when changing the primary select. --->
               if (formItemToFill == 'ShippingAddress_Campus')
                  { DWRUtil.setValue(formItemToFill,SelectedShippingAddressCampus); }
               else if (formItemToFill == 'BillingAddress_Campus')
                  { DWRUtil.setValue(formItemToFill,SelectedBillingAddressCampus); }
               callFillBuildings(formItemToFill,formItemToFill2,formItemToFill3);<!--- // ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID --->
            }
            
         ////////
         
         function callFillBuildings(thisSelect,target,secondtarget) { <!--- //ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID - all select elements. --->
               formItemToFill2 = target;
               formItemToFill3 = secondtarget; <!--- secondary target, clear this too on changing first select --->
               
               if (formItemToFill2 == 'ShippingAddress_Building')
               { DWREngine._execute('getBuildings.cfc', null, 'getBuildingsQuery',document.forms[0].ShippingAddress_Campus.options[document.forms[0].ShippingAddress_Campus.selectedIndex].value,fillBuildings); }
               else if (formItemToFill2 == 'BillingAddress_Building')
               { DWREngine._execute('getBuildings.cfc', null, 'getBuildingsQuery',document.forms[0].BillingAddress_Campus.options[document.forms[0].BillingAddress_Campus.selectedIndex].value,fillBuildings); }
            }
            
         function fillBuildings(r) { <!--- //billingaddress & shpping address when called from form --->
               var getValue =
                  function (thisRow){
                        return thisRow.building; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.building; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill2);
               DWRUtil.addOptions(formItemToFill2, r, [getValue, getText], null);
               DWRUtil.removeAllOptions(formItemToFill3); <!--- //secondtarget = ShippingAddressID - clear this when changing the primary select. --->
               
               if (formItemToFill2 == 'ShippingAddress_Building')
                  { DWRUtil.setValue(formItemToFill2,SelectedShippingAddressBuilding); }
               else if (formItemToFill2 == 'BillingAddress_Building')
                  { DWRUtil.setValue(formItemToFill2,SelectedBillingAddressBuilding); }
               callFillRoom_Numbers_FirstTime(formItemToFill2,formItemToFill3); <!--- // ShippingAddress_Building, ShippingAddressID --->
            }
            
            
         function callFillRoom_Numbers_FirstTime(thisSelect,target) { <!--- this function exists since for some reason, we can't get the form thisSelect.selectedIndex.value to work w/o naming it specifically the first time through (onload of fillBuildings). ---->
               formItemToFill3 = target;
               if (formItemToFill3 == 'ShippingAddressID')
                  {
                     DWREngine._execute('getRoom_Numbers.cfc', null, 'getRoom_NumbersQuery',document.forms[0].ShippingAddress_Building.options[document.forms[0].ShippingAddress_Building.selectedIndex].value,document.forms[0].ShippingAddressOnOffCampus.options[document.forms[0].ShippingAddressOnOffCampus.selectedIndex].value,fillRoom_Numbers);
                  }
               else if (formItemToFill3 == 'BillingAddressID')
                  {
                     DWREngine._execute('getRoom_Numbers.cfc', null, 'getRoom_NumbersQuery',document.forms[0].BillingAddress_Building.options[document.forms[0].BillingAddress_Building.selectedIndex].value,document.forms[0].BillingAddressOnOffCampus.options[document.forms[0].BillingAddressOnOffCampus.selectedIndex].value,fillRoom_Numbers);
                  }   
            }
            
         
          function callFillRoom_Numbers(thisSelect,target) { <!--- // works for both, only called from form onchange, may not be needed...? e.g. use above firsttime func. --->
               formItemToFill3 = target;
               if (formItemToFill3 == 'ShippingAddressID') {
               
               DWREngine._execute('getRoom_Numbers.cfc', null, 'getRoom_NumbersQuery',thisSelect.options[thisSelect.selectedIndex].value,document.forms[0].ShippingAddressOnOffCampus.options[document.forms[0].ShippingAddressOnOffCampus.selectedIndex].value,fillRoom_Numbers); <!--- update this to use variable? --->
               
               }
               else if (formItemToFill3 == 'BillingAddressID')
               {
                  DWREngine._execute('getRoom_Numbers.cfc', null, 'getRoom_NumbersQuery',thisSelect.options[thisSelect.selectedIndex].value,document.forms[0].BillingAddressOnOffCampus.options[document.forms[0].BillingAddressOnOffCampus.selectedIndex].value,fillRoom_Numbers); <!--- update this to use variable? --->
               }
            }
         
         function fillRoom_Numbers(r) { <!--- // works for both, only called from form onchange --->
               var getValue =
                  function (thisRow){
                        return thisRow.addressid; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.room_number; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill3);
               DWRUtil.addOptions(formItemToFill3, r, [getValue, getText], null);
               if (formItemToFill3 == 'ShippingAddressID')
                  { DWRUtil.setValue(formItemToFill3,SelectedShippingAddressRoomNumber);} <!--- need a check in here to see if preselect room_number of street_address1 --->
               else if (formItemToFill3 == 'BillingAddressID')
                  { DWRUtil.setValue(formItemToFill3,SelectedBillingAddressRoomNumber);}   
            }
            
         <!--- These functions below here are duplicates of the above functions, and only exist since I couldn't get the above functions to execute correctly
         // during onload() for both sets of chained selects.
         // shippingaddress onload, only runs first time page loads (functions below here, and variables 4 5 and 6). for some reason, need to use separate funcs onload --->
         ///////////
         function callFillCampuses2(thisSelect,target,secondtarget,thirdtarget) { <!--- //ShippingAddressOnOffCampus, ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID - all radio & select elements. --->
            
               formItemToFill4 = target;
               formItemToFill5 = secondtarget;<!--- secondary target, clear this too on changing first select --->
               formItemToFill6 = thirdtarget;<!--- tertiary target, clear this too on changing first select --->
               
               DWREngine._execute('getCampuses.cfc', null, 'getCampusQuery',thisSelect.options[thisSelect.selectedIndex].value,fillCampuses2);
            }

         function fillCampuses2(r) { <!--- //shippingaddress --->
               var getValue =
                  function (thisRow){
                        return thisRow.campus; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.campus; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill4);
               DWRUtil.addOptions(formItemToFill4, r, [getValue, getText], null);
               DWRUtil.removeAllOptions(formItemToFill5); <!--- //secondtarget = ShippingAddress_Building - clear this when changing the primary select. --->
               DWRUtil.removeAllOptions(formItemToFill6); <!--- //thirdtarge = ShippingAddressID - clear this when changing the primary select. --->
               DWRUtil.setValue(formItemToFill4,SelectedShippingAddressCampus);
               callFillBuildings_FirstTime2(formItemToFill4,formItemToFill5,formItemToFill6);<!--- // New_Building, ShippingAddressID --->
            }
            
         ////////
         function callFillBuildings_FirstTime2(thisSelect,target,secondtarget) { <!--- //ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID - all select elements. --->
               formItemToFill4 = target;
               formItemToFill5 = secondtarget; <!--- secondary target, clear this too on changing first select --->
               DWREngine._execute('getBuildings.cfc', null, 'getBuildingsQuery',document.forms[0].ShippingAddress_Campus.options[document.forms[0].ShippingAddress_Campus.selectedIndex].value,fillBuildings2); <!--- for some reason can't get this programmatically first time through so hard-coded here... ---->
            }
         
         
         <!---Does callFillBuildings2 ever get executed? I think not.--->
         
          function callFillBuildings2(thisSelect,target,secondtarget) { <!--- //ShippingAddress_Campus, ShippingAddress_Building, ShippingAddressID - all select elements. --->
               formItemToFill4 = target;
               formItemToFill5 = secondtarget; <!--- secondary target, clear this too on changing first select --->
               DWREngine._execute('getBuildings.cfc', null, 'getBuildingsQuery',thisSelect.options[thisSelect.selectedIndex].value,fillBuildings2);
            }

         function fillBuildings2(r) { <!--- //shippingaddress --->
               var getValue =
                  function (thisRow){
                        return thisRow.building; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.building; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill4);
               DWRUtil.addOptions(formItemToFill4, r, [getValue, getText], null);
               DWRUtil.removeAllOptions(formItemToFill5); <!--- //secondtarget = ShippingAddressID - clear this when changing the primary select. --->
               DWRUtil.setValue(formItemToFill4,SelectedShippingAddressBuilding);
               callFillRoom_Numbers_FirstTime2(formItemToFill4,formItemToFill5); <!--- // New_Building, ShippingAddressID --->
            }
            
            
         function callFillRoom_Numbers_FirstTime2(thisSelect,target) {
               formItemToFill5 = target;
               DWREngine._execute('getRoom_Numbers.cfc', null, 'getRoom_NumbersQuery',document.forms[0].ShippingAddress_Building.options[document.forms[0].ShippingAddress_Building.selectedIndex].value,document.forms[0].ShippingAddressOnOffCampus.options[document.forms[0].ShippingAddressOnOffCampus.selectedIndex].value,fillRoom_Numbers2);   
               <!--- document.forms[0].ShippingAddressOnOffCampus.options[document.forms[0].ShippingAddressOnOffCampus.selectedIndex].value, --->
            }
            
            
            function fillRoom_Numbers2(r) {
               var getValue =
                  function (thisRow){
                        return thisRow.addressid; <!--- // name of the value column - ALL LOWER CASE! IMPORTANT! --->
                     }
               var getText =
                  function (thisRow) {
                        return thisRow.room_number; <!--- // name of the display column - ALL LOWER CASE! IMPORTANT! --->
                     }
               DWRUtil.removeAllOptions(formItemToFill5);
               DWRUtil.addOptions(formItemToFill5, r, [getValue, getText], null);
               DWRUtil.setValue(formItemToFill5,SelectedShippingAddressRoomNumber); <!--- need to check to see if select room num or street address --->
            }
      </cfif>
      <!--- end ajaxCFC chained selects test ---->


--------------------------

the cfcs just return a query result, with a couple of variables. Here they are:

getCampuses.cfc:

<cfcomponent extends="ajax">
   <cffunction name="getCampusQuery" output="no" access="private" returntype="query">
      <cfargument name="OnOffCampus" required="true" type="numeric"/><!--- expects 0 or 1 --->
<cfset getCampuses = 0 />
      <cfset VarToCheck = "" />
<cfif arguments.OnOffCampus eq 0><cfset VarToCheck = "Campus"><cfelseif arguments.OnOffCampus eq 1><cfset VarToCheck = "State"></cfif>
      <!--- cfif in here to check for campuses or cities based on OnOffCampus arg. then modify query below--->
<cfquery name="getCampuses" datasource="Datasource_name_here">
         SELECT DISTINCT #VarToCheck#
FROM Address
WHERE (Offcampus = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.OnOffCampus#">)
       AND (#VarToCheck# IS NOT NULL) AND (#VarToCheck# <> '')
ORDER BY #VarToCheck# DESC
</cfquery>
<cfset QueryAddRow(getCampuses)><!--- add blank row to beginning of record set --->
      <cfset QuerySetCell(getCampuses, "#VarToCheck#", "")>
<cfquery name="getCampuses" dbtype="query">
         SELECT #VarToCheck# as Campus
FROM getCampuses
ORDER BY Campus ASC
       </cfquery>
      <cfreturn getCampuses />
   </cffunction>
</cfcomponent>

---------------------

getBuildings.cfc:


<cfcomponent extends="ajax">
   <cffunction name="getBuildingsQuery" output="no" access="private" returntype="query">
      <cfargument name="CampusName" required="true" type="string" />
<cfset getBuildings = 0 />
      <cfset VarToCheck = "" />
<cfset StateOrCampus = "" />
<cfif len(arguments.CampusName) gte 4><cfset VarToCheck = "Building"><cfset StateOrCampus = "Campus"><cfelseif len(arguments.CampusName) lt 4><cfset VarToCheck = "City"><cfset StateOrCampus = "State"></cfif>
<cfquery name="getBuildings" datasource="Datasource_name_here">
         SELECT DISTINCT dbo.PROPERCASE(#VarToCheck#) as #VarToCheck#
FROM    Address
WHERE #StateOrCampus# = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#arguments.CampusName#">
AND (#VarToCheck# IS NOT NULL) AND (#VarToCheck# <> '')
ORDER BY #VarToCheck# ASC
      </cfquery>
<cfset QueryAddRow(getBuildings)><!--- add blank row to beginning of record set --->
      <cfset QuerySetCell(getBuildings, "#VarToCheck#", "")>
<cfquery name="getBuildings" dbtype="query">
         SELECT #VarToCheck# AS Building
FROM getBuildings
ORDER BY Building ASC
       </cfquery>
      <cfreturn getBuildings />
   </cffunction>
</cfcomponent>

-------------------

getRoomNUmbers.cfc:

<cfcomponent extends="ajax">
   <cffunction name="getRoom_NumbersQuery" output="no" access="private" returntype="query">
      <cfargument name="Building" required="true" type="string" />
       <cfargument name="OnOffCampus" required="true" type="numeric"/><!--- 0 = oncampus, 1= offcampus --->
<cfset getRoom_Numbers = 0 />
<cfif arguments.OnOffCampus eq 0 >
<cfquery name="getRoom_Numbers" datasource="datasource_name_here">
SELECT    AddressID, Room_Number
FROM    Address
WHERE Building = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="200" value="#arguments.Building#">
AND (Room_Number IS NOT NULL) AND (Room_Number <> '')
ORDER BY Room_Number ASC
</cfquery>
       <cfset QueryAddRow(getRoom_Numbers)><!--- add blank row to beginning of record set --->
<cfset QuerySetCell(getRoom_Numbers, "AddressID", 0)>
<cfset QuerySetCell(getRoom_Numbers, "Room_Number", "")>
<cfquery name="getRoom_Numbers" dbtype="query">
         SELECT AddressID, Room_Number
FROM getRoom_Numbers
ORDER BY Room_Number ASC
       </cfquery>
   <cfelseif arguments.OnOffCampus eq 1>
<CFSTOREDPROC PROCEDURE="getStreetAddresses" DATASOURCE="Datasource_name_here">
   <cfprocparam cfsqltype="cf_sql_varchar" dbvarname="City" value="#arguments.Building#" />
   <CFPROCRESULT NAME="getRoom_Numbers">
</CFSTOREDPROC>
      
       <cfset QueryAddRow(getRoom_Numbers)><!--- add blank row to beginning of record set --->
<cfset QuerySetCell(getRoom_Numbers, "AddressID", 0)>
<cfset QuerySetCell(getRoom_Numbers, "StreetAddress", "")>
<cfquery name="getRoom_Numbers" dbtype="query">
         SELECT AddressID, StreetAddress as Room_Number
FROM getRoom_Numbers
ORDER BY Room_Number ASC
       </cfquery>
</cfif>
   <cfreturn getRoom_Numbers />
   </cffunction>
</cfcomponent>


--------------------


here's the section of the form:

<table border=0 cellpadding="0" cellspacing="2" class="heading_table_center" style="margin-left:0px; padding-left:0px;">
<tr class="heading_item"><td style="border:0px solid ##FFF;">On/Off Campus</td><td style="border:0px solid ##FFF;">Campus</td><td style="border:0px solid ##FFF;">Building</td><td style="border:0px solid ##FFF;">Room Number</td></tr>
<tr class="heading_item">
<td style="border:0px solid ##FFF;"><select name="ShippingAddressOnOffCampus" id="ShippingAddressOnOffCampus" style="vertical-align:middle;" onchange="callFillCampuses(this,'ShippingAddress_Campus','ShippingAddress_Building','ShippingAddressID');" >
<!--- <option value=""></option> --->
<option value="0" <cfif (CurrentShippingAddress.OffCampus eq 0)>SELECTED</cfif>>On Campus</option>
<option value="1" <cfif (CurrentShippingAddress.OffCampus eq 1)>SELECTED</cfif>>Off Campus</option>
</select></td>
<td style="border:0px solid ##FFF;">
<select name="ShippingAddress_Campus" id="ShippingAddress_Campus" style="vertical-align:middle;" onchange="callFillBuildings(this,'ShippingAddress_Building','ShippingAddressID');"/>
</select></td>
<td style="border:0px solid ##FFF;"><select name="ShippingAddress_Building" id="ShippingAddress_Building" style="vertical-align:middle;" onchange="callFillRoom_Numbers(this,'ShippingAddressID');"/></select></td>
<td style="border:0px solid ##FFF;"><select name="ShippingAddressID" id="ShippingAddressID" style="vertical-align:middle;"/></select></td></tr>
</table>


<table border=0 cellpadding="0" cellspacing="2" class="heading_table_center" style="margin-left:0px; padding-left:0px;">
<tr class="heading_item"><td style="border:0px solid ##FFF;">On/Off Campus</td><td style="border:0px solid ##FFF;">Campus</td><td style="border:0px solid ##FFF;">Building</td><td style="border:0px solid ##FFF;">Room Number</td></tr>
<tr class="heading_item">
<td style="border:0px solid ##FFF;"><select name="BillingAddressOnOffCampus" id="BillingAddressOnOffCampus" style="vertical-align:middle;" onchange="callFillCampuses(this,'BillingAddress_Campus','BillingAddress_Building','BillingAddressID');" >
<!--- <option value=""></option> --->
<option value="0" <cfif (CurrentBillingAddress.OffCampus eq 0)>SELECTED</cfif>>On Campus</option>
<option value="1" <cfif (CurrentBillingAddress.OffCampus eq 1)>SELECTED</cfif>>Off Campus</option>
</select></td>
<td style="border:0px solid ##FFF;">
<select name="BillingAddress_Campus" id="BillingAddress_Campus" style="vertical-align:middle;" onchange="callFillBuildings(this,'BillingAddress_Building','BillingAddressID');"/>
</select></td>
<td style="border:0px solid ##FFF;"><select name="BillingAddress_Building" id="BillingAddress_Building" style="vertical-align:middle;" onchange="callFillRoom_Numbers(this,'BillingAddressID');"/></select></td>
<td style="border:0px solid ##FFF;"><select name="BillingAddressID" id="BillingAddressID" style="vertical-align:middle;"/></select></td></tr>
</table>


-------

Here's the onload:

   callFillCampuses(document.forms[0].BillingAddressOnOffCampus,'BillingAddress_Campus','BillingAddress_Building','BillingAddressID');
         callFillCampuses2(document.forms[0].ShippingAddressOnOffCampus,'ShippingAddress_Campus','ShippingAddress_Building','ShippingAddressID');
----------------




I happen to execute the onload inside a jquery document ready function, since it loads faster that way for some reason. But it could load in the normal body onload tag if desired.

There's a bunch of extra logic in my code, since I use the same functions to show chained selects for on and off campus addresses in this case - remapping some fields to do so. So that part is not relevant, but the rest is pretty much as streamlined as I could get it at this point. later I'll revise it when I get to CF8.

About the only inelegance in the above code is having to have a 2nd set of functions to deal with the onload (not sure why that wont' work), and the large number of (global) JS variables that get defined.

Good luck, let me know how it works out.

-Ed

Posted By Ed S. / Posted on 06/18/2009 at 7:56 AM

Write your comment



(it will not be displayed)





About

My name is Brian Rinaldi and I am the Web Community Manager for Flash Platform at Adobe. I am a regular blogger, speaker and author. I also founded RIA Unleashed conference in Boston. The views expressed on this site are my own & not those of my employer.