asked on
Drop-down value on update entering list item not value
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_Mast
SET OfferType=<cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">, ..........
WHERE ReferenceNumber=#ARGUMENTS
</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_Off
ORDER BY OfferTypeID
</cfquery>
<cfreturn offertypes>
</cffunction>
Form page
<cfinvoke component="myCFC"
method="get"
ReferenceNumber="#URL.CFGR
returnvariable="record">
<cfset OfferType=record.OfferType
<cfinvoke component="myCFC"
method="GetOfferTypes"
returnvariable="offertypes
<cfform action="process.cfm">
<SELECT NAME="OfferType">
<cfoutput><OPTION>#VARIABL
<CFOUTPUT QUERY="OfferTypes">
<OPTION VALUE="#OfferTypeID#">#Off
</CFOUTPUT>
</SELECT>
Process page
<cfinvoke component="myCFC"
method="Update">
<cfinvokeargument name="OfferType"
value="#FORM.OfferType#">
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_Mast
SET OfferType=<cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">, ..........
WHERE ReferenceNumber=#ARGUMENTS
</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_Off
ORDER BY OfferTypeID
</cfquery>
<cfreturn offertypes>
</cffunction>
Form page
<cfinvoke component="myCFC"
method="get"
ReferenceNumber="#URL.CFGR
returnvariable="record">
<cfset OfferType=record.OfferType
<cfinvoke component="myCFC"
method="GetOfferTypes"
returnvariable="offertypes
<cfform action="process.cfm">
<SELECT NAME="OfferType">
<cfoutput><OPTION>#VARIABL
<CFOUTPUT QUERY="OfferTypes">
<OPTION VALUE="#OfferTypeID#">#Off
</CFOUTPUT>
</SELECT>
Process page
<cfinvoke component="myCFC"
method="Update">
<cfinvokeargument name="OfferType"
value="#FORM.OfferType#">
ColdFusion Language
Last Comment
_agx_
<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
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.prevOf
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>
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.prevOf
ASKER
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...
Also, FYI, OfferTypeID is the numeric value, OfferType is the "word" associated with it.
Thanks again...
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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"
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"
ASKER
_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.
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>
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"
....
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>
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"
....
ASKER
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.
ASKER
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.Offer
<OPTION VALUE="0">--- select an item ---</OPTION>
</cfselect>
<cfselect queryPosition="below"
name="OfferType"
query="OfferTypes"
value="OfferTypeID"
display="OfferType"
selected="#VARIABLES.Offer
<OPTION VALUE="0">--- select an item ---</OPTION>
</cfselect>
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.
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.
ASKER
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.Refe
FROM dbo.Tbl_Record_Master LEFT OUTER JOIN
dbo.Rtbl_Record_ServiceTyp
WHERE dbo.Tbl_Record_Master.Refe
</cfquery>
<cfreturn record>
</cffunction>
<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.Refe
FROM dbo.Tbl_Record_Master LEFT OUTER JOIN
dbo.Rtbl_Record_ServiceTyp
WHERE dbo.Tbl_Record_Master.Refe
</cfquery>
<cfreturn record>
</cffunction>
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.Offer
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.
Dump the value of #VARIABLES.OfferType# before generating the select list.
VARIABLES.OfferType value is: <cfoutput>#VARIABLES.Offer
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.
ASKER
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>#OfferTy
<CFOUTPUT QUERY="Offertypes">
<OPTION VALUE="#OfferTypeID#">#Off
</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.
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>#OfferTy
<CFOUTPUT QUERY="Offertypes">
<OPTION VALUE="#OfferTypeID#">#Off
</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.
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.
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.
ASKER
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.
ASKER
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.
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.
ASKER
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.
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"
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"
ASKER
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".
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".
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 :)
Kewl, reports are always fun :)
<cfoutput>
<OPTION VALUE="#OfferID#">#VARIABL
</cfoutput>