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 -

7 Comments

LordRhumSifflar

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

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 post...it 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

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

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

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!!