Solved

Drop-down value on update entering list item not value

Posted on 2012-03-19
21
389 Views
Last Modified: 2012-03-20
I have a drop-down that populates with some items/values from a reference table. When a choice is selected the value is written to the master table; this works fine.

When the record is edited the drop down is populated with the value that was previously selected (this is what I want to happen) but if the drop-down doesn't change it passes along the item name instead of what i want - the (numeric) value.

Could someone help with this? Would I have to change my update statement somehow so it displays the item name but still captures the value? Below is an example of what I currently have on my update (the relevant code).

CFC

 <cffunction name="update"
             returntype="boolean"
             hint="Update a record">
<cfargument name="ReferenceNumber"
              type="numeric"
              required="yes"
              hint="Reference Number">
<cfargument name="OfferType"
              type="string"
              required="no"
              hint="Offer Type">

<cfquery datasource="#datasource#">
  UPDATE [DB].[dbo].Tbl_Record_Master
  SET OfferType=<cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">, ..........

WHERE ReferenceNumber=#ARGUMENTS.ReferenceNumber#
  </cfquery>
  <cfreturn true>

 </cffunction>

<cffunction name="GetOfferTypes"
             returntype="query"
             hint="Get Offer Types">

  <cfquery datasource="#Datasource#"
           name="offertypes">
  SELECT OfferTypeID, OfferType
  FROM [DB].[dbo].Rtbl_Record_OfferType
  ORDER BY OfferTypeID
  </cfquery>
  <cfreturn offertypes>

 </cffunction>

Form page

<cfinvoke component="myCFC"
           method="get"
           ReferenceNumber="#URL.CFGRIDKEY#"
           returnvariable="record">

 <cfset OfferType=record.OfferType>

<cfinvoke component="myCFC"
           method="GetOfferTypes"
           returnvariable="offertypes">

<cfform action="process.cfm">
     <SELECT NAME="OfferType">
       <cfoutput><OPTION>#VARIABLES.OfferType#</OPTION></cfoutput>
            <CFOUTPUT QUERY="OfferTypes">
                  <OPTION VALUE="#OfferTypeID#">#OfferType#</OPTION>
            </CFOUTPUT>
      </SELECT>            


Process page

<cfinvoke component="myCFC"
          method="Update">

<cfinvokeargument name="OfferType"
                   value="#FORM.OfferType#">
0
Comment
Question by:earwig75
  • 11
  • 10
21 Comments
 

Author Comment

by:earwig75
ID: 37738714
I should add, I did try it like the example below, but the value isn't defined anywhere so it won't work and I'm not sure how to define something that hasn't been written yet. Hopefully I am being clear.

<cfoutput>
     <OPTION VALUE="#OfferID#">#VARIABLES.OfferType#</OPTION>
     </cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37739390
<cfset OfferType=record.OfferType>


So it was a problem after all ;-) The variable and form field names are confusing. Does  #OfferType# contain a numeric ID or the offer type name?  If possible, use "OfferTypeID" to indicate the numeric id is what's being passed.

Normally you pass the numeric ID to an edit page. Then use that number to automatically preselect a list item. For example if the previously selected ID is stored in a variable named #variables.prevOfferTypeID#

<SELECT NAME="OfferType">
         <CFOUTPUT QUERY="OfferTypes">
              <OPTION VALUE="#OfferTypeID#"  <CFIF OfferTypeID EQ variables.prevOfferTypeID>selected</cfif>>#OfferType#</OPTION>
        </CFOUTPUT>
</SELECT>            

Open in new window


Or if you're using a cfform and cfselect, use the "selected" attribute. Something along these lines

         <cfselect query="OfferTypes"
                       display="OfferType
                       value="OfferTypeID"
                      selected="variables.prevOfferTypeID" />
0
 

Author Comment

by:earwig75
ID: 37739599
Yes there was an issue! I'm glad you remembered. It works fine with cfselect... but then I cannot make the first choice blank/null - that is my issue. If there is a way to make the first choice blank I would just use that option. Is it an "ok" practice to create 1 null option in the table and make it display that one first?

Also, FYI, OfferTypeID is the numeric value, OfferType is the "word" associated with it.

Thanks again...
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 37739651
Yeah, it's common to create a null/blank 1st option. You use it conjunction w/the preselection logic like this. So it either selects a specific ID or the blank option if it's not found.

<SELECT NAME="OfferType">
          <OPTION VALUE="0">--- select an item ---</OPTION>
         <CFOUTPUT QUERY="OfferTypes">
              <OPTION VALUE="#OfferTypeID#"  <CFIF OfferTypeID EQ variables.prevOfferTypeID>selected</cfif>>#OfferType#</OPTION>
        </CFOUTPUT>
</SELECT>

..or with cfselect

        <cfselect queryPosition="below" query="OfferType" .... other attributes>
                  <OPTION VALUE="0">--- select an item ---</OPTION>
         </cfselect>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37739675
Also, FYI, OfferTypeID is the numeric value, OfferType is the "word" associated with it.

Ok, then it's best to keep column and variable names and consistent. Use "OfferTypeID" when the value is numeric and "OfferType" when it's the description. Since your select list submits the numeric ID it's better to call it "OfferTypeID"
0
 

Author Comment

by:earwig75
ID: 37740431
_agx_ I thought I did call it OfferTypeID for the numeric and just OfferType for the word... did you find somewhere that I didn't have it correctly?  Also, if your 1st example above, how would I save the "prevOfferTypeID" and then define it or display it when they come back to view the record... just like any other field? I don't mean to sound dumb; I'm very new to ColdFusion. Also with the first example, when they visit the page to edit a record... wouldn't that force them to make a choice for the drop down even if they didn't want to change it? Thanks again.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37740739
Also, if your 1st example above, how would I save the "prevOfferTypeID" and then define it or display it when they come back to view the record... just like any other field?


Probably, but it depends on your form. Can you post a little more of the form code?I'm guessing you're using a single form for both "Adds" and "Edits". Also that the "myCFC.get" function returns all of the fields for the record being edited, including the OfferTypeID - correct?

Also with the first example, when they visit the page to edit a record... wouldn't that force them to make a choice for the drop down even if they didn't want to change it?

No because the CFIF automatically pre-selects the prior ID.

     <CFIF OfferTypeID EQ variables.prevOfferTypeID>selected</cfif>

I thought I did call it OfferTypeID for the numeric and just OfferType for the word.

In the db table yes, but your <select> list and function arguments are called "OfferType". Since they all represent the numeric ID, it's better to call them  "OfferTypeID" instead.  Keep in mind it'll work either way.. so this is about "good practices" not valid syntax.

Using the same naming convention throughout the code makes it more intuitive and easier to maintain. Six months down the road will you still remember that "OfferType" refers to the number in some places ... but the "word" in others? If you always use "OfferTypeID" to represent the number you won't have to worry about that. It's immediately obvious from just looking at the code. Or if you ever had to do a global search or replace you wouldn't have to worry about mixing up the "word" with the numeric ID.

<!--- form field name --->
<SELECT NAME="OfferTypeID" >
<!--- when invoking the function ...--->
<cfinvoke component="myCFC" method="Update">
       <cfinvokeargument name="OfferTypeID"  value="#FORM.OfferTypeID#">
<!--- for the function argument name, etc... ...--->
<cffunction ...>
      <cfargument name="OfferTypeID"
....
0
 

Author Comment

by:earwig75
ID: 37741695
When I am only using "OfferType" and not "OfferTypeID" I am displaying the word associated with the ID. My db field is OfferType and in it i store the OfferTypeId. In my code i want the user to see the word not the numerical value... that's what my whole issue is. Im trying to normalize everything on the db side. I'll try this as soon as i get in today.
0
 

Author Comment

by:earwig75
ID: 37742207
Ok, below is what I am using now but it still isn't working properly. It is not pre-populating the drop-down with the current choice; it is defaulting to the first choice. Do you see what I could be doing wrong?

  <cfselect queryPosition="below"
                   name="OfferType"
             query="OfferTypes"
             value="OfferTypeID"
             display="OfferType"
             selected="#VARIABLES.OfferType#">
 <OPTION VALUE="0">--- select an item ---</OPTION>
   </cfselect>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37742408
I am displaying the word associated with the ID.

Yeah, I understand the goal. Your variable and column names are just a little off from how it's normally done that's all. Let's ignore it for the moment ...

      Can you post a little more of the form code?

It's a simple problem, but we can't see enough of your code to tell you how to fix it. Can you post the whole edit form and the code for the "myCFC.get" function.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:earwig75
ID: 37742565
Unfortunatly I can't post the entire form because so many parts relate to work. None of the other form fields relate to this field though... I am using the same form to edit/add and just calling a different function for each. Below is an example of my "get" code... I am hoping this is enough. Thanks again; I really do appreciate it:

<cffunction name="get"
             returntype="query"
             hint="Get record details">
  <cfargument name="ReferenceNumber"
              type="numeric"
              required="yes"
              hint="Reference Number">

  <cfquery datasource="#datasource#"
           name="record">


SELECT     dbo.Tbl_Record_Master.ReferenceNumber, dbo.Rtbl_Record_ServiceType.ServiceType, dbo.Tbl_Record_Master.Otherfields....,

FROM         dbo.Tbl_Record_Master LEFT OUTER JOIN
                      dbo.Rtbl_Record_ServiceType ON dbo.Tbl_Record_Master.ServiceType = dbo.Rtbl_Record_ServiceType.ServiceTypeID
  WHERE dbo.Tbl_Record_Master.ReferenceNumber=#ARGUMENTS.ReferenceNumber#
  </cfquery>
  <cfreturn record>
 
 </cffunction>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37742853
So you're using the "get" function to retrieve the previously entered "OfferTypeID" - and a bunch of other fields, correct?

Dump the value of #VARIABLES.OfferType# before generating the select list.

         VARIABLES.OfferType value is:  <cfoutput>#VARIABLES.OfferType#</cfoutput>::end::<br>
     
What's the value? It should be one of the numeric id's in your OfferType table. If not, that explains why nothing's being selected in the list.
0
 

Author Comment

by:earwig75
ID: 37742929
Yes it comes back with a value when I dump it... the WORD/NAME ... that is the problem. If the person updates a field the drop down sends the word to my master table and not a value associated with it.

It will auto-populate with the word if i do it like this... but i need it to send the numeric value if they don't change it.

      <SELECT NAME="OfferType">
      <cfoutput><OPTION>#OfferType#</OPTION></cfoutput>
      <CFOUTPUT QUERY="Offertypes">
      <OPTION VALUE="#OfferTypeID#">#OfferType#</OPTION>
      </CFOUTPUT>
      </SELECT>

So you're using the "get" function to retrieve the previously entered "OfferTypeID" - and a bunch of other fields, correct?

Yes... it populates the form with previously entered values if the user wants to edit the record.

Thanks again.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37743075
Yes it comes back with a value when I dump it... the WORD/NAME ... that is the problem.

Finally we're making progress ;-)  You said "My db field is OfferType and in it i store the OfferTypeId.".  So ether your "get" query is returning the wrong column

        ie the WORD/NAME instead of the ID

... OR

The insert/update code inserted the wrong values into that column.  Which is it? I don't see "OfferType" anywhere in your "get" function SQL. So I can't tell.
0
 

Author Comment

by:earwig75
ID: 37743110
I want it to return the word because that is what I want to display to the user, since the number will be meaningless to them. The drop-down populates with new values fine. Are you thinking that my query needs to change so that it will convert the word into a numeric value on update? I thought I created the join correctly... if not then I am lost and will give up on "normalizing" this.
0
 

Author Comment

by:earwig75
ID: 37743162
Hey... I got it working. What I did was put a cfif on the "process" page that checkes to see if the value is 0... it if is I don't touch that field... if it isn't 0 I update the field with the new value. I will just dislay the current value as text and then if they want to change it, they can choose a new value next to it. Thank you for all of your help. I used a couple of your ideas and put them together.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37743388
Lol, I think we're on 2 different wavelengths.  But not being able to see your actual queries, or dumps of the actual values, usually makes things a lot more drawn out than they should be.  Especially with the confusion of using the same name to represent 2 different things.  Seeing a dump of #variables.OfferType# to start with would've shown the problem immediately: it's storing text not an id.  Anyway.. glad you got something working.
0
 

Author Comment

by:earwig75
ID: 37744100
It is storing the ID (numeric) in the master table... but because of the way the query is written it outputs the word associated with that numeric ID... (I believe that is what is going on). Does that not sound right? The only stuff I left out are other fields that have nothing to do with this one. I included the real query and just left out the fields that none of this touches.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37744344
The only stuff I left out are other fields that have nothing to do with this one.


That's fine and appreciated. But the "get" query you posted doesn't even include a column named "OfferType" or "OfferTypeID" in the SELECT list that I can see.  Unless maybe you're calling it something different and didn't mention it? So there was no way to tell what value the query was returning or that the <select> list was using.  A simple dump of the variable would've let us cut to the chase MUCH quicker ;-)

Btw, if ever data is proprietary, just replace it with a placeholder that accurately represents the value like "Widget A" or "Some Company"  for a string or 1, 52, 18, ... for the numeric ID, etc...

but because of the way the query is written it outputs the word associated with that numeric ID... (I believe that is what is going on).

For this specific form you don't need the WORD at all, because the <select> list already contains the user friendly WORDs/titles.  All you need is the previous ID and the code will automatically highlight it.

ie  if you pass in PreviousOfferTypeID = 3  the code will use the ID to automatically highlight the associated WORD

ie        <select ...>
                <option value="0">select something </option>
                <option value="1">Widget A</option>
                <option value="2">Widget B</option>
                <option value="3">Widget C</option>  <=== the user will see "Widget C"
0
 

Author Comment

by:earwig75
ID: 37744597
I needed the word because I display it in a grid as well... but since I don't need it on this page (you were right again) I just let it write the number and let CFSELECT display the name. If they don't choose anything I just write a "0".

This the first cold fusion application I am building from "scratch" and appreciate your help. Now that all of my form stuff etc is done... it's time to start building reports. Look out for a bunch of dumb questions from me in the coming days :)

EDIT: OH... I made a mistake in my "get" as well... I showed you "ServiceType" but those should have all been "OfferType".
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37744950
Well if you're using the query for more than one thing, you can always return both the ID and the word.  Then just use the ID value instead of the word on the edit page.

Kewl, reports are always fun :)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now