insert/update two fields based on one selection

Hello all,

I apologize if this belongs in a different section (e.g. SQL). This is kind of an odd request and I'm not sure it's even do-able so I thought I'd post it here.

I have a table that has one field that contains some long names and another field that has the abbreviation/acronym. I have a form that uses the abbreviation field to populate a dropdown selection box. The form submits to an action page which inserts or updates a main table (based on another condition elsewhere).

What I would really like is to have two fields from a record inserted into the main table based on the users selection of one field. In other words - when the user makes a selection of an abbreviation/acronym in the form both the abbreviation/acronym AND the full name get inserted into the main table (into two different fields of course).

How hard is that to do? This is what my code looks like now on the submit form:
-------------------------------------------------------
<CFIF #form.submittype# IS "update">
<CFQUERY name="updateProject" datasource="PLOG">
UPDATE data_admin.Main_log SET Date_Assigned=
<cfif IsDefined("FORM.Date_Assigned") AND #FORM.Date_Assigned# NEQ "">
'#FORM.Date_Assigned#'
<cfelse>
NULL
</cfif>
, Refuge_Literal=
<cfif IsDefined("FORM.Refuge_Literal") AND #FORM.Refuge_Literal# NEQ "">
'#FORM.Refuge_Literal#'
<cfelse>
NULL
</cfif>
, Requestor=
<cfif IsDefined("FORM.Requestor") AND #FORM.Requestor# NEQ "">
'#FORM.Requestor#'
<cfelse>
NULL
</cfif>
, Requestor_Office=
<cfif IsDefined("FORM.Requestor_Office") AND #FORM.Requestor_Office# NEQ "">
'#FORM.Requestor_Office#'
<cfelse>
NULL
</cfif>
WHERE Job_Number = '#FORM.Job_Number#'
</CFQUERY>
</CFIF>
-------------------------------------------------
Notice they are all form variables. So how would I insert the abbreviation/acronym chosen by the user on the form and it's corresponding full name?

Any help would be much appreciated!!

Thanks,
Melissa

Melissa,

Let me clarify what you are asking. You have two populated dropdown boxes and when you select one of them and update the main table, you want both dropdown values added to your main table, but you don't want to have the user have to select both dropdown boxes.

If this is the case, there is a way you could do this, both mostly using JavaScript (so your posting is in the wrong area, but will let that slide

One way to do it is to use JavaScript to select dropdown 'A' when dropdown 'B' is selected.

Each dropdown should have its opposing value as the passed value (your acronym dropdown should pass your full name). Now program the on change events so that when you select dropdown 'A' it will compare its pass value with the display values available in 'B' and select it (then submit the form to your process page which will have your cfquery)

You values will be backwards at this time (your acronym form.object will have the full name value) so you may want to use some hidden objects to be set before the submit so your have better naming.

So now you will probably be wanting some code huh? Well let me see.. Here is a little pseudo code that might help (it is WAY to early for me to produce anything good)

function compare(){
var DDA = document.main.object.selectedindex.value;
count = 0;
loop from count to len(DDB)
if (document.main.DDB.option[count].text == DDA)
document.main.DDB.selectedindex=count;
end loop
if (document.main.ddb.selectedindex != -1)
main.submit();
else
alert('no comparable value.')
}

Ebot is right Melissa...you'll have to do this with Javascript.

That's a bummer as I know not a lick of javascript and can't really incorporate it into this project.

And just to clarify, I was hoping just to have ONE drop down box of the abbreviation and when the user chooses something from it TWO pieces of data go into the table since they are just two fields from the same record in the table populating the dropdown box. E.g.

Col A Col B
abc apples
def dirt
ghi gorillas
jkl jam


Only Col A shows in the form. User chooses abc from the dropdown but abc and apples get inserted into the main table.

So this is only possible in JS huh? Wish I could do this project in Flash instead of CF!

Thanks all for your time and help!
melissa

Well you could make the values of your dropdown boxes store both values (CPU, Central processing Unit) and then once you have passed the value to the process page, you could then parse the two values apart. Just use a unique enough delimiter and you should be able to do it easy.

That's also true, if the values are always the same (ie each Name has the same abbreviation all the time). Then you could pass both in the form value, delimited by a pipe symbol or something similar.

That's mostly true but sometimes we have a name with no acronym (don't ask, its a mess) and some of the names are REALLY long so showing both in the drop down won't work.

My boss who is the sql guy (I can run basic queries but he's in charge of the complicated stuff) says he can figure out how to have a sql function run in the background after a record is inserted into the database to check which acronym was inserted and then insert the appropriate long name.

If this works I'll be surprised but you never know. Stranger things have worked out!