Question
Checkboxes for multiple line items
On my form, I query a table based on ref no and bring back
muliple line items for each ref no. What I need to do is display a
checkbox next to each line item. If they check the box, they can
edit the line, otherwise nothing should happen. Some fields are
disply only and cannot be edited. I also need to write the updated
transactions to a history file and that is wher I am having the
problem.
Here is my code for the form :
<cfoutput query="qryGet_ref_Line_Items">
<tr>
<td align="center" width="2%"><cfinput type="checkbox" name="edit_#ref_line_item#" value="Yes"></td>
<td align="center" width="5%">#ref_line_item#<input type="hidden" name="ref_line_item_#ref_line_item#" value="#qryGet_ref_Line_Items.ref_line_item#"></td>
<td align="center" width="5%"><cfinput type="text" name="quantity_#ref_line_item#" value="#qryGet_ref_Line_Items.quantity#"></td>
<cfif qryGet_ref_Line_Items.ref_action is "Receive">
<td align="center" width="7%"><cfinput type="text" name="po_number_#ref_line_item#" value="#qryGet_ref_Line_Items.po_number#"></td>
<td align="center" width="5%"><cfinput type="text" name="item_#ref_line_item#" value="#qryGet_ref_Line_Items.item#"></td>
<td align="center" width="7%"><cfinput type="text" name="part_number_#ref_line_item#" value="#qryGet_ref_Line_Items.part_number#"></td>
<td align="center" width="7%">#qryGet_ref_Line_Items.tracking_number#</td>
<td align="center" width="10%">#qryGet_ref_Line_Items.instructions#</td>
</cfif>
</tr>
here is my action page, where I update the table, and attemp to update the history file, but all records are in the history file, not just the edited ones :
<cfloop index="ref_line_item" list="#form.list_of_line_items#" delimiters=",">
<cfquery name="qryUpdate_ref_Line_Items" datasource="recDisc">
update test_table_ref_Line_Items
set quantity = '#Evaluate("form.quantity_#ref_line_item#")#',
<cfif "#Evaluate("form.ref_action_#ref_line_item#")#" is "Receive">
po_number = '#Evaluate("form.po_number_#ref_line_item#")#',
item = '#Evaluate("form.item_#ref_line_item#")#',
part_number = '#Evaluate("form.part_number_#ref_line_item#")#'
<cfelseif "#Evaluate("form.ref_action_#ref_line_item#")#" is "RTV">
tracking_number = '#Evaluate("form.tracking_number_#ref_line_item#")#'
<cfelseif "#Evaluate("form.ref_action_#ref_line_item#")#" is "Other">
instructions = '#Evaluate("form.instructions_#ref_line_item#")#'
</cfif>
where ref_number = '#form.ref_number#'
and ref_line_item = '#ref_line_item#'
</cfquery>
<!---HISTORY FILE UPDATE- PROBLEM ---->
<cfquery name="qryGenerate_Unique_Number" datasource="recDisc">
SELECT max(activity_transaction_id) as act_txn_id_0,
max(ref_number) as ref_number_0
FROM test_table_Activity_Log
</cfquery>
<!--- Disposition activity is id 4 --->
<cfset activity_id = "4">
<!--- Add 1 to the current fileID (max), to get the next number --->
<cfset #act_txn_id# = #qryGenerate_Unique_Number.act_txn_id_0# + 1>
<!--- Insert the disposition data into the activity log --->
<cfquery name="qryInsert_Activity_Log" datasource="recDisc">
insert into test_table_Activity_Log
(activity_transaction_id,
activity_id,
user_employee_number,
activity_date,
comments,
ref_number,
line_item)
values
('#act_txn_id#',
'#activity_id#',
'#cgi.remote_user#',
#createODBCDateTime(Now())#,
'None',
'#form.ref_number#',
'#ref_line_item#')
</cfquery>
</cfif>
</cfloop>
Here is my code for the form :
<cfoutput query="qryGet_ref_Line_Items">
<tr>
<td align="center" width="2%"><cfinput type="checkbox" name="edit_#ref_line_item#" value="Yes"></td>
<td align="center" width="5%">#ref_line_item#<input type="hidden" name="ref_line_item_#ref_line_item#" value="#qryGet_ref_Line_Items.ref_line_item#"></td>
<td align="center" width="5%"><cfinput type="text" name="quantity_#ref_line_item#" value="#qryGet_ref_Line_Items.quantity#"></td>
<cfif qryGet_ref_Line_Items.ref_action is "Receive">
<td align="center" width="7%"><cfinput type="text" name="po_number_#ref_line_item#" value="#qryGet_ref_Line_Items.po_number#"></td>
<td align="center" width="5%"><cfinput type="text" name="item_#ref_line_item#" value="#qryGet_ref_Line_Items.item#"></td>
<td align="center" width="7%"><cfinput type="text" name="part_number_#ref_line_item#" value="#qryGet_ref_Line_Items.part_number#"></td>
<td align="center" width="7%">#qryGet_ref_Line_Items.tracking_number#</td>
<td align="center" width="10%">#qryGet_ref_Line_Items.instructions#</td>
</cfif>
</tr>
here is my action page, where I update the table, and attemp to update the history file, but all records are in the history file, not just the edited ones :
<cfloop index="ref_line_item" list="#form.list_of_line_items#" delimiters=",">
<cfquery name="qryUpdate_ref_Line_Items" datasource="recDisc">
update test_table_ref_Line_Items
set quantity = '#Evaluate("form.quantity_#ref_line_item#")#',
<cfif "#Evaluate("form.ref_action_#ref_line_item#")#" is "Receive">
po_number = '#Evaluate("form.po_number_#ref_line_item#")#',
item = '#Evaluate("form.item_#ref_line_item#")#',
part_number = '#Evaluate("form.part_number_#ref_line_item#")#'
<cfelseif "#Evaluate("form.ref_action_#ref_line_item#")#" is "RTV">
tracking_number = '#Evaluate("form.tracking_number_#ref_line_item#")#'
<cfelseif "#Evaluate("form.ref_action_#ref_line_item#")#" is "Other">
instructions = '#Evaluate("form.instructions_#ref_line_item#")#'
</cfif>
where ref_number = '#form.ref_number#'
and ref_line_item = '#ref_line_item#'
</cfquery>
<!---HISTORY FILE UPDATE- PROBLEM ---->
<cfquery name="qryGenerate_Unique_Number" datasource="recDisc">
SELECT max(activity_transaction_id) as act_txn_id_0,
max(ref_number) as ref_number_0
FROM test_table_Activity_Log
</cfquery>
<!--- Disposition activity is id 4 --->
<cfset activity_id = "4">
<!--- Add 1 to the current fileID (max), to get the next number --->
<cfset #act_txn_id# = #qryGenerate_Unique_Number.act_txn_id_0# + 1>
<!--- Insert the disposition data into the activity log --->
<cfquery name="qryInsert_Activity_Log" datasource="recDisc">
insert into test_table_Activity_Log
(activity_transaction_id,
activity_id,
user_employee_number,
activity_date,
comments,
ref_number,
line_item)
values
('#act_txn_id#',
'#activity_id#',
'#cgi.remote_user#',
#createODBCDateTime(Now())#,
'None',
'#form.ref_number#',
'#ref_line_item#')
</cfquery>
</cfif>
</cfloop>
