19 Replies Latest reply: Mar 6, 2012 5:40 AM by BKBK RSS

    Chaining select lists

    biene22

      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

        • 1. Re: Chaining select lists
          BKBK MVP

          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.

          • 2. Re: Chaining select lists
            biene22 Community Member

            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.

            • 3. Re: Chaining select lists
              BKBK MVP

              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?

              • 4. Re: Chaining select lists
                biene22 Community Member

                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.


                • 5. Re: Chaining select lists
                  BKBK MVP

                  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>

                  • 6. Re: Chaining select lists
                    biene22 Community Member

                    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.

                    • 7. Re: Chaining select lists
                      BKBK MVP

                      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>

                      • 8. Re: Chaining select lists
                        biene22 Community Member

                        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>

                        • 9. Re: Chaining select lists
                          BKBK MVP

                          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?

                          • 10. Re: Chaining select lists
                            biene22 Community Member

                            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>

                            • 11. Re: Chaining select lists
                              BKBK MVP

                              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.

                              • 12. Re: Chaining select lists
                                BKBK MVP

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

                                • 13. Re: Chaining select lists
                                  biene22 Community Member

                                  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!

                                  • 14. Re: Chaining select lists
                                    BKBK MVP

                                    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>

                                    • 15. Re: Chaining select lists
                                      BKBK MVP

                                      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.

                                      • 16. Re: Chaining select lists
                                        biene22 Community Member

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

                                        I think it's hopeless

                                        • 17. Re: Chaining select lists
                                          BKBK MVP

                                          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?

                                          • 18. Re: Chaining select lists
                                            biene22 Community Member

                                            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

                                            • 19. Re: Chaining select lists
                                              BKBK MVP

                                              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>