Chaining select lists

Report · Feb 20, 2012

Hi,

I try to create two dynamic depending selectlists whereas the second select list depends on the choose of the first list.

Both lists are populated by a database query.

Here is a simple example:

First list: Usergroups

<option value="id">#usergroup_name#

</cfselect>

Second list: Users

<option value="id">#user_name#

</cfselect>

When selecting a usergroup in the first list, the query of the second list runs in dependence of the value of first list.

Is there a possibility to solve this problem without using Url-parameter? I have a big formular with many form-fields and I assume that this method is not very fast.

I also try to use data binding, but then I have a problem preselecting values in the lists.

Environment: Oracle 11g, ColdFusion 9 Standard

Any idea?

regards Claudia

Report · Feb 20, 2012

Example 2 in the Livedocs on cfselect is a fully worked-out example. It consists of 3 files: a CFC (BinFons.cfc) and an XML file(states.xml) and a CFM page containing the form and cfselect tags(call it, say, formPage.cfm). All you have to do is place the files in the same directory.

Report · Feb 20, 2012

Yes, I know this example and it works very well.

But how to preselect values in the selectboxes when loading the page for the first time?

I get the values from a database query and have to set this values as selected in the selectboxes.

Documentation said that "selected" attribute applies only if selection list items are generated from a query.

Report · Feb 20, 2012

biene22 wrote:
Yes, I know this example and it works very well.
But how to preselect values in the selectboxes when loading the page for the first time?
I get the values from a database query and have to set this values as selected in the selectboxes.
Documentation said that "selected" attribute applies only if selection list items are generated from a query.

What is the problem then?

Report · Feb 20, 2012

Okay, I try to make my problem more clearly by enhancing the example:

I have a customer formular which I open with the customer_id(as url parameter). In the database is a table

where customer_id, state and city is saved.

State and city are selectboxes. When I open the formular the selectbox should have selected the values saved

in the customer table.

select customer_id,state,city

from customers

where customer_id = #url.cust_id#

</cfquery>

<option name="0">--state--</option>

</cfselect>

</cfselect>

How I get the values customer.state and customer.city as selected in the listboxes?

I don't want to have <option name="0"> as selected values when opening the formular with a given customer_id.

I want to have for example state="California" and city="Beverly Hills" as selected values.

In my opinion it is only possible to preselect values when populating the listoxes using queries, but here the listbox is filled by bindings.

Report · Feb 21, 2012

biene22 wrote:
Okay, I try to make my problem more clearly by enhancing the example:
I have a customer formular which I open with the customer_id(as url parameter). In the database is a table
where customer_id, state and city is saved.
State and city are selectboxes. When I open the formular the selectbox should have selected the values saved
in the customer table.
<cfquery name="customer" datasource="test">
select customer_id,state,city
from customers
where customer_id = #url.cust_id#
</cfquery>
...
...
How I get the values customer.state and customer.city as selected in the listboxes?
...
...

The value url.cust_id appears to be the only choice to make. The query then tells you the customer, his state and his city. There are no more unknowns and, therefore, no need for lists.

You could simply do something with text fields, like this:

select custName,state,city

from customers

where customer_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.cust_id#">

</cfquery>

Customer: <cfinput type="text" name="customerName" value="#customerDetails.custName#">

State: <cfinput type="text" name="state" value="#customerDetails.state#">

City: <cfinput type="text" name="city" value="#customerDetails.city#">

</cfform>

Report · Feb 21, 2012

I don't want to have simple cfinput fields, I want selectboxes because it must be possible to change the

values of state and city. And therefore I need this chaining listboxes.

Report · Feb 22, 2012

biene22 wrote:
I don't want to have simple cfinput fields, I want selectboxes because it must be possible to change the
values of state and city. And therefore I need this chaining listboxes.

OK. I have a worked-out example that uses the database cfdocexamples. This database ships with ColdFusion, and comes installed as a datasource. It means you can run the example directly, without having to install anything.

The example illustrates the chaining you talk about. It supposes that you know the location, and that it is passed by URL. You wish that the page give you the departments in that location. When you click on the department, you want to get the list of employees in the department.

My database engine is MySQL. If yours is different, then you may have to slightly adapt some of my SQL statements. Place the 2 files in the same directory.

selectEmployee.cfm

Department: <cfselect name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

Employee: <cfselect name="emp" bind="cfc:Employee.getEmployees({dept})"></cfselect>

</cfform>

<div>

Form:

</div>

Employee.cfc

SELECT department

FROM employees

WHERE location = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#">

</cfquery>

</cfloop>

<!--- <cfloop query="departments">

</cfloop> --->

</cffunction>

SELECT Emp_ID, FirstName || ' ' || LastName as Name

FROM Employees

WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">

</cfquery>

</cfloop>

<!--- <cfloop query="employees">

</cfloop> --->

</cffunction>

</cfcomponent>

Report · Feb 29, 2012

Thanks for this example. It works fine, but my problem is:

how can I open selectEmployee.cfm and set for example department "Sales" as selected in

Report · Feb 29, 2012

biene22 wrote:
Thanks for this example. It works fine, but my problem is:
how can I open selectEmployee.cfm and set for example department "Sales" as selected in
<cfselect name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

I don't understand what you mean by "open selectEmployee.cfm". Could you please explain?

Report · Feb 29, 2012

How can I run selectEmployee.cfm in browser

and set for example department "Sales" as selected in

Report · Feb 29, 2012

biene22 wrote:
How can I run selectEmployee.cfm in browser
and set for example department "Sales" as selected in
<cfselect name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>

Ah, I see. I hope you understand that, in the example I gave, the argument in getDepartment is the location, not the department. However, you can easily modify the code to use department in place of location.

The first statement in selectEmployee.cfm tells you how to open the page. The URL scope means you should do it, for example, like this:

http://127.0.0.1:8500/bindTest/selectEmployee.cfm?location=Newton

(Remember that the path might be different on your own server.) Here, I have used the location Newton to override the default of San Francisco.

Report · Mar 02, 2012

Did you succeed in opening the CFM page the way I suggested? There are alternatives, of course.

Report · Mar 05, 2012

No, I think you don't understand me yet .

I try it again:

When you run selectEmployee.cfm for the first time no values are selected in both selectlists. They show "Select department" .

But I want to to have for example value "Sales" selected in first select list and "Peter Jacobson" in second select list.

I get this values from a query not from an url-parameter . The query looks like this:

SELECT * from employees where emp_id = #url.emp_id#

</cfquery>

Imagine url.emp_id = 7 then the result of this query is : department="Sales" . And this values should be selected in first selectlist

and value=7 ("Peter Jacobson") should be selected in the second select list

<cfselect name="emp" bind="cfc:Employee.getEmployees({dept})"></cfselect>.

This all should happen when you run the page selectEmployee.cfm for the first time.

And now when changing value of selectlist "dept" values of second selectlist should be changed (using binding).

So the very simple question is: How to use bindings and additionally set the "selected attribute" like this:

It is clear to me that this example make no sense in technical respect. But it's only an example!

Thanks in advance for your patience!

Report · Mar 06, 2012

I see what you mean. I could also see a few minor errors, which the following suggestion corrects.

selectEmployee.cfm

SELECT department, FirstName || ' ' || LastName as emp_name

FROM employees

WHERE emp_id = #url.emp_id#

</cfquery>

Department: <cfselect name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>

Employee: <cfselect name="emp" bind="cfc:Employee.getEmployees()" bindonload="true" selected="#select_user.emp_name#"></cfselect>

</cfform>

Employee.cfc

SELECT distinct(department)

FROM employees

</cfquery>

</cfloop>

</cffunction>

SELECT emp_id, FirstName || ' ' || LastName as Name

FROM Employees

</cfquery>

</cfloop>

</cffunction>

</cfcomponent>

Report · Mar 06, 2012

Now values are selected, but binding doesn't work anymore.

I think it's hopeless

Report · Mar 06, 2012

What do you mean by 'binding doesn't work anymore'. If you get the select lists, then binding is working.

I've just run a quick test. It works, at least on my system. What's your ColdFusion version?

Report · Mar 06, 2012

When I change the department, values of selectlist "employee" are always the same. They does not depend on the selected value of department.

I use ColdFusion 9 Standard

Report · Mar 06, 2012

biene22 wrote:
When I change the department, values of selectlist "employee" are always the same. They does not depend on the selected value of department.

ColdFusion 9 is as up to date as you can be. You could have experimented a bit with the above code. Binding to department is just a minor variation. For example,

selectEmployee.cfm

SELECT department, FirstName || ' ' || LastName as emp_name

FROM employees

WHERE emp_id = #url.emp_id#

</cfquery>

Department: <cfselect name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>

Employee: <cfselect name="employee" bind="cfc:Employee.getEmployees({dept})" bindonload="true" selected="#select_user.emp_name#" ></cfselect>

</cfform>

Employee.cfc

SELECT distinct(department)

FROM employees

</cfquery>

</cfloop>

</cffunction>

<cfargument name="dept" type="string" required="true">

SELECT emp_id, FirstName || ' ' || LastName as emp_name

FROM Employees

WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">

</cfquery>

</cfloop>

</cffunction>

</cfcomponent>

Report · Mar 06, 2012

So long as we understand that what we now have is experimental. It goes against the whole point of binding, which is to enable you to separate business code (like queries) from presentation code.

Adobe Community

Chaining select lists