cfselect, binding, and selectedvalues

ColdFusion's cfselect doesn't support the selectedvalue argument when using binding. I don't like the JavaScript solutions out there, so here's a simple SQL solution. If you have a cfselect like this one that makes a call to a remote CFC then the selectedvalue is completely ignored when the field is populated: Remote.cfc: SELECT ID , CityName FROM City (NOLOCK) WHERE State_ID = ORDER BY CityName I've seen JavaScript solutions from Ray Camden and others to make this work, but it always just seemed like too much work, or involved changing/overriding core cfajax files. So why not just have the result you want be the first value returned from the query? With a simple CASE statement we can bubble our desired value to the top of the list so that when the select is populated it's the first (and by default selected) element. I just need to change my binding call and my function: Remote.cfc: SELECT ID , CityName FROM ( SELECT ID , CityName , CASE WHEN ID = THEN 0 ELSE 1 END AS Sort FROM City (NOLOCK) WHERE State_ID = ) d ORDER BY Sort, CityName Now when the query is returned, the ID that was passed as the selectedvalue argument will have a sort value of 0, and every other row will have a sort value of 1. Our ORDER BY statement then bubbles the first result to the top of the list.
Posted by Daniel Short on Jan 22, 2009 at 10:21 AM | Categories: ColdFusion -



LordRhumSifflar wrote on 02/16/09 5:39 AM

Well done. But, Do you know if (and how) we can do the same things with queryOfQuery? I've got an error executing database when i try to use this kind of query: SELECT ID , CityName FROM ( SELECT ID , CityName , CASE WHEN ID = THEN 0 ELSE 1 END AS Sort FROM queryCity (NOLOCK) WHERE State_ID = ) d ORDER BY Sort, CityName WHERE queryCity is already a query
Daniel Short

Daniel Short wrote on 02/16/09 8:12 AM

There are a few problems with that query: 1. You can't do a subselect in a QofQ. 2. The (nolock) keywords aren't necessary (and will throw an error). 3. You can't use a case statement in a QofQ. So to get around those three issues you can use the following query: SELECT ID , CityName , 0 AS Sort FROM queryCity WHERE ID = UNION ALL SELECT ID , CityName , 1 AS SORT FROM queryCity WHERE State_ID = ORDER BY Sort, CityName The way to get around most odd issues with QofQ limitations is clever use of Unions.

shekar wrote on 10/28/10 2:47 AM

Thanks a lot daniel..fixed a week's struggle..
Matt G

Matt G wrote on 04/15/11 7:26 AM

Great gave me the idea of instead of messing with the sql when returning an array just move the value of the array to the top of the pack init(); var returnArray = ArrayNew(2); var qCustomers = getcustomers(orderby='customer_name asc'); var i = 1; if (NOT selectValueOnTop){ returnArray[1][1] = 0; returnArray[1][2] = 'Choose one'; returnArray[1][3] = false; } for (i=1; i lte qCustomers.recordcount; i++){ if ( Compare(arguments.customer_id,qCustomers['customer_id'][i]) eq 0 and selectValueOnTop ) { returnArray[1][1] = qCustomers['customer_id'][i]; returnArray[1][2] = qCustomers['customer_name'][i]; returnArray[1][3] = true; }else{ returnArray[i+1][1] = qCustomers['customer_id'][i]; returnArray[i+1][2] = qCustomers['customer_name'][i]; returnArray[i+1][3] = false; } } return returnArray;

Ashraf wrote on 12/15/11 2:59 PM

Dan, Thanks for the article. It was easy to fix with your database solution instead of using JavaScript or other methods. Thanks again.

Junaid wrote on 11/16/12 6:27 AM

I don't know a thing about CFC.. How would I be able to achieve this functionality? I'm trying this for 7 straight days without any luck.. Please help me out with this. :(

Kim wrote on 05/02/13 10:05 AM

I just want to say THANK you for sharing this. I'm trying to set a default value but still allow the user to over-ride. Straight out binding doesn't allow this flexibility. Using the CASE used to direct the sort is so simple & totally solves my problem. Thanks again!!