Cascading cfselects in CF 9

Report · Apr 01, 2012

I've done a good amount of searching, including in this forum, and I'm still missing some important understanding about how to have two or more cfselects in a cfform, each dependent on the previous one. The best reference I've found is from Ben Forta's blog (Surprise! Surprise!) http://www.forta.com/blog/index.cfm/2007/5/31/ColdFusion-Ajax-Tutorial-2-Related-Selects, but when I adapt that to my own database, I get "bind value is not a 2D array." I could probably do my cfselects with JQuery, but using components looks so good to me. Below is the code I'm using. One initial question relates to CFINVOKE. If my CFC has two or more functions, which one do I put here? I jsut guessed and put in the first one. Do I even need CFINVOKE? In his example, I don't think Ben even uses this tag. As an aside, I wish I had the DB that Ben uses, the cfartgallery. Unfortunately, I downloaded my CF and used a license key. I guess the sample DB is included only on media.

At any rate, if you see my error, please point it out. Understanding how cascading cfselects work would be a major step forward for me.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<h1>Select</h1>

<p>

Select something

</p>

<table>

<tr>

<td>Select Name:</td>

<td><cfselect name="address_book_ID"

bind="components:forta1.getName()"

bindonload="true" /></td>

</tr>

<tr>

<td>Select Email:</td>

<td><cfselect name="listID"

bind="components:forta1.getEmail()" /></td>

</tr>

</table>

</cfform>

</body>

</html>

SELECT address_book_ID, lastname

FROM address_book

ORDER BY lastname

</cfquery>

<!---

</cfloop>

--->

</cffunction>

SELECT email, listID

FROM email_list

WHERE listID = '120'

ORDER BY email

</cfquery>

<!---

</cfloop>

--->

</cffunction>

</cfcomponent>

Report · Apr 01, 2012

Mistake! I copied some code where I hard-coded a value. It's under "Get Data" and "WHERE list ID='120' the line should be

WHERE address_book_ID = #ARGUMENTS.address_book_ID#.

I still get the error with this line.

Report · Apr 01, 2012

I finally went ahead and just created a cfartgallery DB with the fields that Ben calls in his CFC. And populated both tables with one record. Still the same error message. "Bind value is not a 2D array."

Report · Apr 01, 2012

Do I even need CFINVOKE?

No. It is useful for testing your functions to verify they work. But it is not necessary for your form.

As an aside, I wish I had the DB that Ben uses, the cfartgallery. Unfortunately, I downloaded my CF and used a license key. I guess the sample DB is included only on media

It is part of the CF documentation. It is only installed if you select the "documentation" option during the installation.

"bind value is not a 2D array."

You are close, but it looks like you mixed up his example a bit.

1) His function returns an array. But yours is returning a query object (ie "data"). Hence the error. However, things have changed a bit since that article was written. CF8 only supported binding select lists to an array. CF9 supports binding with arrays OR queries. To make your current function work with a query, just supply the query columns you want to bind in your cfselects. For example, with getName():

SELECT address_book_ID, lastname

....

</cfquery>

<cfselect name="address_book_ID"

bind="components:forta1.getName()"

display="LastName"

value="address_book_id"

bindonload="true" /></td>

2) You dropped the bind relating the second list to the first one. You need to add it back so the list only displays the email for the selected name. Also be sure to add cfqueryparam in your actual query.

<cfselect name="listID"

bind="components:forta1.getEmail( {address_book_ID} )"

display="email"

value="listID" />

Message was edited by: -==cfSearching==-

Report · Apr 02, 2012

Thank you for your very helpful suggestions, which have enabled me to make some progress. I'll copy my current code below (I've changed tables) and followed your suggestions. Right now, I can populate the first dropdown with names and with CFDEBUG on, there are no errors. However, the second dropdown is empty. I can populate it if I hardcode a value in the query in the CFC function getDates(). You'll see I added a CFQUERYPARAM, as you suggested, but that doesn't seem to make any difference in the result. I also find that I can change the CFSELECT settings, but the person's name always appears, not their Emp_Id or the ModelSelected (which I tried just to see if something else would be the display). I'm closer to success, but I can't see what I'm missing now.

Component

SELECT Name, SelectionDate, Emp_Id, ModelSelected FROM Laptop_Selection ORDER BY Name SELECT Session_Date, Session_Name FROM Laptop_Rollout WHERE Session_Name= ORDER BY Session_Date

CFFORM

Select Your Name:	--------------Select Name--------------
Select Rollout Date:

Report · Apr 02, 2012

Looks like the forums mangled your code. Can you repost your current cffunctions and cfselect code again?

Report · Apr 02, 2012

Yes, I saw that problem (for some reason, on my machine at work, I can't copy and paste code as I can at home; so I tried pasting in the HTML editor, with the results that you saw) and was going to re-post the code tonight from home, but forgot my flash drive. At any rate, I got some help at work and the CFC is working perfectly! It's very cool. Tomorrow, I want to experiment with the values that the selects pass. I really need the employee ID passed when the user clicks Submit, not the ModelSelected, which I'm using only so that I can display an appropriate set of dates for each model. Someone suggested a hidden field in the CFFORM, but I'm not sure how that would work, since the CFSELECTS take only a 2D array. So, in short, in the first select, I want to display the user's name and pass the value of the ModelSelected to the second select, which displays specific dates for that model. Then when the user clicks Submit, I need to pass the date the user selected, along with the user's employee ID, to the action/confirmation page. If you have any thoughts on how to accomplish this, I would be glad to try out some scenarios. If it helps, I'll post the code in its present form tomorrow morning.

Report · Apr 02, 2012

Someone suggested a hidden field in the CFFORM, but I'm not sure how that would work, since the CFSELECTS take only a 2D array

.

They only work with two values, yes: list text and list value. However, you could return a concatenation of two id's ie "{id1}:{id2}". Then split the values when needed.

(for some reason, on my machine at work, I can't copy and paste code as I can at home; so I tried pasting in the HTML editor,

Yeah, I imagine the html editor would remove tag based code. You could always try the code formatter under [Advanced Editor] [ >> ] [Syntax Highlighting]

Report · Apr 03, 2012

I've made some progress in the last day, with code samples included below. There are three issues now.

The form doesn't pass the datetime data from the array. If I hardcode in a date, that goes to the action page. Otherwise, I see a zero. If I change the returntype="array" to returntype="query," then the datetime is passed. I can't figure that one out.
You can see that I pick up an Emp_Id in a query, but I can't include it in a 2D array. I see your suggestion above, which sounds like a neat solution, but I'm not sure how that would work. I imagine that I'd want the concatenation in the getNames function. In the CFLOOP? How can I pass that value to the action page? One suggestion I have is to use a hidden field. That would mean adding another function to the CFC, wouldn't it? And use Emp_Id as the argument and add the function below getDates? So this Emp_Id would be the ID of the user who just selected a date, and I would use that as a foreign key to do an INSERT and save the date the user chose.
And last, I don't understand how bind works with CFSELECT in this sense. These parameters, value="Session_Date" display="Session_Date," don't seem to matter. I can change them or even leave them out and still get the same display.

Finally, one last thought that you could weigh in on. Two hard-core programmers I work with both suggest that I skip the CFC route altogether and use JQuery to cascade the selects. My friends argue that in this way, I'll have total control over what happens on the page, rather than leaving it to CF Server to do something that's invisible and unknowable. I've done some searching already and don't find tutorials that really fit my situation (database-driven related selects). I'll continue to search, using broader search terms and leaving ColdFusion out of it. Just look for "JQuery and related selects" and variations on that theme. What is your perspective on an option like this?

Report · Apr 04, 2012

The code seems to have changed a lot since the earlier example, so I cannot really comment on most of this without seeing your latest.

I imagine that I'd want the concatenation in the getNames function. In the CFLOOP?

You could, but it is simpler to do the concatenation within your sql query (exact syntax is database specific). The query would return both ids in a single column. So your select list "value" would be something like "17:6" instead of "17". You could then split that string on ":" and extract whichever id you need.

And last, I don't understand how bind works with CFSELECT in this sense. These parameters, value="Session_Date" display="Session_Date," don't seem to matter. I can change them or even leave them out and still get the same display.

Again, I would have to see your new code but the "value" and "display" attributes do matter. When binding to a query, those attributes tell CF which query column it should use for the list text and which to use as the value.

What is your perspective on an option like this?

Truthfully it sounds like a lot of your current issues stem from newnesss to binding and the ajax debugging tools, rather than bugs with binding itself (though there are some of those too). But as far as binds vs jquery - it depends. Both have pros and cons. For simple lists, a bind is often simpler because they are already built in, so it is simpler to get things running. However, jquery offers more in terms of customization imo. But that comes at the cost of a little more complexity and a slightly steeper learning curve. So I would say it depends on your needs.

Report · Apr 05, 2012

You're right! This is my first production experience with CFCs. A programmer here made the CFC work by neatly creating a join in the second function, which gave me the empID and the session date that I needed. It's worthwhile going into components in depth because I'd like to use them for our future CF projects, which typically involve event registrations and various kinds of reporting from databases. So I very much appreciate your continued help. I learned enough to qualify as a mere beginner, but I want to continue to investigate the possibilities of components and binding. I should probably order a book on CF 9 (I'll bet Ben Forta is the main author here!) and delve into all the capabilities of this latest version. Ray Camden is another source I've used often and who has a tutorial on related selects. Now, when I search the Adobe CF forums, I'll also look for "cfSearching"! Thank you!

Adobe Community

Cascading cfselects in CF 9