Drop Down Question

[johnhig]

Hi,

I have the following drop down menu that displays information from a look up table in my database.

<CFQUERY NAME="GetUnits" DATASOURCE="intranet" DBTYPE="ODBC">
SELECT Item_No, Unit_Desc, Serial_No
FROM Inv_Roots2
ORDER BY Item_No
</CFQUERY>
<A NAME="Unit">
<SELECT NAME="Unit" TABINDEX="17" CLASS="SelectBox">
<CFLOOP QUERY="GetUnits">
<CFIF ParameterExists(URL.SelectedOption) AND URL.SelectedOption IS "#Item_No#" OR ParameterExists(Form.Unit) AND Form.Unit IS "#Item_No#" OR ThisUnit IS "#Item_No#">
<CFSET SELECTED=" SELECTED">
<CFELSE>
<CFSET SELECTED="">
</CFIF>
<OPTION VALUE="#Item_No#"#SELECTED#><CF_CAPITALIZE UNSHOUT="Yes" TITLECASE="Yes" HYPHENATEDWORDS="Yes" ALLCAPACRONYMS="Yes">#Item_No# - #Unit_Desc#</CF_CAPITALIZE>
</CFLOOP>
</SELECT>


Now, I only take the item_no value and insert that into my table currently.

What I would like to know if I wanted to make a modification to also insert the unit_desc and the serial_no, can I tweek my current code or do I need to implement something like the cftwoseletedrelated function?

Thanks John

 

[johnhig]

Ok. I am not sure if this is the most logical way to do this, but I just combined the values like the following

#item_no# - #Unit_desc# - #Serial_no#

It works fine. I am not doing anything other than just displaying the info for reference so I can believe am to incorrect.

Thanks John

 

[cfStarlight]

Is #item_no# - #Unit_desc# - #Serial_no# the <OPTION> value or text?

Small comment about the code. The correct syntax is ParameterExists("URL.SelectedOption"). You must use quotes around the variable name to prevent an error if the variable doesn't exist. But in any case ParameterExists is deprecated. Use IsDefined instead.

    <cfif IsDefined("URL.SelectedOption") ...>

 

[GUJUm0deL]

The problem I see with doing #item_no# - #Unit_desc# - #Serial_no# is that if you ever need to split the three options, or order them in a particular way it's gonna be a nightmare.

From the code you posted, it looks like #Item_No# - #Unit_Desc# is a textual part of the <option> and not the value.

I would much rather do this:
1. Pass the field as #item_no# - #Unit_desc# - #Serial_no#
2. Before insterting/updating the dB, split the three fields into three diff columns and insert/update into the dB as three diff values. This way you have more flexiblity to <cfoutput> using ORDER BY, GROUP BY, etc.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

From the code you posted, it looks like #Item_No# - #Unit_Desc# is a textual part of the <option> and not the value.

GUJUm0deL,

I think you may have meant if #item_no# - #Unit_desc# - #Serial_no# is the <option> value. It doesn't matter if its the <option> "text" because the text isn't submitted to the action page.

If its the <option> value, then yes I agree it should be split into separate values before being inserted into the db. Storing delimited data in a column is almost always a mistake

They can use list functions to grab each value. The general idea is something like this psuedo-code.

Action Page

<cfif ListLen(form.Unit, "-") gte 3>
  <cfset Item_no = listGetAt(form.Unit, 1, "-")>
  <cfset Unit_desc = listGetAt(form.Unit, 2, "-")>
  <cfset Serial_no = listGetAt(form.Unit, 3, "-")>

  <cfquery ...>
    INSERT INTO YourTable (Item_No, Unit_desc, Serial_No)
    VALUES 
    ( 
    <cfqueryparam value="#Item_no#" cfsqltype="WhateverTheDataTypeIs" >,
    <cfqueryparam value="#Unit_desc#" cfsqltype="WhateverTheDataTypeIs" >,
    <cfqueryparam value="#Serial_No#" cfsqltype="WhateverTheDataTypeIs" >
    )
  </cfquery>
</cfif>

 

[GUJUm0deL]

cfStarlight, That is what I meant.

I wanted to ask johnhig (based on his code above) that #Item_No# - #Unit_Desc# appears to be used as the <option> "text" and not as the <option> "value". But his dilemma seems to be storing all three variables in the dB.



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

GUJUm0deL,

Okay. Sounds like we're thinking the same thing then

 

[imstillatwork]

absolutely DO NOT use parameterExists() unless you are using a PRE MX5 version.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!