Link 2 drop down lists on one form, limit list 1

[TheBlueDog]

I am programming in CF and have 2 drop down lists on one page which both contain I pull from an every-changing database. Although drop down list 1 is pretty small, drop down list 2 is very large and I would like to limit the contents of this long list based on what the user chooses for drop down list 1. (Common example would be choose cities based on a country chosen rather than showing all cities in the database regardless of the country chosen). I can do this of course submitting list 1 and then building list 2 on a page load, but I have too many multiple submissions already and would like to limit my list on the selection of list 1 without reloading.

I have seen some javascript where the values are hardcoded, has anyone built this using database-culled data instead?

Thanks for your help, my whole application will substantially improve if I can make this work!!

 

[ecobb]

There are several custom tags in the Macromedia Developer's Exchange that do this, 2 of them that I know of are called CF_ParentChildSelect, and Activeselect. They're free, so you can go to the web site, search for them, and download them.

Hope This Helps!

Ecobb
- I hate computers!

 

[tlhawkins]

Hey...

I'm sure I've answered this question before but I can't seem to find it... anyway here is the sample I have saved to remind myself how to do this every time I go to do it.

Maybe it will help:

  <SCRIPT Language=&quot;JavaScript&quot;>
  <!--
    function updatesel(form)
    {
      form.Two.length = 1;
      form.Two.selectedIndex = 0;
      choice = form.One.options[form.One.selectedIndex].value;
 if (choice == &quot;first&quot;)
 {
   <CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;4&quot;>
   <CFOUTPUT>
     (form.Two.length)++;
     form.Two.options[form.Two.length - 1].text   = &quot;SubChoice #x#&quot;;
     form.Two.options[form.Two.length - 1].value = &quot;SC#x#&quot;;
   </CFOUTPUT>
   </CFLOOP>
 }
 if (choice == &quot;second&quot;)
 {
   <CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;6&quot;>
   <CFOUTPUT>
     (form.Two.length)++;
     form.Two.options[form.Two.length - 1].text = &quot;Second SubChoice #x#&quot;;
     form.Two.options[form.Two.length - 1].value = &quot;SC#x#&quot;;
   </CFOUTPUT>
   </CFLOOP>
 }
}
//-->
</script>

<FORM METHOD=&quot;POST&quot; ACTION=&quot;look2.cfm&quot; NAME=&quot;myform&quot;>
<SELECT NAME=&quot;One&quot; 
        OnChange=&quot;updatesel(this.form)&quot; 
        WIDTH=&quot;250&quot;>
  <OPTION VALUE = &quot;%&quot;>All</OPTION>
  <OPTION VALUE = &quot;first&quot;>First choice</OPTION>
  <OPTION VALUE = &quot;second&quot;>Second choice</OPTION>
</SELECT>
Look
<SELECT NAME=&quot;Two&quot; WIDTH=&quot;250&quot; >
  <OPTION VALUE = &quot;%&quot;>All</OPTION>
  <CFLOOP index=&quot;x&quot; From=&quot;1&quot; to=&quot;6&quot;>
    <CFOUTPUT>
      <OPTION VALUE = &quot;&quot;>000000000000000000000000</OPTION>
    </CFOUTPUT>
  </CFLOOP>
</select>
</FORM>

have fun.


Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[TheBlueDog]

Many thanks to you both for responding, I will get to work this morning!!
-Kim

 

[CidMatrix]

If you can't get this code to work, you can also try looking for CF_TwoSelectsRelated from the DevExchange. I've used this with a lot of success!! (Can't say the same with CF_ThreeSelectsRelated as you can see from my post today).

I'm hoping by looking at tlhawkins code, maybe I can customized it so that it works with three selects.

 

[CidMatrix]

OK, I've tried to modify "tlhawkins" code so that it works with THREE selects linked to a query and I can't get it to work. Again, my java script skills are zero to null. So I must apologize.

If tlhawkins, or anyone for that matter can modify the code so that it works for three selects AND that I can populate the list dynamically from a query, that would be great!!

 

[erwino]

OK, here is what I am using to create a list of options from the database:
<code>
function create_list (filter) {
var INV_ARRAY = new Array(3);
<cfoutput>
INV_ARRAY[0] = new Array(#query_name.RecordCount#);
INV_ARRAY[1] = new Array(#query_name.RecordCount#);
INV_ARRAY[2] = new Array(#query_name.RecordCount#);
</cfoutput>
// fill array with all options.
<cfoutput query=&quot;query_name&quot;>
INV_ARRAY[0][#CurrentRow#] = #id#; //value
INV_ARRAY[1][#CurrentRow#] = &quot;#description#&quot;; //text
INV_ARRAY[2][#CurrentRow#] = #spec_id#; //spec_id
</cfoutput>
<cfoutput>
// first, remove all what is in the listbox
while (formname.listname.options.length!=0)
formname.listname.options.remove(0);
// now, fill it !
for (var m=0; m<#query_name.RecordCount#; m++) {
if (INV_ARRAY[2][m] == filter){
var oOption = document.createElement(&quot;OPTION&quot;
oOption.text=INV_ARRAY[1][m];
oOption.value=INV_ARRAY[0][m];
formname.listname.options.add(oOption)
}</cfoutput>
}
return true;
}
</code>

In this function the data retruned has 3 values, the id for the list, the description, and a spec_id to filter with. THis is the id from the (in your case) first list, put a onchange event in the list that triggers the create_list(this.form.list1.value) function.
Your list is then sorted with the first list criteria.
Set a default filter value to show a predefined selection (the first option in the list)
Hope this helps.
Erwin

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[CidMatrix]

Thanks for the code. I'll give it a try later on today or tomorrow. I'm currently having an issue with the &quot;Cold Fusion Application Server&quot; service. It keeps getting hung and I get Location Code: 26 errors. Don't know what could be causing. No new code was introduce recently and everything was working fine before. Oh well, I have to get back to my research. Thanks again.

 

[CidMatrix]

OK Erwin. I took your code and modified it so that I could use it. I've only done it for two selects so far (I dout that adding a third select in this particular case would be very much different). Anyway, I think I'm almost there. The second select does updated based on what the 1st one has; howeve, my only problem is that the second select only shows the first option, not the entire list. Since I'm not very good at java scripting, I was wondering if you could take a look at the code and let me know what I'm doing wrong. I do want to note that when the page first loads up the second list does populate completely, it is only after I make a change in the second select that I get only one option.

Here it is:

<SCRIPT Language=&quot;JavaScript&quot;>
function create_list () {
var INV_ARRAY = new Array(3);
<cfoutput>
INV_ARRAY[0] = new Array(#getInterTitles.RecordCount#);
INV_ARRAY[1] = new Array(#getInterTitles.RecordCount#);
INV_ARRAY[2] = new Array(#getInterTitles.RecordCount#);
</cfoutput>
// fill array with all options.
<cfoutput query=&quot;getInterTitles&quot; group=&quot;GrpID&quot;>
INV_ARRAY[0][#CurrentRow#] = &quot;#PosID#&quot;; //value
INV_ARRAY[1][#CurrentRow#] = &quot;#PosDesc#&quot;; //text
INV_ARRAY[2][#CurrentRow#] = &quot;#GrpID#&quot;; //spec_id
</cfoutput>
<cfoutput>
// first, remove all what is in the listbox
while (myform.Two.options.length!=0)
myform.Two.options.remove(0);
// now, fill it !
for (var m=0; m < #getInterTitles.RecordCount#; m++) {
if (INV_ARRAY[2][m] == document.myform.One.value){
var oOption = document.createElement(&quot;OPTION&quot;
oOption.text=INV_ARRAY[1][m];
oOption.value=INV_ARRAY[0][m];
myform.Two.options.add(oOption)
}</cfoutput>
}
return true;
}
</script>


And here are my selects:

<FORM METHOD=&quot;POST&quot; ACTION=&quot;look2.cfm&quot; NAME=&quot;myform&quot;>
<SELECT NAME=&quot;One&quot;
OnChange=&quot;create_list(this.form)&quot;
WIDTH=&quot;250&quot;>
<CFOUTPUT QUERY=&quot;getInterTitles&quot; GROUP=&quot;GrpID&quot;>
<OPTION VALUE = &quot;#GrpID#&quot;
<CFIF GrpID EQ Default1>SELECTED</CFIF>>
#GroupDesc#
</OPTION>
</CFOUTPUT>
</SELECT>
Look
<SELECT NAME=&quot;Two&quot; WIDTH=&quot;250&quot; >
<CFOUTPUT QUERY=&quot;getInterTitles&quot; GROUP=&quot;PosID&quot;>
<OPTION VALUE = &quot;#PosID#&quot;>#PosDesc#</OPTION>
</CFOUTPUT>
</select>
</FORM>

Lastly, I'm assuming I'm handling the default correctly (within the select statement) if you think it is better to do it within the script, if you could show me how, that would be great.

Thanks again.

 

[erwino]

Had a quick look, this is one thing that I've spotted.

<code>
OnChange=&quot;create_list()&quot; NOT create_list(this.form)
</code>

Also noted that you are using the same query, therefore you are confusing yourself. Have a look at the 'fill aray with all options' (took me a bit of time too ;-) Maybe run 2 queries, one with the GrpID and GroupDesc, the other with PosID, GrpID and Desc, normally these should be in 2 tables.
GrpID - GrpDesc linked to PosID - Desc - GrpID by GrpID.)

You are using the group by and this will only get you one option from the group. (i.e. only one record is loaded into the list) Take the group out of <code><cfoutput query=&quot;getInterTitles&quot; group=&quot;GrpID&quot;></code> and I think you are cooking with gas.
Good luck

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[tlhawkins]

Hi guys,

I put together a sample of how to do 2 select and 3 select dynamic drop down boxes. You can find them at:

http://www.freecfm.com/t/tlhawkins/fully_dynamic_select3.cfm

http://www.freecfm.com/t/tlhawkins/fully_dynamic_select2.cfm

They are hosted on a free CFM hosting account so it may be down a lot, but if you can get through it has a sample, the code, and the DB you need to get started making your own.
Have fun.


Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[CidMatrix]

Erwino,

I made the modifications you suggested. The only reason I was using only one query before was because I just went on the assumption that this was the preferred method based on the custom tag &quot;ThreeSelectsRelated&quot;.

I got the two selects two work, thank you. The issues I have are this. First, again, the default works OK with the first selects, but, not for the second select. The second select does get filtered properly based on the default of the first, but, the default for the second is not selected. This is true even though I do have a CFIF statement when building the second select. I'm thinking that the javascript is ocurring after the CFIF, or something like that.

The other thing was that I was trying to modify the code so that it is easily reusable by not having anything static within it, but, I kept getting a bunch of &quot;not defined&quot; errors. Not too much worried about this as I am about the defaults, just more of an FYI.

Thanks again for all your help guys.

 

[tlhawkins]

You got it exactly...

CFM is server side, javascript is client side. So any changes you make with javascript are going to supercede the settings of the CFM. The examples I've given uses CFM to create the javascript, so it can get a little confusing as to which language is setting the variables. I would add a javascript IF statement to set the defaults when you change a selection.




Travis Hawkins
BeachBum Software
travis@cfm2asp.com

http://www.beachbumsoftware.com

 

[CidMatrix]

Thanks. So I'm not crazy.

Well, then, could someone please verify the following code for me. Better, yet, tell me what I'm doing wrong.
=============>>>>>>>>>>>
{
if (INV_ARRAY[0][m] == &quot;#Select2_Default#&quot
document.MyForm.PosID.options.selectedIndex = INV_ARRAY[0][m];
}
<<<<<<<<<<<=================

I'm adding it after this line:

myform.Two.options.add(oOption)

Thanks.

 

[erwino]

{
if (INV_ARRAY[0][m] == &quot;#Select2_Default#&quot
document.MyForm.PosID.options.selectedIndex = INV_ARRAY[0][m];
}
if (array[posititon 0][position m] == &quot;whatever is set as Select2_Default in CFM&quot set the select to that option.
This means that when the code is runnin on the page, you will alway's select the same option based on the variable that you passed from CFM. You have to assign a javascript var to hold the Select2_Default for the first walkthrough, and set it to the selection made in another list when the user interact with it.
As mentioned by tlhawkins CFM is processed before the page is loaded, Javascript runs when the user interacts with the page.

Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.