Copy link to clipboard
Copied
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
<cfselect name="usergroups" >
<cflloop query="select_usergroup">
<option value="id">#usergroup_name#
</cfselect>
Second list: Users
<cfselect name="users" >
<cflloop query="select_user">
<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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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>
<cfselect name="state" bind="cfc:bindFcns.getstates()" bindonload="true">
<option name="0">--state--</option>
</cfselect>
<cfselect name="city" bind="cfc:bindFcns.getcities({state})">
<option name="0">--city--</option>
</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.
Copy link to clipboard
Copied
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:
<cfparam name="url.cust_id" default="1234">
<cfquery name="customerDetails" datasource="test">
select custName,state,city
from customers
where customer_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.cust_id#">
</cfquery>
<cfform name="customerForm">
Customer: <cfinput type="text" name="customerName" value="#customerDetails.custName#"> <br>
State: <cfinput type="text" name="state" value="#customerDetails.state#"> <br>
City: <cfinput type="text" name="city" value="#customerDetails.city#"> <br>
<cfinput name="sbmt" type="submit" value="Send">
</cfform>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
<!--- The location is expected to come in as a URL parameter. The bind requires it when the page loads, so a default location, San Francisco, is set. --->
<cfparam name="url.location" default="San Francisco">
<cfform name="empForm">
<cfinput name="loc" type="hidden" value="#url.location#" >
Department: <cfselect name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>
<br>
Employee: <cfselect name="emp" bind="cfc:Employee.getEmployees({dept})"></cfselect>
<br><br>
<cfinput name="sbmt" type="submit" value="Submit">
</cfform>
<div>
Form:
<cfdump var="#form#">
</div>
Employee.cfc
<cfcomponent>
<cffunction name="getDepartments" access="remote" output="false" returntype="array">
<cfargument name="location" type="string" required="true">
<cfset var departments = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "departments" dataSource = "cfdocexamples">
SELECT department
FROM employees
WHERE location = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.location#">
</cfquery>
<cfset arr[1][1]="">
<cfset arr[1][2]="Select department">
<cfloop query="departments">
<cfset arr[currentrow+1][1]=department><!---option values in the select list--->
<cfset arr[currentrow+1][2]=department><!---displayed values in the select list --->
</cfloop>
<!--- Alternative, if you require no "Select department" option --->
<!--- <cfloop query="departments">
<cfset arr[currentrow][1]=department><!---option values in the select list--->
<cfset arr[currentrow][2]=department><!---displayed values in the select list --->
</cfloop> --->
<cfreturn arr>
</cffunction>
<cffunction name="getEmployees" access="remote" output="false" returntype="any">
<cfargument name="dept" type="string" required="true">
<cfset var employees = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "employees" dataSource = "cfdocexamples">
SELECT Emp_ID, FirstName || ' ' || LastName as Name
FROM Employees
WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">
</cfquery>
<cfset arr[1][1]="">
<cfset arr[1][2]="Select employee">
<cfloop query="employees">
<cfset arr[currentrow+1][1]=emp_id><!---option values in the select list--->
<cfset arr[currentrow+1][2]=name><!---displayed values in the select list --->
</cfloop>
<!--- Alternative, if you require no "Select employee" option --->
<!--- <cfloop query="employees">
<cfset arr[currentrow][1]=emp_id><!---option values in the select list--->
<cfset arr[currentrow][2]=name><!---displayed values in the select list --->
</cfloop> --->
<cfreturn arr>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Did you succeed in opening the CFM page the way I suggested? There are alternatives, of course.
Copy link to clipboard
Copied
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:
<cfquery name = "select_user" dataSource = "cfdocexamples">
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
<cfselect name="dept" bind="cfc:Employee.getDepartments({loc})" bindonload="true"></cfselect>
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:
<cfselect name="emp" bind="cfc:Employee.getEmployees({dept})" selected="#select_user.department#"></cfselect>
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!
Copy link to clipboard
Copied
I see what you mean. I could also see a few minor errors, which the following suggestion corrects.
selectEmployee.cfm
<!--- The employee ID (emp_id) is expected to come in as a URL parameter. The bind requires it when the page loads, so a default emp_id of 7 is set. --->
<cfparam name="url.emp_id" default="7">
<cfquery name = "select_user" dataSource = "cfdocexamples">
SELECT department, FirstName || ' ' || LastName as emp_name
FROM employees
WHERE emp_id = #url.emp_id#
</cfquery>
<cfform name="empForm">
Department: <cfselect name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>
<br>
Employee: <cfselect name="emp" bind="cfc:Employee.getEmployees()" bindonload="true" selected="#select_user.emp_name#"></cfselect>
<br><br>
<cfinput name="sbmt" type="submit" value="Submit">
</cfform>
Employee.cfc
<cfcomponent>
<cffunction name="getDepartments" access="remote" output="false" returntype="array">
<cfset var departments = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "getDept" dataSource = "cfdocexamples">
SELECT distinct(department)
FROM employees
</cfquery>
<cfloop query="getDept">
<cfset arr[currentrow][1]=department><!---option values in the select list--->
<cfset arr[currentrow][2]=department><!---displayed values in the select list --->
</cfloop>
<cfreturn arr>
</cffunction>
<cffunction name="getEmployees" access="remote" output="false" returntype="any">
<cfset var employees = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "employees" dataSource = "cfdocexamples">
SELECT emp_id, FirstName || ' ' || LastName as Name
FROM Employees
</cfquery>
<cfloop query="employees">
<cfset arr[currentrow][1]=emp_id><!---option values in the select list--->
<cfset arr[currentrow][2]=name><!---displayed values in the select list --->
</cfloop>
<cfreturn arr>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
Now values are selected, but binding doesn't work anymore.
I think it's hopeless
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
<!--- The employee ID (emp_id) is expected to come in as a URL parameter. The bind requires it when the page loads, so a default emp_id, 7, is set. --->
<cfparam name="url.emp_id" default="6">
<cfquery name = "select_user" dataSource = "cfdocexamples">
SELECT department, FirstName || ' ' || LastName as emp_name
FROM employees
WHERE emp_id = #url.emp_id#
</cfquery>
<cfform name="empForm">
Department: <cfselect name="dept" bind="cfc:Employee.getDepartments()" bindonload="true" selected="#select_user.department#"></cfselect>
<br>
Employee: <cfselect name="employee" bind="cfc:Employee.getEmployees({dept})" bindonload="true" selected="#select_user.emp_name#" ></cfselect>
<br><br>
<cfinput name="sbmt" type="submit" value="Submit">
</cfform>
Employee.cfc
<cfcomponent>
<cffunction name="getDepartments" access="remote" output="false" returntype="array">
<cfset var departments = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "getDept" dataSource = "cfdocexamples">
SELECT distinct(department)
FROM employees
</cfquery>
<cfloop query="getDept">
<cfset arr[currentrow][1]=department><!---option values in the select list--->
<cfset arr[currentrow][2]=department><!---displayed values in the select list --->
</cfloop>
<cfreturn arr>
</cffunction>
<cffunction name="getEmployees" access="remote" output="false" returntype="any">
<cfargument name="dept" type="string" required="true">
<cfset var employees = queryNew("","")>
<cfset var arr = arrayNew(2)>
<cfquery name = "employees" dataSource = "cfdocexamples">
SELECT emp_id, FirstName || ' ' || LastName as emp_name
FROM Employees
WHERE department = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dept#">
</cfquery>
<cfloop query="employees">
<cfset arr[currentrow][1]=emp_id><!---option values in the select list--->
<cfset arr[currentrow][2]=emp_name><!---displayed values in the select list --->
</cfloop>
<cfreturn arr>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
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.