Insert list results into table

[Neil5EA7]

Hi all,

New to Coldfusion so posting for help!

I have created a form for users to assign jobs to technicians. My form shows the outstanding jobs and below looped output of which tech in each area they can assign the jobs to. The idea is that they assign all the outstanding jobs to the selected tech.

The form returns the output as two lists, such as:

form.opArea: Area 4,Area 5
form.tech: ME1,ME2

How can I now update my oustanding jobs table, where the area is matched to the form.opArea and the form.tech name is inserted into the Technician column?

Cheers!

[BKBK]

This seems to me to be a software-design problem, rather than a ColdFusion problem. As form.opArea and form.tech are lists, that suggests that your form has 2 or more fields named opArea and 2 or more fields named tech. Is that intentional? Does it matter whether ME1 is assigned to Area 4 or Area 5?  I ask because the usual practice is to have form fields that have distinct names.

[Neil5EA7]

Hi,

There's only two form fields: Field one shows the area number, and the second is a select list so that they can choose the technician to do the jobs for that area. I loop over the area so that each outstanding area is covered:

I have added the area to the assignee name as the value now which makes it alot easier to grab the output:

Area 4_CTULLY,Area 5_SCROCKER

So, yes, a design problem!

[BKBK]

Neil5EA7: I have added the area to the assignee name as the value now which makes it alot easier to grab the output:

Area 4_CTULLY,Area 5_SCROCKER

I wouldn't do that. That is strong coupling. It will prevent you scaling your design in future.

You could use your previous design. But with the understanding that there is a one-to-one relationship between the list representing area and the list representing the selected technician. That is

Area 4 <=> Tully, C.

Area 5 <=> Crocker, S.

You could then do something like:

<cfset listIndex = 0>
<cfloop list="#form.tech#" item="selectedTech">
	<cfset listIndex = listIndex + 1>
        <!--- The area whose list-index is same as that of selected Tech --->
	<cfset area = listGetAt(form.area,listIndex)>

	<cfquery datasource="myDSN">
	update jobs
	set technician = <cfqueryparam value="#selectedTech#" cfsqltype="cf_sql_varchar">
	where area = <cfqueryparam value="#area#" cfsqltype="cf_sql_varchar">
	</cfquery>

</cfloop>