Insert list results into table

New Here ,
Dec 29, 2020 Dec 29, 2020

Copy link to clipboard

Copied

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!

TOPICS
Advanced techniques, Database access

Views

92

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 29, 2020 Dec 29, 2020

Copy link to clipboard

Copied

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.

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Dec 29, 2020 Dec 29, 2020

Copy link to clipboard

Copied

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:

 

Capture.JPG

 

 

 

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Dec 29, 2020 Dec 29, 2020

Copy link to clipboard

Copied

LATEST

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines