dependable drop-down? 1

[GUJUm0deL]

My client has a table called "locations" that has four columns (LID, country, state, city).

What I'd like to do from the UI is have 3 drop-down boxes; the user select a country (drop-down 1), depending on his selection the state drop-down gets populated, and when the user selects a state the city drop-down populated. How can I do this in CF? I'm going to running a query first that returns all country, state and city.

Now the kicker countries other then USA and Canada don't have states so in that case when those counties are selected only the city drop-drop gets populated.

Any ideas?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

australia has states, and canada has provinces, which function the same as states

see

http://www.indiankey.com/cfajax/examples/list.htm

r937.com | rudy.ca

 

[GUJUm0deL]

Hi Rudy, sorry it took some time to get back. I looked at the link and i'm not sure how to implement it. This is my setup (maybe there's another way of acheiving my goal)

TABLE:
LID Country State City
1 Africa N/A Johannesburg
2 Argentina N/A Buenos Aires
3 United States Arizona Phoenix
4 United States Arizona Tucson
5 United States California Los Angeles
6 United States California Sacramento
7 United States Wisconsin Madison
8 United States Wyoming Cheyenne
9 Venezuela N/A Maracaibo
10 Venezuela N/A Valencia

Now, when the user selects United States', the STATE select box should display all relevants state options (in my example above it should return Arizona, California, Wisconsin, Wyoming). Then when the user selects the state, lets say California, then the CITY select bx would return all relevant city options (in this scenario it should be: Los Angeles, Sacramento)

I only have one table that shows the country -> state -> city relationship.

How can I accomplish this? I liked the link you provided but i'm not sure how to implement that.


____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

you have two choices -- send the entire table to the browser and let javascript take care of it, or send only the countries, for the country dropdown, so that when a choice is made from the country dropdown, this refreshes the page and you fill in the states dropdown for the selected country, and do the same when the city is selected

r937.com | rudy.ca

 

[GUJUm0deL]

The second option is what I'm leaning towards. I was thinking of letting the user select the country, then (after page reloads) the state, then (another page reload) the city. But I was hoping there would be an easier method. With this method, I'd have to let the user choose the country/state/city first then have them enter the add property form.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

reloading the page is user-unfriendly

do a search for "related selects" -- plenty of good javascript solutions which assume you can send the entire table the first time (this is the easiest)

next best would be an ajax solution, in which the choice on the first select actually calls the server for the next select, except that it's not really a complete page refresh

reloading the page would be the least desirable solution

interestingly, reloading the page is also the fallback strategy if javascript is not enabled

r937.com | rudy.ca

 

[GUJUm0deL]

Hi Rudy,

I've done countless google searches on "dependent select boxes", but i'll do some for "related selects" and see what I get. I have found some (very) good options, but I haven't been able to get it to work for my scenario.

Some of the search results I liked were:

http://www.javascripttoolbox.com/lib/dynamicoptionlist/examples.php

http://www.webtricks.com/sourcecode/source_code.cfm?CodeID=18

http://www.pengoworks.com/qforms/docs/examples/n-related_selectboxes.htm

All these example show the JS createing arrays that hold the 1st, 2nd and 3rd option values and their relation. (I have to be honest, I was never good with arrays and their fromations so maybe that's why I can't get these examples to work)



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

do a search for "coldfusion related selects"

r937.com | rudy.ca

 

[GUJUm0deL]

Hi Rudy, well I found something off of easycfm that might help me. I got this far, then got stuck with a (non-descriptive) JS error.

You can see the code run here:

http://highestateliving.com/3.cfm

Any help is appreciated!

This is how I'm writing the code:

<cfquery name="qryLocationInfo" datasource="#DB#" username="#DB_Username#" password="#DB_Password#">
	SELECT	 distinct Country, State, City 
	FROM	 PropertyLocation
	group by Country, State, City
	ORDER BY Country, State, City
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Multiple three selects related</title>

<script language="JavaScript">
	//create the initial array to hold the different records.
	var aryLocation = new Array();
	
	<!--- We set a variable for our loop to insert the data. The loop starts at zero because JavaScript arrays start at zero. --->
	<cfset Variables.JSLoop = 0>
	
	<cfoutput>
		<cfloop query="qryLocationInfo">
			aryLocation[#Variables.JSLoop#] = new Array("#qryLocationInfo.Country#", "#qryLocationInfo.State#", "#qryLocationInfo.City#");
			<cfset Variables.JSLoop = Variables.JSLoop + 1>
		</cfloop>
	</cfoutput>
	
	function tsrUpdSelect(frstSel, scndSel, thrdSel, thisSel) {
		//local variables
		var i; var chkCty = "";
	
		//see if we just changed the first select box (state).
		if(thisSel.name == frstSel.name) {
			//set the length of the other selects to zero to empty them
			scndSel.options.length = 0;
			thrdSel.options.length = 0;
	
			//set the first option for each (being messages to do a select).
			scndSel.options[scndSel.length] = new Option("Choose State", "");
			thrdSel.options[thrdSel.length] = new Option("Choose City", "");
	
			//Now loop through and set the second option list (city).
			//If a state was chosen. There is also code to prevent duplicate cities. This could happen if a city had multiple addresses.
			if(thisSel.options[thisSel.selectedIndex].value != "") {
				for (i = 0; i < aryLocation.length; i++) {
					if(aryLocation[i][0] == thisSel.options[thisSel.selectedIndex].value && chkCty.lastIndexOf(aryLocation[i][1] == -1) {
						scndSel.options[scndSel.length] = new Option(aryLocation[i][1], aryLocation[i][1]); chkCty = chkCty + "," + aryLocation[i][1];
					}
				}
			}
		}
	
		//see if we just changed the second select box(state).
		if(thisSel.name == scndSel.name) {
			//set the length of the third select to zero.
			thrdSel.options.length = 0;
			//set the first option.
			thrdSel.options[thrdSel.length] = new Option("Choose City", "");
			//set the rest of the values if a city was chosen.
			for(i=0; i < aryLocation.length; i++) {
				if(aryLocation[i][0] == frstSel.options[frstSel.selectedIndex].value && aryLocation[i][1] == scndSel.options[scndSel.selectedIndex].value) { thrdSel.options[thrdSel.length] = new Option(aryLocation[i][2], aryLocation[i][2])
				}
			}
		}
	}
</script>
</head>

<body>
<cfoutput>
	<form name="testForm" method="post" action="">
	<table>
		<tr><th>Country</th><th>State</th><th>City</th></tr>
		<tr>
			<td>
				<cfset countryList = "">
				<select name="myCountry" onChange="tsrUpdSelect(this, testForm.myState, testForm.myCity, this);">
					<option value="" selected>Choose Country</option>
					<cfloop query="qryLocationInfo">
						<cfif not listFindNoCase(countryList, qryLocationInfo.Country, ",")>
							<option value="#qryLocationInfo.Country#"> #qryLocationInfo.Country# </option>
							<cfset countryList = listAppend(countryList, qryLocationInfo.Country, ",")>
						</cfif>
					</cfloop>
				</select>
			</td>
			<td>
				<select name="myState" onChange="tsrUpdSelect(testForm.myCountry, this, testForm.myCity, this);">
					<option value="">Choose State</option>
				</select>
			</td>
			<td>
				<select name="myCity">
					<option value="">Choose City</option>
				</select>
			</td>
		</tr>
	</table>
	</form>
</cfoutput>

<p>&nbsp;</p>

<cfdump var="#qryLocationInfo#">
</body>
</html>

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

BTW, the JS error is "object expected" at:

<select name="myCountry" onChange="tsrUpdSelect(this, testForm.myState, testForm.myCity, this);">

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

sorry, my javascript does not extend to debugging somebody's adaptation of somebody else's functions

:-(

r937.com | rudy.ca

 

[r937]

by the way, remember when i said sending the contents of all selects at once was only one of three solutions?

i found a tutorial which shows you how to do it with ajax (i.e. only one select at a time)

http://www.cfzone.net/ShowDetail.asp?NewsId=12654

interestingly enough, it uses countries as the first select

unfortunately it's not a free tutorial, it'll cost ya three bucks

r937.com | rudy.ca

 

[GUJUm0deL]

Hi Rudy, i'm not too worried about the $3 bucks as I am getting this to work the way I want it to. I looked at the link but I'd like to see an example before I get the PDF.

Anyways, lemme as you something:
from my link (

http://highestateliving.com/3.cfm)

if you do veiw source you see the JS array being formed from the query. This is a small excerpt:

aryLocation[119] = new Array("United States", "Utah", "Salt Lake City");
			
aryLocation[120] = new Array("United States", "Virginia", "Richmond");
			
aryLocation[121] = new Array("United States", "Washington", "Seattle");
			
aryLocation[122] = new Array("United States", "Wisconsin", "Madison");
			
aryLocation[123] = new Array("United States", "Wyoming", "Cheyenne");
			
aryLocation[124] = new Array("Venezuela", "", "Caracas");
			
aryLocation[125] = new Array("Venezuela", "", "Maracaibo");

I'm guessing that maybe JS is confused as to what to load when you select the country. If the JS array has many iteration of Country (like 'United States') then how does it know what State to populate in the state select box. Does that make sense?

I followed the code exactly as the author described even used the same function names and all. This is the tut from easycfm:

http://tutorial196.easycfm.com/

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

Hi Rudy, it works now. I posted the JS error question in the JS forum and someone realized there was a missing closing ')' tag. Once I closed it, the code worked out great.

Thanks.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

that;s good news

did you fix this?

http://highestateliving.com/3.cfm

because it doesn't work for canada

r937.com | rudy.ca

 

[GUJUm0deL]

Hey Rudy. Yeah I made the fix. You prolly checked the link before I uploaded the final changes.

What I'm doing is if any Country that does not have any State value I populate the array with 'No State, please select City'. So, now if you select 'Canada' as the Country, then the State field will have 'No State, please select City'. This allows the City field to be populated, and it notifies users that this Country has no state/province.

Not sure if this would be helpful, but I may write a FAQ on this (this seems to be a VERY popular delimma for developers). What do you think?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

definitely write it up as a FAQ,



r937.com | rudy.ca

 

[mamabird]

GUJUm0deL, I am currently going through this exact same issue and am struggling with it. I tried looking at your site at

http://highestateliving.com/3.cfm

but I get a File Not Found error so I can't see what you've done. Have you written an FAQ yet or do you have information to help out?

Thanks!
mamabird

 

[GUJUm0deL]

mamabird, yeah I took down that page (that was only created so I can post my issue in this thread). I haven't gotten the chance to write an FAQ on this.

I'll see if I can write a quick FAQ, or post the code on my site so you can see what i've done.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

Hey, I wrote a quick FAQ (faq232-6607). I hope it explains it well...

____________________________________
Just Imagine.

http://www.imajinarts.com