AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of jameskane
jameskane
 asked on

Creating Codfusion Dynamic Form

I have a table which is generated by looping through a recordset. Each line of the table represents a record in the related database table - there are hundreds of lines/ database table records.

 I need to place a form element on each row of the table which will allow me to change the status of that record from OPEN to CLOSED - a simple menu, two values and defaulting to the current database value of open or closed.

I don't have any idea how to turn this table into the appropriate form OR forms that will accomplish this. I have AJAX and cfform ideas rolling around in my head.

Can someone give me a strategy for accomplishing this.

many thanks
ColdFusion Language
Avatar of undefined
Last Comment
jameskane
8/22/2022 - Mon
duncancumming
An Ajax method would probably be nice, so you could update it in realtime without having to submit the form.  However, here's a simple way to do it web 1.0 style!



<form action="page2.cfm" method="POST" name="data">
<table>
<cfoutput query="getData">
<tr>
	<td>#getData.Name#</td>
	<td>
	<select name="status_#getData.ID#">
		<cfif getData.status EQ "Open">
			<option value="Open" selected>Open</option>
			<option value="Closed">Closed</option>
		<cfelse>
			<option value="Open">Open</option>
			<option value="Closed" selected>Closed</option>
		</cfif>
	</select>
	</td>
</tr>
</cfoutput>
</table>
<cfoutput><input type="hidden" name="IDs" value="#ValueList(getData.ID)#"></cfoutput>
<input type="submit" name="submit" value="Submit">
</form>
 
 
page2:
 
<cfparam name="Form.IDs" default="">
 
<cfloop index="ID" list="#Form.IDs#">
	<cfset status = Form["status_#ID#"]>
	
	<cfquery name="updateStatus" datasource="dsn">
		UPDATE yourTable
		SET Status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="6" value="#status#">
		WHERE ID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ID#">
	</cfquery>
</cfloop>

Open in new window

jameskane
ASKER
VERY MANY  THANKS  duncancumming for taking the time to send me that code. It was very timely as I w as partially down that strategic road !!

I have integrated your code into what I had done and think its very close.. but there's a bug somewhere I cant figure out.

I am taking the liberty of attaching my latest code - the form and the action page - plus, unfortunately an  image of the error message! There is probably something massively obvious to you that I am doing wrong.

IF you could take a look, it would be GREAT

thanks, james
FORM :
 
 
<cfquery name="recordset_banklisting" datasource="office">
 
SELECT bank_cheque_listing.cheque_paydate,
     bank_cheque_listing.cheque_value,
     bank_cheque_listing.cheque_bank,
     bank_cheque_listing.cheque_branch,
     bank_cheque_listing.cheque_number,
     bank_cheque_listing.cheque_ac_owner,
     bank_cheque_listing.cheque_status,
     bank_cheque_listing.transactionNumber,
     MEMBERS2.memberID,
     members2.Nom,
     Members2.Prenom,
     members2.Adresse,
     members2.zip,
     members2.ville
FROM bank_cheque_listing
INNER JOIN members2
ON bank_cheque_listing.memberID = members2.memberID
ORDER BY bank_cheque_listing.cheque_paydate ASC
 
 
 
</cfquery>
 
 
 
</head>
 
<body>
<form action="action_form_statusupdate_chequestobank.cfm" method="post" name="data" id="data">
 
<br />
 
 
<table width="100%" border="1">
  <tr>
    <td colspan="7" align="center"><h2>Cheque Information</h2></td>
    <td colspan="5" align="center"><h2>ABAC Member Information</h2></td>
  </tr>
  <tr>
    <td>Date</td>
    <td>Value (euros)</td>
    <td>Number</td>
    <td>Bank</td>
    <td>Branch</td>
    <td>Account Owner</td>
    <td>Cheque Status</td>
    <td>Nom</td>
    <td>Prenom</td>
    <td>Adresse</td>
    <td>ZIP</td>
    <td>Ville</td>
  </tr>
  <cfset color= 1>
  <CFOUTPUT>
  <cfloop query="recordset_banklisting">
  <cfif color EQ 1>
  <tr bgcolor="CCCCCC">
  <cfelse>
  <tr></cfif>
    <td><cfoutput>#recordset_banklisting.cheque_paydate#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.cheque_value#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.cheque_number#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.cheque_bank#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.cheque_branch#</cfoutput></td>
    <cfif #recordset_banklisting.cheque_ac_owner# EQ #recordset_banklisting.memberid#>
	<td><cfoutput>#recordset_banklisting.nom#</cfoutput></td> <cfelse>
    <td><cfoutput>#recordset_banklisting.cheque_ac_owner#&nbsp;***</cfoutput></td></cfif>
    <td><select name="cheque_status_#recordset_banklisting.transactionnumber#" >
     
      <option value="held" <cfif (isDefined("recordset_banklisting.cheque_status") AND "held" EQ #recordset_banklisting.cheque_status#)>selected="selected"</cfif>>Held</option>
      <option value="Cashed" <cfif (isDefined("recordset_banklisting.cheque_status") AND "Cashed" EQ #recordset_banklisting.cheque_status#)>selected="selected"</cfif>>Cashed</option>
    </select></td>
    <td><cfoutput>#recordset_banklisting.nom#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.prenom#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.adresse#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.zip#</cfoutput></td>
    <td><cfoutput>#recordset_banklisting.ville#</cfoutput></td>
  </tr>
  <cfoutput><input name="TransactionNumber" type="hidden"  value="#ValueList(recordset_banklisting.transactionNumber)#" /></cfoutput>
  <cfif #color# EQ 1> 
    <cfset color=0> <cfelse>  <cfset color=1></cfif>
  </cfloop></CFOUTPUT>
</table>
 
 
fsdfsd<cfoutput>#recordset_banklisting.recordcount#</cfoutput>
<cfset session.counter = #recordset_banklisting.recordcount#>
<cfoutput>#session.counter#</cfoutput>
 
<label>
<input type="submit" name="SUBMIT" id="SUBMIT" value="Submit" />
</label>
</form>
</body>
</html>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 
ACTION PAGE /
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<cfparam name="Form.transactionnumber" default="">
 
<cfloop index="ID" list="#Form.transactionnumber#">
        <cfset status = Form["cheque_status_#transactionnumber#"]>
        
        <cfquery name="updateStatus" datasource="Office">
                UPDATE UPDATE bank_cheque_listing
                
                SET cheque_Status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="6" value="#cheque_status#">
                WHERE TransactionNumber = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#transactionNumber#">
               
        </cfquery>
</cfloop>
 
 
 
 
 
 
 
 
 
 
 
</head>
 
<body>
</body>
</html>

Open in new window

error-lastone.jpg
duncancumming
transactionnumber is the hidden form field that contains all the ids.  you want to use just each individual id when you do the processing.  here's some updated code

<cfloop index="ID" list="#Form.transactionnumber#">
        <cfset cheque_status = Form["cheque_status_#ID#"]>
        
        <cfquery name="updateStatus" datasource="Office">
                UPDATE UPDATE bank_cheque_listing
                
                SET cheque_Status = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" maxlength="6" value="#cheque_status#">
                WHERE TransactionNumber = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ID#">
               
        </cfquery>
</cfloop>

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
duncancumming
Also, move this line:
<cfoutput><input name="TransactionNumber" type="hidden"  value="#ValueList(recordset_banklisting.transactionNumber)#" /></cfoutput>
OUTSIDE of the <cfloop></cfloop>.  In fact the cfloop isn't really necessary, you can just use <cfoutput query="blah"> instead, and get rid of all the other <cfoutput> tags, except one for the bit after the loop.

Otherwise you repeat it several times unnecessarily.  Here's some more tidied up code, this time for the first page.  

You know you don't need the # # in most cfset statements?
<cfset session.counter = #recordset_banklisting.recordcount#>
can be better written as:
<cfset session.counter = recordset_banklisting.recordcount>


<body>
<form action="action_form_statusupdate_chequestobank.cfm" method="post" name="data" id="data">
 
<br />
 
<table width="100%" border="1">
<tr>
	<td colspan="7" align="center"><h2>Cheque Information</h2></td>
	<td colspan="5" align="center"><h2>ABAC Member Information</h2></td>
</tr>
<tr>
	<td>Date</td>
	<td>Value (euros)</td>
	<td>Number</td>
	<td>Bank</td>
	<td>Branch</td>
	<td>Account Owner</td>
	<td>Cheque Status</td>
	<td>Nom</td>
	<td>Prenom</td>
	<td>Adresse</td>
	<td>ZIP</td>
	<td>Ville</td>
</tr>
<cfset color= 1>
<CFOUTPUT query="recordset_banklisting">
	<cfif color EQ 1>
		<tr bgcolor="##CCCCCC">
	<cfelse>
		<tr>
	</cfif>
		<td>#recordset_banklisting.cheque_paydate#</td>
		<td>#recordset_banklisting.cheque_value#</td>
		<td>#recordset_banklisting.cheque_number#</td>
		<td>#recordset_banklisting.cheque_bank#</td>
		<td>#recordset_banklisting.cheque_branch#</td>
		<cfif recordset_banklisting.cheque_ac_owner EQ recordset_banklisting.memberid>
			<td>#recordset_banklisting.nom#</td> 
		<cfelse>
			<td>#recordset_banklisting.cheque_ac_owner# ***</td>
		</cfif>
		<td><select name="cheque_status_#recordset_banklisting.transactionnumber#" >
			<option value="held" <cfif (isDefined("recordset_banklisting.cheque_status") AND "held" EQ recordset_banklisting.cheque_status)>selected="selected"</cfif>>Held</option>
			<option value="Cashed" <cfif (isDefined("recordset_banklisting.cheque_status") AND "Cashed" EQ recordset_banklisting.cheque_status)>selected="selected"</cfif>>Cashed</option>
		</select></td>
		<td>#recordset_banklisting.nom#</td>
		<td>#recordset_banklisting.prenom#</td>
		<td>#recordset_banklisting.adresse#</td>
		<td>#recordset_banklisting.zip#</td>
		<td>#recordset_banklisting.ville#</td>
	</tr>
	<cfif color EQ 1> 
		<cfset color=0>
	<cfelse> 
		<cfset color=1>
	</cfif>
</cfoutput>
</table>
 
<cfoutput>
<input name="TransactionNumber" type="hidden"  value="#ValueList(recordset_banklisting.transactionNumber)#" />
 
#recordset_banklisting.recordcount#
<cfset session.counter = recordset_banklisting.recordcount>
#session.counter#
</cfoutput>
 
<input type="submit" name="SUBMIT" id="SUBMIT" value="Submit" />
</form>
</body>
</html>

Open in new window

duncancumming
And finally,  you're using the variable color to alternate background colours on your table rows.  You can simplify all this using a bit of arithmetic.  

a MOD b means divide a by b, and just give me the remainder.  So if the number is even, e.g. 4, when we divide it by 2, it has remainder zero, which evaluates to false.  If the number is odd, it has remainder 1, which evaluates to true (in fact all positive/negative integers evaluate to true).  

<CFOUTPUT query="recordset_banklisting">
	<cfif recordset_banklisting.CurrentRow MOD 2>
	<!--- odd-numbered rows, 1,3,5 etc --->
		<tr bgcolor="##CCCCCC">
	<cfelse>
	<!--- even-numbered rows, 2,4,6 etc --->
		<tr>
	</cfif>

Open in new window

duncancumming
In my earlier comment starting "transactionnumber is the hidden form field", slight error:
UPDATE UPDATE bank_cheque_listing

should just be:
UPDATE bank_cheque_listing

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jameskane
ASKER
Whooh !!!!  thanks SO much !!

i took your code and ran it and Its getting very close - there still seems to be a problem with the following line

WHERE TransactionNumber = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ID#">

I am attaching the error message for that.

thanks again very much
error-lastone2.jpg
ASKER CERTIFIED SOLUTION
duncancumming
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jameskane
ASKER
GOT IT !! was a typo on my part. This is really fantastic - got the page working and learned a lot !!
If you have another second, could you just give me a little explanation of
<cfset cheque_status = Form["cheque_status_#ID#"]
I understand the approach, but cant figure this line.

AGAIN  VERY MANY THANKS !!!

james
jameskane
ASKER
This was just   AMAZING support. NEVER expected help link this!!!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
duncancumming
ok, that line is a bit like doing
<cfset cheque_status = Form.cheque_status_#ID#>

However the above line won't work because of the #ID# part.  However, all Coldfusion structures can be accessed like an associative array.  e.g.
URL.name
is identical to
URL["name"]

This allows us to use dynamic variables as the structure key.  In the example, because it's in the loop, it's the same as writing
<cfset cheque_status = Form["cheque_status_1"]>
<cfset cheque_status = Form["cheque_status_2"]>
etc

Which is also the same as writing
<cfset cheque_status = Form.cheque_status_1>
<cfset cheque_status = Form.cheque_status_2>
etc
jameskane
ASKER
got it !  thanks again

james
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent