How do you display certain fields in a bind to cfselect?

[siriiven]

I have 2 cfselects that bind to a couple of queries. In the first query I select a location code and a location name. In the actual cfselect I want to display #Location# - #LocName# in that format but it is only showing whatever is in the second slot of my array. Here is my .cfc code:

<cffunction name="getLocation" access="remote" returnType="array">
<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>

<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT Location, LocName
FROM Locations
ORDER BY Location ASC
</cfquery>

<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.Location>
<cfset result [2]=data.LocName>
</cfloop>

<!--- And return it --->
<cfreturn result>
</cffunction>


<cffunction name="getController" access="remote" returnType="array">
<cfargument name="controller" required="no" type="any">

<!--- Define variables --->
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>

<!--- Get data --->
<cfquery name="data" datasource="#THIS.dsn#">
SELECT PartsInventory.Location, PartsInventory.Controller, PartsInventory.SerialNbr, PartsInventory.Model, Locations.CX
FROM PartsInventory LEFT JOIN Locations ON PartsInventory.Location = Locations.Location
WHERE PartsInventory.Location = ('#ARGUMENTS.controller#')
Order By PartsInventory.Controller ASC




</cfquery>

<!--- Convert results to array --->
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result [1]=data.SerialNbr>
<cfset result [2]=data.Controller>

</cfloop>

<!--- And return it --->
<cfreturn result>
</cffunction>


Here is my select boxes:

<cfselect name="bldglocation" bind="cfc:controllerselect.getLocation()" bindonload="true" />

<cfselect enabled="No" name="getController" multiple="no" style="width: 21em;"
bind="cfc:controllerselect.getController({bldglocation})" bindonload="false" />

They are getting the right information I just do not know how to make a form object display certain items from my query.

Any ideas are appreciated.
Thanks.

[Guest]

Hi,

Concept: Pass your query "data" into a temp query. In the first field, drop in your #Location# - #LocName#. That becomes your select "display" field. Then create a second field, drop in your #LocName#. This becomes your "value" field. The code below should be pretty easy to follow. (I use the code to drop in a blank row at the top of my cfselect as this typically bombs when you "bind" on cfselect.)

<CFFUNCTION NAME="getCategory" ACCESS="remote" returnType="query">

Review QueryNew and QuerySetCell to help you out.

cfwild

<CFSET var data="">

<CFQUERY NAME="data" DATASOURCE="#DSN#" >
SELECT DISTINCT #TP#_#SESSION.tableName#.Category
FROM #TP#_#SESSION.tableName#;
</CFQUERY>

<!--- Add Blank Row (For No Filter) & Sort for CFSelect --->
<CFSET totalRows = (data.recordcount+1)>
<CFSET getCategory = QueryNew("Category", "VarChar")>
<CFSET newRow = QueryAddRow(getCategory, totalRows)>
<CFSET temp = QuerySetCell(getCategory, "Category", "", 1)>
<CFSET counter = 2>
<CFLOOP QUERY="data">
<CFSET temp = QuerySetCell(getCategory, "Category", Category, counter)>
<CFSET counter = counter+1>
</CFLOOP>

<CFRETURN getCategory>

</CFFUNCTION>