Adding checkboxes after display

[trojnfn]

I retrieve records from a database and display the records in a table. Each of
the records belongs to one user; for example, if I find six records, they
belong to one person. I want to add a checkbox (yes/no) at the end of each
record so that the user can indicate if he wishes to retain the record
(contains user info) - yes, or get rid of it - no. I put in <input
type=checkbox name="retain" value="yes"> yes <br>
<input type=checkbox name="retain" value="no">no at the end of the line
enclose <td></td>. The problem is the data comes out at yes, no, yes, no, yes,
no, if there are six records and yes and no are checked alternately. How can I
get it so that yes only is for the first record, no for the second, etc. ?
Based on the yes or no response, I want to do something else, but I cannot
because of the way the info is stored. Anybody have a easy way around this, or
perhaps a better way of doing it ?

[Dan Bracuk]

first, use radio buttons instead of checkboxes. How do you interpret both
boxes being selected.

second, change the name of the form fields for each row of data. Appending
the query currentrow is a good way to do this.

[trojnfn]

I have tried radio buttons, checkboxes, and even drop down menus, but it still
outputs data as yes, yes, yes, etc. I even tried
<cfloop list> and that seems to break it up individually, but then when I try
to update the database it only updates the first row and not each individaul
row. I tried to use currentrow to get each response to correspond to the
particalur row but my syntax is incorrect.

Can you provide me with a quick and dirty sample so that each yes/no response
corresponds to a row and can update the database so that each record will have
a yes or no response in the field ?

[Dan Bracuk]

quick and dirty is my specialty. correct syntax is optional.

Let's say the query is named q1.

Name your radio buttons as follows.
<cfform, etc
<cfoutput query="q1"

<cfinput name="decide#currentrow# type="radio" value="Y" checked>keep
<cfinput name="decide#currentrow# type="radio" value="N" checked>delete

Do the same thing for all your form fields.

When processing the results, do this
<cfloop collection="form" item="thisformfield">
this formfield will take on values such as decide1, decide2, etc
do all your logic here
</cfloop>

Originally posted by: trojnfn
I have tried radio buttons, checkboxes, and even drop down menus, but it still
outputs data as yes, yes, yes, etc. I even tried
<cfloop list> and that seems to break it up individually, but then when I try
to update the database it only updates the first row and not each individaul
row. I tried to use currentrow to get each response to correspond to the
particalur row but my syntax is incorrect.

Can you provide me with a quick and dirty sample so that each yes/no response
corresponds to a row and can update the database so that each record will have
a yes or no response in the field ?

[mxstu]

Hi trojnfn,

Just output your query and dynamically name each set of form fields by
appending the query row number as a suffix. Outside the loop, store the total
number of rows in a hidden form field. Then on your action page, loop from 1
to the form.numberOfRows# and process each set of values. Note - By default,
you want to preselect the "yes" option.







<!---- FORM PAGE ---->
<form method="post" action=".....">
<cfoutput query="yourQuery">
Record ID: #recordID#
<!--- set "yes" as default --->
<input type="radio" name="retain#CurrentRow#" value="yes" checked> Retain
<input type="radio" name="retain#CurrentRow#" value="no"> Delete
<input type="hidden" name="recordID#CurrentRow#" value="#recordID#"><hr>
</cfoutput>

<!--- store number of records in hidden field --->
<cfoutput>
<input type="hidden" name="numberOfRecords"
value="#yourQuery.recordCount#">
</cfoutput>
<input type="submit">
</form>

<!---- ACTION PAGE ---->
<cfloop from="1" to="#form.numberOfRecords#" index="row">
<!--- set "yes" as default to ensure value exists --->
<cfparam name="form.retain#row#" default="yes">
<cfset recordID = form["recordID"& row]>
<cfset retain = form["retain"& row]>
<!---- display results ---->
<cfoutput>
<cfif retain>
RETAIN #recordID#<br>
<cfelse>
DELETE #recordID#. Do something<br>
</cfif>
</cfoutput>
</cfloop>

[Kronin555]

Much simpler. Assuming myquery has an id value in it:

<cfoutput query="myquery">
Data... <input type="checkbox" name="delete" value="#id#">Delete<br>
</cfoutput>

then on your processing page, you just do:
<cfquery..>
delete from mytable where id in (#form.delete#)
</cfquery>

[mxstu]

You're right. A single option is better. It's late and I was making it much more complicated than it needed to be ;-)

[trojnfn]

Hello MXSTU, and KRONIN555

Thanks for both of your responses. I do not actually want to delete the record
if the delete button or checkbox is selected. I just want to update the
database field indicating whether the user wants to delete or keep that record.
Once I have the database updated, I can then do a select on all delete records
or a select on all keep records, and do something else, etc. Dan Bracuk
responded and gave me some helpful hints, but I am not smart enought to fully
understand them. My code to display the records and Dan's radio button
suggestions are below. Basically, at the right of each record, the user has to
indicate wheter to keep or delete that record. If there are six records
displayed, he has to do this six times, one for each record. After the form is
submitted, I want to update each record keep or delete (Y or N, etc) in the
field named Decide, this must be done six times, one for each record. Again, I
do not want to physically delete the record, I just want to indicate whether
the user wants to keep or delete it by updating the field. Do either of your
suggestions apply to what I am trying to do, and if it does, can you put in the
proper context for me in the code below. Again, I am fairly new to CF and this
is a little overwhelming for me. I have been trying to do this for almost two
weeks now and still cannot figure it out. I know it is a simple thing. Thanks
again for all of your help.



<cfform action="action_page.cfm" method="post">

<table border="2" bordercolor="blue" width="300%" cellpadding=1 cellspacing=0>

<tr>
<th>Baseline No</th>
<th>Manufacturer</th>
<th>Model Part No</th>
<th>Noun Description</th>
<th>Top Assembly ID</th>
<th>ID</th>
<th>Program Manager</th>
<th>Orig Input Date</th>
<th>Bldg</th>
<th>Room</th>
<th>ACQ Cost</th>
<th>SQ Feet</th>
<th>Document Number</th>
<th>Custodian Name</th>
<th>Legacy Source Coce</th>
<th>Contract Code</th>
<th>Contract NO</th>
<th>Contract Name</th>
<th>Decide ?</th>
</tr>
<cfoutput query="qrygetdata">
<tr>
<td>#baseline_num#</td>
<td>#manufacturer#</td>
<td>#model_part_no#</td>
<td>#noun_description#</td>
<td>#top_assembly_id#</td>
<td>#id#</td>
<td>#program_manager#</td>
<td>#orinputdt#</td>
<td>#bldg#</td>
<td>#room#</td>
<td>#dollarformat(acq_cost)#</td>
<td>#sq_ft#</td>
<td>#doc_no#</td>
<td>#custodian_name#</td>
<td>#legacy_source_code#</td>
<td>#contract_cd#</td>
<td>#contract_no#</td>
<td>#contract_title#</td>
<td>
<cfinput name="decide#currentrow#" type="radio" value="Y" checked>Keep
<cfinput name="decide#currentrow#" type="radio" value="N" checked>Delete
</td>
</tr>
</cfoutput>
</table>
<p>
<input type="submit" value="Submit">
</cfform>


Here is my action page below. I want to update the existing table field,
called Decide, with the radio buttons, either Y or N. All
other fields remain the same since they have not changed. I just want to add
either Y or N to the Decide field. Once I get that in there, I can then extract
all records with decide='Y' or decide='N' to do something else. I do not
understand the cfloop collection commands. The book said something about COM or
DCOM (???). Anyway, I am just using a simpe sql update. Obviously, it does not
work. How do I incorporate the update into your cfloop command ?

<cfquery datasource="Justification_db">
<cfloop collection="form" item="thisformfield">
update file_name
set decide = '#form.decide#'
where baseline_num = #form.baseline_num#====>(baseline_num is the unique key
that identifies each record, so I am trying

to use that but I guess currentrow will do the same thing ?)
</cfloop>
</cfquery>

[trojnfn]

Hello Dan (sorry if this posted more than once, it did not seem like it got
submitted the other two times)
Thanks again for your response. Unfortunately, I am fairly new to CF and can
only understand and do some basic stuff. What I am trying to do and what you
are helping me with is way beyond my comprehension. So I don't really
understand some of the stuff that you are doing. Anyway, my form is displayed
below with your radio button feature. Basically, the records are displayed (for
this example, lets say there are six records total) with the radio buttons at
the end. The user will select either keep or delete (six times,one for each
record) and then submit the form. The action page will then do a simple sql
update and put either Y or N into the Decide field of each of the six records.
Can you look at my code below and put in the correct commands to do what I need
it to do ? I know it has something to do with cfloop and currentrow, but again,
I am not sure of the proper context.


<cfform action="action_page.cfm" method="post">

<table border="2" bordercolor="blue" width="300%" cellpadding=1 cellspacing=0>

<tr>
<th>Baseline No</th>
<th>Manufacturer</th>
<th>Model Part No</th>
<th>Noun Description</th>
<th>Top Assembly ID</th>
<th>ID</th>
<th>Program Manager</th>
<th>Orig Input Date</th>
<th>Bldg</th>
<th>Room</th>
<th>ACQ Cost</th>
<th>SQ Feet</th>
<th>Document Number</th>
<th>Custodian Name</th>
<th>Legacy Source Coce</th>
<th>Contract Code</th>
<th>Contract NO</th>
<th>Contract Name</th>
<th>Decide ?</th>
</tr>
<cfoutput query="qrygetdata">
<tr>
<td>#baseline_num#</td>
<td>#manufacturer#</td>
<td>#model_part_no#</td>
<td>#noun_description#</td>
<td>#top_assembly_id#</td>
<td>#id#</td>
<td>#program_manager#</td>
<td>#orinputdt#</td>
<td>#bldg#</td>
<td>#room#</td>
<td>#dollarformat(acq_cost)#</td>
<td>#sq_ft#</td>
<td>#doc_no#</td>
<td>#custodian_name#</td>
<td>#legacy_source_code#</td>
<td>#contract_cd#</td>
<td>#contract_no#</td>
<td>#contract_title#</td>
<td>
<cfinput name="decide#currentrow#" type="radio" value="Y" checked>Keep
<cfinput name="decide#currentrow#" type="radio" value="N" checked>Delete
</td>
</tr>
</cfoutput>
</table>
<p>
<input type="submit" value="Submit">
</cfform>


Here is my action page below. I want to update the existing table field,
called Decide, with the radio buttons, either Y or N. All
other fields remain the same since they have not changed. I just want to add
either Y or N to the Decide field. Once I get that in there, I can then extract
all records with decide='Y' or decide='N' to do something else. I do not
understand the cfloop collection commands. The book said something about COM or
DCOM (???). Anyway, I am just using a simpe sql update. Obviously, it does not
work. How do I incorporate the update into your cfloop command ?

<cfquery datasource="Justification_db">
<cfloop collection="form" item="thisformfield">
update file_name
set decide = '#form.decide#'
where baseline_num = #form.baseline_num#====>(baseline_num is the unique key
that identifies each record, so I am trying

to use that but I guess currentrow will do the same thing ?)
</cfloop>
</cfquery>

[mxstu]

trojnfn,

Use Kronin555's method. It is much simpler. I have attached a scaled down
verision of your form, which you should be able to test. Here is an explanation
of the attached code

Display one checkbox per row. Give all checkboxes the same name and set the
"value" equal to the unique record id from your table ("baseline_num"). Store
all of the record ids from your query in a hidden form field.

So if your table contained (4) records with "baseline_num" id numbers
(1,5,18,22), the html might look like this:


<form>
<input type="checkbox" name="deleteIdList" value="1">
<input type="checkbox" name="deleteIdList" value="5">
<input type="checkbox" name="deleteIdList" value="18">
<input type="checkbox" name="deleteIdList" value="22">

<!--- list of all baseline_num id's being updated --->
<input type="hidden" name="idList" value="1,5,18,22">
</form>


Because all of the checkboxes have the same name, if you check more than one
box, the values will be passed to the action page as a comma delimited list.
So if you checked the first and third box

form.deleteIdList = "1,18"


Then simply use the list of deleted ids to mark those records as deleted.


UPDATE file_name
SET decide = 'N'
WHERE baseline_num IN (#form.form.deleteIdList#)

... translated is ...

WHERE baseline_num IN (1,18)


Both lists are used to update records that were retained. In this case the
records
retained are (5,22)


UPDATE file_name
SET decide = 'Y'
WHERE baseline_num IN (#form.idList#) AND
baseline_num NOT IN (#form.deleteIdList#)

... becomes ...

WHERE baseline_num IN (1,5,18,22) AND
baseline_num NOT IN (1,18)


* Note - cfqueryparam should be used in cfquery statements






<!--- MAIN PAGE --->
<cfform action="action_page.cfm" method="post">
<table border="2">
<tr>
<th>Baseline No</th>
<th>Decide ?</th>
</tr>
<cfoutput query="qrygetdata">
<tr>
<td>#baseline_num#</td>
<td><input name="deleteIdList" type="checkbox"
value="#baseline_num#">Delete</td>
</tr>
</cfoutput>
</table>

<cfoutput>
<input type="hidden" name="idList"
value="#ValueList(qrygetdata.baseLine_Num)#">
</cfoutput>
<input type="submit" value="Submit">
</cfform>


<!--- ACTION PAGE ---->

<!--- ensure variable exists --->
<cfparam name="form.deleteIdList" default="">

<cfif listLen(form.idList) gt 0>
<!--- update any records that were "Deleted" --->
<cfif listLen(form.deleteIdList) gt 0>
<cfquery datasource="#yourDSN#">
UPDATE file_name
SET decide = 'N'
WHERE baseline_num IN (#form.deleteIdList#)
</cfquery>
</cfif>
<!--- update any records that were "Retained" --->
<cfquery datasource="#yourDSN#">
UPDATE file_name
SET decide = 'Y'
WHERE baseline_num IN (#form.idList#)
<cfif listLen(form.deleteIdList) gt 0>
AND baseline_num NOT IN (#form.deleteIdList#)
</cfif>
</cfquery>
</cfif>

[trojnfn]

Hello MXSTU,

I just implemented your code and guess what, I think it works ! I still have
to go back and decipher your explanations so that I can understand them more
clearly, but I think the output is what I want.

A couple of quick questions : By checking the delete box, the user is
indicating they want that record deleted and by leaving the checkbox blank,
they want the record retained ?

What if I add another option called info ? Basically, the user will have to
indicate if they want the record to be deleted, retained (like before) or info
- ask for more info because the record does not tell them enough to delete or
keep. By adding this third option, can I have three checkboxes labeled Delete,
Keep, Info, and still apply your technique, or does that not work. I have not
tested it but will do so now. For simplicity and clarity, I will call my field
Retain instead of Decide. Again, I will update the field with Y for retain
(keep), N for do not retain (delete) and I for more information.

Thanks again for all your help.

[mxstu]

trojnfn,
1) I just implemented your code and guess what, I think it works ! I still
have to go back and decipher your explanations so that I can understand them
more clearly, but I think the output is what I want.

Yes, make sure you understand what it is doing. It may seem complex, but it
is really pretty simple.

2) A couple of quick questions : By checking the delete box, the user is
indicating they want that record deleted and by leaving the checkbox blank,
they want the record retained ?

Yes

3) What if I add another option called info ? Basically, the user will have to
indicate if they want the record to be deleted, retained (like before) or info
- ask for more info because the record does not tell them enough to delete or
keep. By adding this third option, can I have three checkboxes labeled Delete,
Keep, Info, and still apply your technique, or does that not work. I have not
tested it but will do so now. For simplicity and clarity, I will call my field
Retain instead of Decide. Again, I will update the field with Y for retain
(keep), N for do not retain (delete) and I for more information.


No, the single checkbox method would not work for (3) options. I think you
would need to go back to my previous suggestion and provide three radio radio
buttons for each row and dynamically name the form fields using #currentRow#.
Then on the action page extract the values for each field and update the
records one at a time.

[trojnfn]

Hello MXSTU

Obviously I am having problems again. Below is my partial code with the
multiple radio buttons. Can you see what I am doing wrong ?

<td>#legacy_source_code#</td>
<td>#contract_cd#</td>
<td>#contract_no#</td>
<td>#contract_title#</td>
<td>
<cfinput type="radio" name="retain#currentrow#" value="Yes">Retain
<cfinput type="radio" name="retain#currentrow#" value="No">Delete
<cfinput type="radio" name="retain#currentrow#" value="Info">Info
<input type="hidden" name="recordID#currentrow#" value="#recordID#">
</td>

</tr>
</cfoutput>

<cfoutput>
<input type="hidden" name="number_of_records" value="#qrygetdata.recordcount#">
</cfoutput>
</table>

<p>
<input type="submit" value="Submit">

Below is my action page :

<cfloop from="1" to="#form.number_of_records#" index="row">
<cfparam name="form.retain#row#" default="yes">
<cfset recordID = form["recordID"& row]>
<cfset retain = form["retain"& row]>

<cfoutput>
<cfif retain>
retain #recordID#<br>
<cfelse>
Delete #recordID#</cfif></cfoutput></cfloop>

But I am getting the following error :

Error Diagnostic Information

An error occurred while evaluating the expression:


"#form.number_of_records#"



Error near line 57, column 23.

--------------------------------------------------------------------------------


I checked the spelling and it seems correct (unless I cannot see something).
Do you know what the problem is ?
Also, based on this method, how do I update the table for each record with Y,
N, or I values ?

[Kronin555]

Is this:
<input type="hidden" name="number_of_records" value="#qrygetdata.recordcount#">

inside your <cfform>....</cfform> tags?

Turn on debugging and make sure it's getting submitted with your form.

[trojnfn]

Yes, it is between the tags, but I am using <form> instead of <cfform>. would that make a difference ?

where would I turn debuggin on, in the IE browser ?

[Kronin555]

<cfform> vs <form> shouldn't make a difference.

You don't turn debugging on in your browser. You turn it on in the Coldfusion
Administrator for your specific IP address. Then each page request will have a
bunch of nice debugging information on the bottom of it, visible to your
computer (your IP address).

[trojnfn]

OK, I remember now. I have to get hold of the CF Admin to do that, but I don't
think it can be done since we have dynamic IP's and I would have to request
static ip to make this happen.

Other than the error, is my code with the three radio buttons correct ?
In the action page, how do I actually update the table field Retain with
either value Y, N, or I ?

[mxstu]

Is your form is using: < form ... method="POST"> ?

[trojnfn]

Hello again, MXSTU

Now my form won't open at all ! I am getting the following partial message :
site administrator that this error has occurered (be sure to include the
contents of this page in your message to the administrator).

this is my actual form commnad ( I switch between form and cfform)
<cfform action="../../../pages/logistics/property/just_form_review_test.cfm"
method="post">

Here is the end of the code where I put in the multiple radio buttons along
with your other cocde:
<td>#contract_no#</td>
<td>#contract_title#</td>
<td>
<input type="radio" name="retain#currentrow#" value="Yes">Retain
<input type="radio" name="retain#currentrow#" value="No">Delete
<input type="radio" name="retain#currentrow#" value="Info">Info
<input type="hidden" name="recordID#currentrow#" value="#recordID#">
</td>
</tr>
</cfoutput>

<cfoutput>
<input type="hidden" name="number_of_records" value="#qrygetdata.recordcount#">
</cfoutput>
</table>

<p>
<input type="submit" value="Submit">
</cfform>

This is what I have in the action page (did not put the dump in yet)
<cfloop from="1" to="#form.number_of_records#" index="row">
<cfparam name="form.retain#row#" default="yes">
<cfset recordID = form["recordID"& row]>
<cfset retain = form["retain"& row]>

<cfoutput>
<cfif retain>
retain #recordID#<br>
<cfelse>
Delete #recordID#
</cfif>
</cfoutput>
</cfloop>

Where does the update table part go, and do you see anything wrong that will
prevent the page from coming up ?
I rebooted, shut off the computer, ended and restarted IE, etc., and I still
get this error. It worked before and I thought I had
the problem solved, but it just keeps frustrating me to no end ! Can you pleae
help !

[mxstu]

I can't tell anything from that. It is just the standard error "header". Can you post the full error message?