The Cold Fusion Web Database Construction Kit
Now that you are familiar with using Cold Fusion to build simple data submission forms, you can delve into the methods necessary to develop a complex data entry application presented at the end of this chapter.
This chapter makes extensive use of JavaScript. Be sure to review Chapter 19, "Additional Form Data Validation Techniques," before proceeding.
Working with HTML form objects is really very simple. They have limited functionality and few formatting options. Implementing forms that have a nicely formatted appearance requires you to rely on some rather unorthodox techniques.
One of the difficulties you may encounter when creating forms is getting all your objects to line up in a nicely organized, columnar format. Standard practice, depicted in Figure 20.1, is to place a field description on the line above a form object. This practice, however, usually results in the form exceeding the length of the screen, requiring the user to scroll down the page while performing data entry. From a usability standpoint, you want to have all your data entry fields viewable by the user at the same time. Tables can help you achieve this goal.
Figure 20.1 An example of form layout used by an unsophisticated Webmaster.
The trick to effective form layout is to create a two column table where you embed your data entry field and a brief description. Thus, the construct used to create results in Figure 20.1:
<STRONG><Email></STRONG><BR> <INPUT TYPE="TEXT" NAME="email" VALUE="#email#" SIZE=30 MAXLENGTH=30>
is summarily replaced with:
<TR> <TD ALIGN="RIGHT"> <STRONG>Email</STRONG> </TD> <TD> <INPUT TYPE="TEXT" NAME="email" VALUE="#email#" SIZE=30 MAXLENGTH=30> </TD> </TR>
resulting in the form depicted in Figure 20.2.
NOTE: While using tables can aid you in the placement and formatting of form fields, be advised that a browser does not start rendering a table until it has received the entire contents of that table. This may lead to the end-user's perception that your table formatted forms take longer to load than forms not using this technique.
Figure 20.2 Using tables, you can create nicely formatted forms.
Frames allow you to create multiple, independent, scrollable regions within your browser and can prove quite useful when implementing complex data entry applications. Frames are commonly used to create toolbars and display related information. In Cold Fusion they can be invaluable for performing interactive data lookups.
NOTE: Frames have not been formally accepted into the HTML specification and are not supported by every browser. Before you design your application using frames, make sure your users are running a frames-capable browser, such as Netscape 2.0+ or Microsoft Internet Explorer 3.0+.
Frames are implemented using only three tags: <FRAMESET>, <FRAME>, and <NOFRAMES>. The <FRAMESET> tag and its attributes specify the number of individual frames to be created and their physical properties, such as width and height. The <FRAME> tag defines the attributes of a single frame, such as whether scrolling or resizing is allowed and what URL is to be launched within it. The <NOFRAMES> tag is used to provide support for older browsers that do not support frames.
<FRAMESET> Attributes. <FRAMESET> has two attributes, ROWS and COLS, which define the height and width of individual frames. The number of individual frames to be created is implicitly specified by the number of entries.
The code in Listing 20.1 demonstrates the various methods that can be employed to define framesets. Each FRAMESET contains two frames--one twice as tall as the other--for a browser running full-screen in 800*600 resolution:
){kind=link}
){kind=link}
flMethod 1: Frame sizes can be specified as a percentage of area[daggerdbl]
<FRAMESET ROWS="66%,33%">
<FRAME SRC="myurl" Name="Frame1">
<FRAME SRC="myurl" Name="Frame2">
</FRAMESET>
flMethod 2: You can also define frames as sizes relative to each other[daggerdbl]
<FRAMESET ROWS="2*,*">
<FRAME SRC="myurl" Name="Frame1">
<FRAME SRC="myurl" Name="Frame2">
</FRAMESET>
flMethod 3: Frame sizes can also be defined as an absolute size in pixels[daggerdbl]
<FRAMESET ROWS="400,200">
<FRAME SRC="myurl" Name="Frame1">
<FRAME SRC="myurl" Name="Frame2">
</FRAMESET>
TIP: A single asterisk (*) character is interpreted by the browser as a request to give the frame all remaining available space. This is particularly useful because available space is both platform-dependent and may vary due to browser resizing.
<FRAME> Attributes. The <FRAME> tag defines the physical attributes of a single frame. The following are the attributes of the <FRAME> tag and their syntax:
The SRC attribute defines the URL to be loaded in the frame. You can specify a CF- generated template as the initial URL. If no URL is specified, the frame is left empty.
The NAME attribute is used to define a name for the frame so it can be explicitly referenced. While NAME is technically an optional attribute, in practice, you always want to assign a name to your frames. Our sample application forces frame reloads, updates multiple frames simultaneously, and initiates form submissions in one frame that are actually executed in a different frame. While you can reference different frames as JavaScript array elements, your code will be better understood by others if you reference frames by name.
The NORESIZE attribute is usually invoked to create a button bar or banner. It should only be used when you are confident about the size of objects to be placed within a frame.
The SCROLLING attribute determines whether scroll bars are visible within the frame. A value of yes forces scroll bars to appear whether they are needed or not. Conversely, a value of no hides scroll bars. The default value for this attribute is auto, which displays scroll bars only if needed.
MARGINWIDTH and MARGINHEIGHT are optional attributes defining a margin area (in pixels) for the frame where no text or objects are rendered.
Frame Examples--Our Sample Application. Like tables, framesets can be nested
within other framesets. The sample order entry system uses this technique to implement
the complex frame layout, described in Listing 20.2 and pictured in Figure 20.3.
Each frame contains its own independent URL. One of the biggest challenges in working
with frames is coordinating the interactions between independent CF templates. These
strategies involve making extensive use of JavaScript and hidden forms and are discussed
later in the chapter.
<TITLE>A2Z Books - Customer Invoicing</TITLE> <FRAMESET ROWS="55,*"> <FRAME SRC="common/menubar.CFM" name="menubar" noresize scrolling=no marginheight=3 ¬marginwidth=3> <FRAMESET COLS="45%,55%"> <FRAMESET ROWS="50%,*,30"> <FRAME SRC="advforms/sampleapp/customerstart.CFM" NAME="customer" MARGINWIDTH=0> <FRAME SRC="advforms/sampleapp/invoicestart.CFM" NAME="invoice" MARGINWIDTH=0> <FRAME SRC="advforms/sampleapp/lineitemstart.CFM" NAME="lineitems" MARGINWIDTH=0> </FRAMESET> <FRAME SRC="advforms/sampleapp/instruct.CFM" NAME="display" MARGINWIDTH=0> </FRAMESET> </FRAMESET> <NOFRAMES> <CFINCLUDE TEMPLATE="/que/advforms/common/noframes.CFM"> </BODY> </HTML> </NOFRAMES>
Figure 20.3 The FRAMESET in Listing 20.2 produces this interface.
The SELECT element allows you to pick from a predefined set of choices. SELECT objects are usually implemented as pull-down menus and can be extremely useful in enforcing data integrity, because menu choices can be pre-validated.
Formatting Pull-Down Menus. <SELECT> objects, implemented as pull-down
menus, present an unusual formatting challenge. Unlike the TEXT element whose SIZE
attribute determines the horizontal width of the object, the SIZE attribute of a
SELECT element instead specifies the vertical length of the object and is usually
used only when implementing a multiselect box. The horizontal width of pull-downs
is set by the browser, calculated dynamically by the length of the longest text string
within the SELECT menu, leading to non-right-aligned objects, as depicted in Figure
20.4.
Figure 20.4 Because <SELECT> objects have no attribute for determining object width, right-alignment appears to be impossible.
By establishing a standard <OPTION> that has a string of characters whose length is longer than any other item within any other pull-down menu you are attempting to align, you can, in effect, set the width of the select boxes to be aligned. Listing 20.3 illustrates this technique, resulting in the right-aligned select boxes shown in Figure 20.5.
){kind=link}
){kind=link}
<TABLE>
<TR>
<TD><STRONG>Select Category</STRONG></TD>
<TD>
<SELECT NAME="id">
<OPTION VALUE=0>
<OPTION VALUE=0>----------------------
<CFOUTPUT QUERY="getcategory">
<OPTION VALUE=#ID#>#category#
</CFOUTPUT>
</SELECT>
</TD>
</TR>
<TR>
<TD><STRONG>Select Shipping Method</STRONG></TD>
<TD>
<SELECT NAME="shipmethodid">
<OPTION VALUE=0>
<OPTION VALUE=0>----------------------
<CFOUTPUT QUERY="getshipmethod">
<OPTION VALUE=#shipmethodid#>#shippingmethod#
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>
Figure 20.5 By using the technique employed in Listing 20.3, you can trick your browser into creating pull-down menus of equal width.
Using Cold Fusion to Set Selected Menu Items. Cold Fusion makes populating
list boxes simple, but how do you indicate an item that has been previously selected
after the form has been reloaded? Fortunately, the <OPTION> element contains
a SELECTED attribute. By placing a <CFIF>...</CFIF> construct within
a <CFOUTPUT> query, as implemented in Listing 20.4, you can set selected menu
items to be marked, as depicted in Figure 20.6.
){kind=link}
<TD ALIGN="RIGHT">
<STRONG>Ship By</STRONG>
<CFOUTPUT>
<SELECT NAME="shipmethodid">
<OPTION VALUE=0>
<OPTION VALUE=0>----------------------
</CFOUTPUT>
<CFOUTPUT query="shippingmethod">
<CFIF #getorder.shipmethodid# is #shippingmethod.shipmethodid#>
<OPTION VALUE=#shipmethodid# SELECTED>#shippingmethod#
<CFELSE>
<OPTION VALUE=#shipmethodid#>#shippingmethod#
</CFIF>
</CFOUTPUT>
</SELECT>
</TD>
Figure 20.6 Using Cold Fusion, you can dynamically populate pull-down menus and have them display previously selected values.
Working with Multiselect Boxes. Multiselect boxes function much like checkboxes--they
both return a comma delimited list of values to a form. These comma delimited lists
can then be parsed using Cold Fusion's LIST functions and used in conjunction with
<CFLOOP> to save data in a many-to-many relationship. In Listing 20.5 and Figure
20.7, a multiselect box is used to select and display the categories of books that
interest a customer. Note the use of a union query to differentiate between the categories
that have been selected and those that are of no interest to the customer.
){kind=link}
<CFQUERY NAME="getcustomer" DATASOURCE="a2zdata">
SELECT * FROM customers where customerid=#customerid#
</CFQUERY>
<CFQUERY NAME="getinterests" DATASOURCE="a2zdata">
SELECT category.category,category.id,1 as flag
FROM customerinterests,category
where customerinterests.customerid=#customerid#
AND customerinterests.id=category.id
UNION
SELECT category.category,category.id,0 as flag
FROM category
WHERE category.id
NOT IN (SELECT id
FROM customerinterests
WHERE customerinterests.customerid=#customerid#
)
ORDER BY category
</CFQUERY>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<BODY BGCOLOR=#C0C0C0>
<H2>
Many to Many Relation Using Advanced CF
<HR WIDTH=100%>
</H2>
<CENTER>
<CFOUTPUT QUERY="getcustomer">
<STRONG>Customer Name:</STRONG> <EM>#lastname#,#firstname#</EM>
</CFOUTPUT>
<FORM ACTION="advforms/htmltricks/savemany2many.cfm" METHOD="POST">
<CFOUTPUT>
<INPUT TYPE="hidden" NAME="customerid" VALUE=#getcustomer.customerid#>
</CFOUTPUT>
<SELECT NAME="interests" SIZE=5 MULTIPLE>
<CFOUTPUT QUERY="getinterests">
<CFIF #flag# is 1>
<OPTION VALUE=#id# SELECTED>#category#
<CFELSE>
<OPTION VALUE=#id#>#category#
</CFIF>
</CFOUTPUT>
</SELECT><BR>
<INPUT TYPE="submit" VALUE="Save Changes">
</FORM>
</CENTER>
</BODY>
</HTML>
Figure 20.7 A many-to-many relation can sometimes be represented as a multiselect box.
Selections from the multiselect box are passed to CF templates as a comma delimited list. Listing 20.6 demonstrates using CFLOOP to "walk" through the list, saving each item out as an individual record in a table.
){kind=link}
<!--- First, delete old entries --->
<CFQUERY NAME="DeleteOldEntries" DATASOURCE="a2zdata">
DELETE FROM Customerinterests where customerid=#customerid#
</CFQUERY>
<!---Now, use CFloop and GetlistAt to save entries from form--->
<CFIF #parameterexists(interests)# is "Yes">
<CFLOOP INDEX="ii" FROM="1" TO="#listlen(interests)#">
<CFSET #item# = #listgetat(interests,ii)#>
<CFQUERY NAME="saveitem#ii#" DATASOURCE="a2zdata">
INSERT INTO customerinterests (customerid,id) VALUES (#customerid#,#item#)
</CFQUERY>
</CFLOOP>
</CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<BODY BGCOLOR=#C0C0C0>
<H3>Customer Interests Saved<HR WIDTH=100%></H3>
<CENTER>
<CFOUTPUT>
<FORM ACTION="advforms/htmltricks/many2many.cfm&customerid=#customerid#" ¬METHOD="POST">
</CFOUTPUT>
<INPUT TYPE="submit" VALUE="Click here to re-edit the customer preferences">
</FORM>
</CENTER>
</BODY>
</HTML>
Most forms you see on the Web contain two button objects, one whose function is to submit the form's input fields to be processed by the URL specified as the form's ACTION and another that clears the input fields on that page. However, there are actually two different types of buttons you can utilize in your forms, each having its own idiosyncrasies and purpose.
Working with Submit Buttons. Submit buttons are those most commonly found in data entry forms. When pressed, they cause the data contained in the form's input fields to be transferred to the URL specified as the form's ACTION. The following code snippet asks for your name and presents you with buttons to either submit your name to the CF template savedata.cfm or erase any data you may have entered in the input field yourname.
<FORM ACTION="/cgi/dbml.exe?template=/savedata.cfm" METHOD="POST"> Your Name: <INPUT TYPE="text" NAME="yourname" SIZE="20"> <INPUT TYPE="SUBMIT" VALUE="Save Changes"> <INPUT TYPE="RESET" VALUE="Clear"> </FORM>
Submit buttons function in a manner similar to that of radio buttons. You can create multiple submit buttons on a form, as shown in Listing 20.7, by assigning each button the same name but a different value.
<FORM ACTION="advforms/sampleapp/customer.cfm" METHOD="POST" ¬ target="display"> <CFIF #parameterexists(customerid)# is "Yes"> <CFOUTPUT> <INPUT TYPE="hidden" NAME="customerid" value=#customerid#> </CFOUTPUT> <INPUT TYPE="SUBMIT" NAME="custactions" VALUE="Edit"> </CFIF> <INPUT TYPE="SUBMIT" NAME="custactions" VALUE="New"> <INPUT TYPE="TEXT" NAME="Custname" VALUE="" SIZE=10 MAXLENGTH=10> <INPUT TYPE="SUBMIT" NAME="custactions" VALUE="Lookup"> </FORM>
Your submit button, after it's named, is passed to the target URL like any other input object where its value can be parsed. Listing 20.8 implements this functionality allowing a single .CFM file to perform several different operations.
<!--customer.cfm-->
<!--This template performs the following 3 functions:-->
<!--*Searches the customer table based on lastname-->
<!--*Uses a sentinel (customerid 1) to create a New customer-->
<!--*Edits a pre-existing customer-->
<CFQUERY NAME="getcust" DATASOURCE="a2zdata">
SELECT *
FROM customers
<CFIF #custactions# contains "Lookup">
WHERE lastname LIKE `%#custname#%' and customerid > 1
</CFIF>
<CFIF #custactions# contains "New">
WHERE customerid=1
</CFIF>
<CFIF #custactions# contains "Edit">
WHERE customerid=#customerid#
</CFIF>
ORDER BY lastname,firstname
</CFQUERY>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<BODY BGCOLOR="#C0C0C0">
<CFIF #custactions# contains "lookup">
<!--Output results based on LIKE query and ask user to select-->
<!--customer-->
<CENTER>
<CFOUTPUT>
<H3>
Your query returned #getcust.recordcount# results
</H3>
</CFOUTPUT>
<TABLE>
<TH></TH><TH>Last Name</TH><TH>First Name</TH><TH>Address 1</TH>
<CFOUTPUT QUERY="getcust">
<TR>
<TD>
<A
HREF="advforms/sampleapp/loadcustomer.cfm&customerid=#customerid#"
target="customer">
<IMG SRC="/que/graphics/blue.gif" BORDER=0></A>
</TD>
<TD>#lastname#</TD>
<TD>#firstname#</TD>
<TD>#address1#</TD>
</TR>
</CFOUTPUT>
</TABLE>
<CFELSE>
<!--Either edit a preexisting customer or create a new one-->
<!--Customer data entry fields are contained in customerfields.cfm-->
<CENTER>
<CFIF #custactions# contains "New">
<H2>Create New Customer Record</H2>
<CFELSE>
<H2>Edit Customer Record</H2>
</CFIF>
<FORM ACTION="advforms/sampleapp/customersave.cfm"
METHOD="POST">
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/customerfields.cfm">
<INPUT TYPE="SUBMIT" VALUE="Save">
</FORM>
</CENTER>
</CFIF>
</BODY>
</HTML></HTML>
Working with JavaScript Buttons. JavaScript button syntax is nearly identical to that of conventional submit buttons; however, when pressed, they do not perform a form submission. Typically, these buttons are used in combination with the JavaScript event OnClick to execute a JavaScript function, using the following syntax:
<INPUT TYPE="button" Name="mybutton" value="I Will Not Submit!" ¬onClick="checkinput()">
Detailed examples of Javascript button functionality are provided in the following section.
HTML is a markup language designed principally for the cross-platform transferral of textual information. It contains limited facilities for use in application development. Some browsers, however, support a high-level, scripting language called JavaScript that has the primary purpose of helping you develop browser-centric applications. JavaScript statements are invoked within HTML documents, giving you a very high level of control over your browser environment. While no facilities exist natively in JavaScript for communicating with databases, however, when paired with Cold Fusion, it becomes a powerful ally in your quest to develop data-driven applications. Before you proceed any further, be sure you understand the concepts outlined in Chapter 19, "Additional Form Data Validation Techniques."
The JavaScript location property and href attribute allow you to reload the contents of several frames simultaneously. A frame reload directive follows the following syntax:
[windowrefererence].location.href="url"
In Listing 20.9, information about a customer is loaded into a frame. After the page has finished loading, the <BODY ONLOAD> directive executes the JavaScript function loadinvoicewindows(). Loadinvoicewindows(), in turn, resets the location.href of the two frames named invoice and lineitems, causing an immediate reload of each. Figure 20.8 indicates the end result of selecting a customer.
<CFQUERY NAME="getcust" DATASOURCE="a2zdata">
SELECT *
FROM customers
WHERE customers.customerid=#customerid#
</CFQUERY>
<CFQUERY NAME="getlastdate" DATASOURCE="a2zdata">
SELECT max(orderdate) as lastorderdate
FROM orders
WHERE orders.customerid=#customerid#
</CFQUERY>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<SCRIPT>
function loadinvoicewindows() {
<CFOUTPUT>
top.invoice.location.href="advforms/sampleapp/
¬ invoicebrowse.cfm?customerid=#customerid#"
top.lineitems.location.href="advforms/sampleapp/lineitemstart.cfm"
</CFOUTPUT>
}
</SCRIPT>
<BODY BGCOLOR="#C0C0C0" ONLOAD=loadinvoicewindows()>
<STRONG><FONT SIZE="-3">Customer Information</FONT></STRONG><BR>
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/displaycustomerinfo.cfm">
<BR>
<TABLE WIDTH=100% border=1>
<TR>
<TD VALIGN="CENTER">
<CENTER>
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/custnavbar.cfm">
</CENTER>
</TD>
</TR>
</TABLE>
Figure 20.8 Selecting a customer causes the associated list of invoices to load automatically.
TIP: In Listing 20.9, Cold Fusion was used to generate JavaScript. The techniques presented here are used extensively in our sample application to build a recursive save routine for order line items.
Most browsers contain a status bar, keeping you informed about what actions are being performed and providing additional help to the user. For instance, moving your mouse cursor on top of the Netscape Back arrow causes the text Return to previous document in history list to appear in this area. Using the JavaScript method window.status you can add this additional level of support to your forms, making them more friendly. Listing 20.10 provides a "wrapper" for this functionality.
){kind=link}
<SCRIPT LANGUAGE="JavaScript">
function helpme(xstr) {
window.status=xstr; return true
}
</SCRIPT>
You can deploy this function on your forms, as implemented in Listing 20.11, to create context-sensitive help for your data entry fields. As depicted in Figure 20.9, tabbing between fields causes your browser's status line message to change depending on cursor location.
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/javafunc.cfm">
<BODY BGCOLOR=#C0C0C0>
<FORM ACTION="advforms/javascript/savecustomer.cfm" METHOD="POST"
¬NAME="custform">
<CENTER>
<H2>Create New Customer (JavaScript Window.Status Example)</H2>
<TABLE BORDER=1>
<TR>
<TD><STRONG>Last Name</STRONG></TD>
<TD><INPUT TYPE="TEXT" NAME="lastname" SIZE=30 MAXLENGTH=30 ¬OnFocus="helpme(`Please enter your last name')"></TD>
</TR>
<TR>
<TD><STRONG>First/M.I.Name</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="firstname" SIZE=26 MAXLENGTH=30 ¬OnFocus="helpme(`Please enter your first name')">
<INPUT TYPE="TEXT" NAME="middleinit" SIZE=1 MAXLENGTH=1 ¬OnFocus="helpme(`Please enter your middle initial')">
</TD>
</TR>
</TABLE><BR><BR>
<INPUT TYPE="Submit" OnFocus="helpme(`Clicking here will save the customer ¬information')" VALUE="Save">
</FORM>
</CENTER>
</BODY></HTML>
Figure 20.9 Using JavaScript, you can provide additional information about interactive objects on your browser's status line.
You can reference forms and objects on forms through the JavaScript arrayforms. In Listing 20.12, the forms named "form1" and "form2" can be alternately referenced in Javascript as document.forms[0] and document.forms[1]:
<HTML> <BODY> <FORM NAME="form1" ACTION="saveorder.cfm" METHOD="POST"> <INPUT TYPE="TEXT" NAME="shipto" SIZE="30" VALUE=""> </FORM> <FORM NAME="form2" ACTION="saveorderitems.cfm" METHOD="POST"> <INPUT TYPE="HIDDEN" NAME="quantity" VALUE="0.00" SIZE="8"> </FORM> </BODY> </HTML>
It follows, then, that you can reference the value of the form element shipto in Listing 20.12 as either of the following:
document.forms[0].shipto.value document.form1.shipto.value
JavaScript also provides a facility for determining the number of forms contained in a document. This can be particularly useful in setting an upper bounds within a for loop to perform summations of field values or multiform submissions. The syntax :
document.forms.length
Using JavaScript, you cannot add form objects or text to a page without reloading the entire document. After a document element has been formatted, it may not be changed. You can, however, update form field values without initiating a page reload. Listing 20.13 and Figure 20.10 demonstrate the use of Javascript combined with Cold Fusion, to validate the bookid field in the order entry application and provide immediate feedback to the user as to whether or not he or she has entered a correct identification number.
){kind=link}
<!--This template performs validation on the bookid field -->
<!--in the order.cfm template. -->
<!--Parameters required: -->
<!--Bookid: the bookid entered by the user -->
<!--Formid: The form id of the line item in the order form -->
<!--
<CFIF #bookid# is not "">
<CFQUERY NAME="getbook" DATASOURCE="a2zdata">
SELECT * FROM inventory where bookid=#bookid#
</CFQUERY>
</CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
<CFIF #bookid# is not "">
<CFIF #getbook.recordcount# is 1>
top.display.document.forms[#formid#].title.value="#getbook.title#"
<CFELSE>
top.display.document.forms[#formid#].title.value="*Error"
</CFIF>
<CFELSE>
top.display.document.forms[#formid#].title.value="*Error"
</CFIF>
// -->
</SCRIPT>
</CFOUTPUT>
</BODY>
</HTML>
Figure 20.10 Using Cold Fusion and JavaScript, you can cross-reference tables on-the-fly to fill in form fields, providing immediate feedback to the user about whether he or she has entered valid data.
There are two techniques for submitting form fields for processing. The most commonly used method is to use a submit button. Using this technique, however, has several drawbacks. You can use the OnClick event handler to call a JavaScript function; however, after a form submission has been initiated, there is no way to stop it. Using a JavaScript button with an OnClick event, however, gives you unlimited flexibility in performing actions such as form validation, asking for user confirmation, and performing calculations prior to sending the data to an HTTP server. In fact, using JavaScript buttons, you need not perform a form submission at all. The syntax for form submission follows:
formname.submit()
Formname is the name of any form or form element from the forms array.
Using JavaScript, you can launch alert dialog boxes to notify the user of a potential problem or completed action. Alerts are dialog boxes with a single OK button and do not return a value to the calling function. The syntax for creating an alert is simple:
alert(stringexpr)
where stringexpr is a JavaScript expression that evaluates as a character string. A sample alert box is displayed in Figure 20.11.
JavaScript also supports an interactive dialog box with OK and Cancel buttons, shown in Figure 20.10. The Confirm dialog box returns a value of true if the user clicks the OK button, and a value of false if Cancel is clicked. The syntax for invoking a Confirm is similar to that of the alert:
myval=confirm(stringexpr)
These dialog boxes are both modal. JavaScript execution suspends until the user makes a selection. Listing 20.14 contains a sample implementation of this functionality.
Figure 20.11 JavaScript allows you to generate interactive confirmation dialog boxes.
){kind=link}
){kind=link}
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<SCRIPT LANGUAGE="JavaScript">
function confirmsave(){
if (confirm("Please confirm: Create record for ¬"+document.custform.lastname.value)) {
document.custform.submit()
}
else {
alert("Save Aborted")
}
}
</SCRIPT>
<BODY BGCOLOR=#C0C0C0>
<FORM ACTION="advforms/javascript/savecustomer.cfm" METHOD="POST" ¬NAME="custform">
<CENTER>
<H2>Create New Customer (JavaScript Confirmation Dialog/Alert Example)</H2>
<TABLE BORDER=1>
<TR>
<TD><STRONG>Last Name</STRONG></TD>
<TD><INPUT TYPE="TEXT" NAME="lastname" SIZE=30 MAXLENGTH=30></TD>
</TR>
<TR>
<TD><STRONG>First/M.I.Name</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="firstname" SIZE=26 MAXLENGTH=30 >
<INPUT TYPE="TEXT" NAME="middleinit" SIZE=1 MAXLENGTH=1>
</TD>
</TR>
</TABLE><BR><BR>
<INPUT TYPE="button" OnClick="confirmsave()" VALUE="Save">
</FORM>
</CENTER>
</BODY>
</HTML>
Figure 20.12 You can alert the user when an unexpected action occurs.
JavaScript provides a method whereby you can open multiple instances of your browser, treating each as a custom object. The syntax for opening a new browser window is:
){kind=link}
WindowPropertyName = window.open("URL", "targetname" [,"WindowOptions"])
This syntax can be somewhat confusing. WindowPropertyName is a window variable you use when referring to setting window properties and methods. These include performing functions such as a document reload (window.location.href), closing the window (window.close), and launching an alert dialog box (window.alert).
Targetname is only used when you want to reference the new window as a TARGET attribute of a URL load:
<FORM ACTION="mytemplate" target="Targetname") <A HREF="mytemplate" target="Targetname>Click Here</A>
Listing 20.15 and Figure 20.13 demonstrate opening a new browser window to display additional help about data entry fields. As the user moves the cursor from field to field on the data entry form, a JavaScript function is executed, which sets a hidden form field with the passed numeric ID. When the user presses the Help button, function help() is executed, referencing the hidden form field, opening the new window, and executing a CF template in the new window. This template, in turn, outputs additional help stored in a memo field within the Help table. Take special note of the technique used to pass the helpkey.value to the Cold Fusion template.
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<SCRIPT>
function helpme2(msg,helpid){
window.status=msg
document.custform.helpkey.value=helpid
return true
}
function confirmsave(){
if (confirm("Please confirm: Create record for "+document.custform.lastname.value)) {
document.custform.submit()
}
else {
alert("Save Aborted")
}
return true
}
function help(){
helpwindow=window.open("","myhelpwindow","scrollbars=yes,status=no, ¬width=200,height=300, location=no,resizeable=yes")helpwindow.document.location.href="advforms/¬javascript/loadhelp.cfm&helpid="+document.custform.helpkey.value
return true
}
</SCRIPT>
<BODY BGCOLOR=#C0C0C0>
<FORM ACTION="advforms/javascript/savecustomer.cfm" METHOD="POST" NAME="custform">
<INPUT TYPE="hidden" NAME="helpkey" VALUE=0>
<CENTER>
<H2>Create New Customer (Context Sensitive Help Example)</H2>
<TABLE BORDER=1>
<TR>
<TD><STRONG>Last Name</STRONG></TD>
<TD><INPUT TYPE="TEXT" NAME="lastname" SIZE=30 MAXLENGTH=30 OnFocus="helpme2(`Enter the last name of the customer',1)"></TD>
</TR>
<TR>
<TD><STRONG>First/M.I.Name</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="firstname" SIZE=26 MAXLENGTH=30 OnFocus="helpme2(`Enter the first name of the customer',2)">
<INPUT TYPE="TEXT" NAME="middleinit" SIZE=1 MAXLENGTH=1 OnFocus="helpme2(`Enter the middle initial of the customer',3)">
</TD>
</TR>
</TABLE><BR><BR>
<INPUT TYPE="button" OnClick="help()" VALUE="Help">
<INPUT TYPE="button" OnClick="confirmsave()" VALUE="Save">
</FORM>
</CENTER>
</BODY>
</HTML>
Listing 20.16 outputs the contents of a memo field in the help table to the user in the miniature browser you opened with the window.open method. The memo field may contain HTML codes, affording you much flexibility in designing your on-line help.
<CFQUERY NAME="gethelp" DATASOURCE="a2zdata">
SELECT * FROM help where helpid=#helpid#
</CFQUERY>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<BODY BGCOLOR=#008080>
<H3>On-Line Help!</H3>
<CFIF #gethelp.recordcount# is 1>
<CFOUTPUT>#gethelp.help#</CFOUTPUT>
<CFELSE>
<CENTER>Sorry, no help is available on this topic</CENTER>
</CFIF>
</BODY>
</HTML>
Figure 20.13 Using JavaScript, you can create a context-sensitive help environment similar to a standard Windows help file.
Developing complex applications using Cold Fusion leads to the creation of a large number of template files. This can become a real headache when it comes time to maintain your work. In this section, you learn about strategies for merging template functions into a single file and reducing redundant code in your applications.
Cold Fusion provides a powerful mechanism for importing template code at runtime. By cleverly structuring your code, you can, in effect, create a library of callable subroutines, invoked with <CFINCLUDE> and passed parameters with <CFSET>. Listing 20.17 illustrates a Cold Fusion template functioning as a page hit counter.
){kind=link}
<!--loghits.cfm -->
<!--This template logs hits to a page. -->
<!--It should be CFINCLUDE'd on the page page you want to -->
<!--track with a CFSET #pageref# = "page reference" directly -->
<!--preceeding it. -->
<CFQUERY NAME="logme" DATASOURCE="A2ZData"
INSERT LOG (ipaddress,browsertype,pageref) VALUES (`#cgi.remote_addr#','#cgi.http_user_agent#','#pageref#')
</CFQUERY>
Now that you have built this page hit mechanism, it becomes a trivial matter to log Cold Fusion page hits to a database table:
<CFSET #pageref# = "MyPage"> <CFINCLUDE TEMPLATE="advforms/cftricks/loghits.cfm"> <HTML><BODY>Your page access has just been logged!</BODY></HTML>
You may be required to develop an interface for batch data entry, such as an event registration form. To help you in this endeavor, consider Listing 20.18. Using a combination of submit buttons, <CFSET> and <CFLOCATION>, the template allows the user to input record after record quickly and efficiently. If the data to be entered is pre-sorted by geographic location, data entry occurs even more rapidly because the fields most likely to be the same (city,state,zip) are automatically propagated.
<!--Signup.cfm-->
<!--You might want to use this form algorithm when creating a -->
<!--signup sheet for an event or when batch entering data.-->
<CFIF #parameterexists(action)# is "Yes">
<CFIF #action# contains "Cancel">
<CFLOCATION URL="/">
<CFELSE>
<CFINSERT DATASOURCE="a2zdata" TABLENAME="customers" FORMFIELDS="lastname,firstname,middleinit,address1,address2,city,state,zip,email, ¬phone,fax">
<CFIF #action# contains "Exit">
<CFLOCATION URL="/">
</CFIF>
</CFIF>
<CFELSE>
<CFSET #city#="">
<CFSET #state#="">
<CFSET #zip#="">
</CFIF>
<CFSET #lastname#="">
<CFSET #firstname#="">
<CFSET #middleinit#="">
<CFSET #address1#="">
<CFSET #address2#="">
<CFSET #email#="">
<CFSET #phone#="">
<CFSET #fax#="">
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<BODY BGCOLOR=#C0C0C0>
<CENTER>
<H2>Event Signup Demo</H2>
(This form calls itself)
<FORM ACTION="advforms/cftricks/signup.cfm" METHOD="POST">
<INPUT TYPE="hidden" NAME="lastname_required" VALUE="You must enter a last ¬name">
<CFOUTPUT>
<TABLE>
<TR>
<TD ALIGN=RIGHT><STRONG>Last Name</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="lastname" VALUE="#lastname#" SIZE=30 MAXLENGTH=30>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>First/M.I.Name</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="firstname" VALUE="#firstname#" SIZE=26 MAXLENGTH=30>
<INPUT TYPE="TEXT" NAME="middleinit" VALUE="#middleinit#" SIZE=1 MAXLENGTH=1>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>Address 1</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="address1" VALUE="#address1#" SIZE=30 MAXLENGTH=30>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>Address 2</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="address2" VALUE="#address2#" SIZE=30 MAXLENGTH=30>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>City,St,Zip</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="city" VALUE="#city#" SIZE=13 MAXLENGTH=30>
<INPUT TYPE="TEXT" NAME="state" VALUE="#state#" SIZE=2 MAXLENGTH=2>
<INPUT TYPE="TEXT" NAME="zip" VALUE="#zip#" SIZE=10 MAXLENGTH=10>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>Email</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="email" VALUE="#email#" SIZE=30 MAXLENGTH=30>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>Phone</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="phone" VALUE="#phone#" SIZE=12 MAXLENGTH=12>
</TD>
</TR>
<TR>
<TD ALIGN=RIGHT><STRONG>Fax</STRONG></TD>
<TD>
<INPUT TYPE="TEXT" NAME="fax" VALUE="#fax#" SIZE=12 MAXLENGTH=12>
</TD>
</TR>
</TABLE>
<INPUT TYPE="submit" NAME="action" VALUE="Save & Continue">
<INPUT TYPE="submit" NAME="action" VALUE="Save & Exit">
<INPUT TYPE="submit" NAME="action" VALUE="Cancel">
</CFOUTPUT>
</BODY>
</HTML>
In developing a data entry interface, novice CF programmers frequently create separate template files for viewing, editing, and creating a record. You can combine these three functions into a single template file by using a technique involving an empty record in your data table, called a "sentinel" and carrying a known primary key.
Listing 20.19 uses a sentinel record to serve the dual purpose of both inserting and updating information in the customer table, depending on the customerid value.
<CFIF #customerid# is 1>
<CFTRANSACTION>
<CFINSERT DATASOURCE="a2zdata" TABLENAME="CUSTOMERS" FORMFIELDS="firstname,lastname,middleinit,address1,address2,city,state,zip, ¬email,phone,fax,customersince,company">
<CFQUERY NAME="Getid" DATASOURCE="a2zdata">
SELECT max(customerid) as custID FROM customers
</CFQUERY>
<CFSET #customerid# = #getid.custID#>
</CFTRANSACTION>
<CFELSE>
<CFUPDATE DATASOURCE="a2zdata" TABLENAME="CUSTOMERS"
FORMFIELDS="firstname,lastname,middleinit,address1,address2,city,state, ¬zip,email,phone,fax,customersince,customerid,company">
</CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
function loadcustomer() {
top.customer.location.href="advforms/sampleapp/loadcustomer.cfm?customerid= ¬#customerid#"
}
</SCRIPT>
</CFOUTPUT>
<BODY ONLOAD=loadcustomer()>
<H2>Customer Record Saved</H2>
</BODY>
</HTML>
Designing an order entry application system presents a number of challenges. The user must be able to search, select, or create a customer record, view all the information about that customer, and create an order that involves a one-to-many relationship between shipping information and line items. Furthermore, there should be immediate feedback to the user that he or she is properly keying in the correct book ID numbers for the order. Data entry must occur rapidly. You don't want to keep customers waiting in line! You also have business rules to consider. After an invoice has been "closed," it cannot be edited. Optimally, you would like all this information presented on-screen at the same time with minimal scrolling and navigating between pages. Yikes!
Believe it or not, if you understand the concepts presented in this chapter, you are more than ready to take on this task!
Program flow in the application is deceptively simple. The user selects a customer, creates an order, and then saves the order. This would be straightforward if it were not for the one-to-many relation of order-to-order items and JavaScript's limitation of being unable to add new form elements without initiating a page reload. Consider that, in our interface, whenever a new line item is added, the order fields and any preexisting line items must be saved before a reload of the order form can occur. Not doing so leads to the discarding of any editing changes made to the order whenever the user presses the Add New Line Item button or Delete line item buttons. To help you better understand the program flow, we have provided the flow chart, as shown in Figure 20.14. Please review it as you run the application.
Figure 20.14 Flow chart for the order entry application.
The order entry system makes extensive use of frames to provide an intuitive user interface and show as much related data on-screen as possible. Table 20.1 outlines the frames used in the application and their purpose.
| Frame Name | Purpose |
| Menubar | Displays your A2Z Books banner. Purely cosmetic. |
| Customer | Displays customer information. Contains context-sensitive button bar for create/edit/search customer. |
| Invoice | This frame is automatically updated whenever a new customer is loaded. It allows the user to select an invoice to edit or display. |
| LineItems | A status window, used as a target for our recursive order save function. |
| Display | The main data input frame. |
The order.cfm template, Listing 20.20, is the real meat and potatoes of the application. It uses the sentinel technique, described under the heading "Applying Advanced Cold Fusion Techniques" in this chapter, to perform both editing and viewing functions. Of particular interest are the techniques employed to perform data validation on line items. When a user inputs a book ID into a line item, a JavaScript function executes a Cold Fusion template that performs a lookup in another frame. If the book ID is found in the inventory table, the line item description is automatically filled in, thus providing immediate feedback to the user. If it is not found, the message *Error appears in the description. When the user attempts to save the order, the description field for all line items is checked and if any contain an error message, the form is not submitted and an alert is generated.
NOTE: Many of the JavaScript functions refer to the form [formid + 1] when referencing line items. This is due to the fact that the first line item (#getlineitems.currentrow# = 1) created on the form is referenced as form[2] because two forms immediately precede it. Form[0] contains order-specific information and Form[1] is a hidden form used for bookid lookup.
){kind=link}
<!--Load all shipping methods-->
<CFQUERY NAME="shippingmethod" DATASOURCE="a2zdata">
SELECT * FROM shippingmethod
</CFQUERY>
<!--Get Customer Information-->
<CFQUERY NAME="getcustomer" DATASOURCE="a2zdata">
SELECT * FROM customers where customerid=#customerid#
</CFQUERY>
<!--If Orderid = 1, create new invoice-->
<CFIF #orderid# is 1>
<CFSET #newinvoice# = "Yes">
<CFTRANSACTION>
<CFQUERY NAME="createneworder" DATASOURCE="a2zdata" >
INSERT INTO orders
(
customerid,shipto,shipcompany,shipaddress1,shipaddress2,
shipcity,shipstate,shipzip
)
VALUES
(
#customerid#,'#getcustomer.firstname# #getcustomer.lastname#',
`#getcustomer.company#','#getcustomer.address1#',
`#getcustomer.address2#','#getcustomer.city#',
`#getcustomer.state#','#getcustomer.zip#'
)
</CFQUERY>
<CFQUERY NAME="getid" DATASOURCE="a2zdata">
SELECT max(orderid) as neworderid FROM orders
</CFQUERY>
<CFSET #orderid# = #getid.neworderid#>
</CFTRANSACTION>
<CFELSE>
<CFSET #newinvoice# = "No">
</CFIF>
<!--Load Order Header Information-->
<CFQUERY NAME="getorder" DATASOURCE="a2zdata">
SELECT *
FROM orders LEFT JOIN shippingmethod
ON orders.shipmethodid=shippingmethod.shipmethodid
WHERE orderid=#orderid#
</CFQUERY>
<!--Load line items for order-->
<CFQUERY NAME="getlineitems" DATASOURCE="a2zdata">
SELECT *,saleprice*quantity as total
FROM orderitems LEFT JOIN inventory
ON orderitems.bookid=inventory.bookid
where orderid=#orderid#
</CFQUERY>
<!--Set tax rate-->
<CFIF #getorder.taxable# is 1>
<CFSET #taxrate# = 0.045>
<CFELSE>
<CFSET #taxrate# = 0.00>
</CFIF>
<!--Calculate invoice total and tax total-->
<CFQUERY NAME="calc" DATASOURCE="a2zdata">
SELECT sum(quantity*saleprice) as total,
sum(quantity*saleprice*#taxrate#) as taxtotal
FROM orderitems
where orderid=#orderid#
</CFQUERY>
<!--Import common header information-->
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<!--Import our function for window status help-->
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/javafunc.CFM">
<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
function refreshinvoicewindow() {
top.invoice.location.href="que/advforms/sampleapp/invoicebrowse.cfm?customerid=#customerid#"
}
/* Total Line Item Row, then recalculate invoice totals */
function calctotal(formid){
var n
formid=formid+1
document.forms[formid].total.value=document.forms[formid].quantity.value * document.forms[formid].saleprice.value
n=CalcInvoiceTotal()
return true
}
/* Calculate totals for entire invoice */
function CalcInvoiceTotal(){
var txtotal=0
var ttotal=0
for (var n=1; n<=#getlineitems.recordcount#; n++) {
if (document.forms[0].taxable[0].selected) {
txtotal=txtotal+ eval(document.forms[n+1].total.value) * .045
}
ttotal=ttotal + eval(document.forms[n+1].total.value)
}
document.submitform.taxtotal.value="$"+txtotal
document.submitform.total.value="$"+ttotal
return true
}
/* Validate bookid field and return book description */
function validbook(formid){
formid=formid+1
document.forms[1].bookid.value=document.forms[formid].bookid.value
document.forms[1].formid.value=formid
document.forms[1].submit()
return true
}
/* Add a new line item to the order */
function addlineitem(){
var saveit=checklineitems()
if (saveit==1) {
top.lineitems.location.href="advforms/sampleapp/additem.cfm?orderid= ¬#orderid#&customerid=#customerid#"
}
}
/* Check line items and make sure they contain valid book id numbers */
function checklineitems(){
var saveit=1
for (var n=1; n<=#getlineitems.recordcount#; n++) {
if (document.forms[n+1].title.value=="*Error") {
saveit=0
alert("Cannot save order - error in line item "+n)
}
}
return saveit
}
/* Verify line items, then launch order save process */
function saveorder(closevalue){
var saveit=1
saveit=checklineitems()
if (saveit==1) {
document.forms[0].orderopen.value=closevalue
document.forms[0].submit()
}
if (saveit==0) { return false }
else { return true }
}
</SCRIPT>
</CFOUTPUT>
<!--If we're editing the invoice, we'd better update the list of -->
<!--all invoices for the customer.-->
<CFIF #parameterexists(readonly)# is "No">
<BODY ONLOAD=refreshinvoicewindow()>
</CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/sampleapp/a2zaddress.cfm">
<BR><BR><BR>
<!--**********Edit/Display fields contained in the ORDER table***********-->
<TABLE CELLPADDING=0 CELLSPACING=0 BORDER=0 WIDTH=100%>
<TR>
<TD VALIGN="TOP">
<TABLE>
<TR>
<<TD VALIGN="TOP">
<<STRONG><FONT SIZE="-3">Bill to</FONT></STRONG>
<<HR WIDTH=200 SIZE=2>
<<CFOUTPUT>
<#getcustomer.firstname# #getcustomer.middleinit#
#getcustomer.lastname#<BR>
<#getcustomer.company#<BR>
<#getcustomer.address1#<BR>
<CFIF #getcustomer.address2# is not "">
<#getcustomer.address2#<BR>
<</CFIF>
<#getcustomer.city# #getcustomer.state# #getcustomer.zip#<BR>
<ph: #getcustomer.phone# fax: #getcustomer.fax#<BR>
</CFOUTPUT>
<HR WIDTH=200 SIZE=2>
</TD>
</TR>
</TABLE>
</TD>
<TD ALIGN="RIGHT" VALIGN="TOP">
<STRONG><FONT SIZE="-3">Ship to</FONT></STRONG>
<HR WIDTH=200 SIZE=2 ALIGN="RIGHT">
<CFOUTPUT>
<CFIF #parameterexists(readonly)# is "No" and #getorder.orderopen# is 0>
<!--The template updateorder.cfm is used to perform a CFUPDATE -->
<!--on fields in the Order table.-->
<!--This is form id 0-->
<FORM ACTION="advforms/sampleapp/updateorder.cfm"
METHOD="POST" TARGET="lineitems">
<INPUT TYPE="hidden" NAME="orderopen" VALUE=#getorder.orderopen#>
<INPUT TYPE="hidden" NAME="orderid" VALUE=#getorder.orderid#>
<INPUT TYPE="hidden" NAME="customerid" VALUE=#getorder.customerid#>
<INPUT TYPE="hidden" NAME="lineitems" VALUE=#getlineitems.record ¬count#>
<TABLE>
<TR>
<TD>To:</TD>
<TD>
<INPUT TYPE="TEXT" NAME="shipto" SIZE="30" VALUE="#getorder.shipto#"
OnFocus="helpme(`Enter shipping contact person')"></TD>
</TR>
<TR>
<TD>Co:</TD>
<TD>
<INPUT TYPE="TEXT" NAME="shipcompany" SIZE="30" VALUE="#getorder.shipcompany#"
OnFocus="helpme(`Enter the name of the company to deliver ¬the shipment to')"></TD>
</TR>
<TR>
<TD>Addr:</TD>
<TD>
<INPUT TYPE="TEXT" NAME="shipaddress1" SIZE=30 VALUE="#getorder.shipaddress1#"
OnFocus="helpme(`Enter the destination address')"></TD>
</TR>
<TR>
<TD>Addr:</TD>
<TD>
<INPUT TYPE="TEXT" NAME="shipaddress2" SIZE=30 VALUE="#getorder.shipaddress2#"
OnFocus="helpme(`Enter the destination address')"></TD>
</TR>
<TR>
<TD>C,S,Z:</TD>
<TD>
<INPUT TYPE="TEXT" NAME="shipcity" SIZE=9 VALUE="#getorder. ¬shipcity#"
OnFocus="helpme(`Enter the destination city')">
<INPUT TYPE="TEXT" NAME="shipstate" SIZE=2 VALUE="#getorder. ¬shipstate#"
OnFocus="helpme(`Enter the destination state')">
<INPUT TYPE="TEXT" NAME="shipzip" SIZE=10 VALUE="#getorder. ¬shipzip#"
OnFocus="helpme(`Enter the destination zipcode')">
</TD>
</TR>
</TABLE>
<CFELSE>
<TABLE CELLPADDING=0 BORDER=0>
<TR>
<TD>
#getorder.shipto#<BR>
#getorder.shipcompany#<BR>
#getorder.shipaddress1#<BR>
#getorder.shipaddress2#<BR>
#getorder.shipcity# #getorder.shipstate# #getorder.shipzip#<BR><BR>
</TD>
</TR>
</TABLE>
</CFIF>
</CFOUTPUT>
<HR WIDTH=200 SIZE=2 ALIGN="RIGHT">
</TD>
</TR>
</TABLE>
<BR><BR>
<CFIF #parameterexists(readonly)# is "No" and #getorder.orderopen# is 0>
<CFOUTPUT>
<TABLE WIDTH=100% BORDER=1>
<TR>
<TD>
<STRONG>P.O.##</STRONG><INPUT TYPE="TEXT" NAME="purchaseorder" ¬SIZE=10
VALUE="#getorder.purchaseorder#" OnFocus="helpme(`Enter a purchase order number (if required)')">
</TD>
<TD ALIGN="CENTER">
<STRONG>Taxable?</STRONG>
<SELECT NAME="taxable" OnChange="CalcInvoiceTotal()">
<CFIF #getorder.taxable# is 1>
<OPTION VALUE=1 SELECTED>Yes
<OPTION VALUE=0>No
<CFELSE>
<OPTION VALUE=1>Yes
<OPTION VALUE=0 SELECTED>No
</CFIF>
</SELECT>
</TD>
</CFOUTPUT>
<!--The technique presented here with select boxes is outlined -->
<!--earlier in this chapter.-->
<TD ALIGN="RIGHT">
<STRONG>Ship By</STRONG>
<CFOUTPUT>
<SELECT NAME="shipmethodid">
<OPTION VALUE=0>
<OPTION VALUE=0>---------------
</CFOUTPUT>
<CFOUTPUT query="shippingmethod">
<CFIF #getorder.shipmethodid# is #shippingmethod.shipmethodid#>
<OPTION VALUE=#shipmethodid# SELECTED>#shippingmethod#
<CFELSE>
<OPTION VALUE=#shipmethodid#>#shippingmethod#
</CFIF>
</CFOUTPUT>
</SELECT>
</TD>
</TR>
</TABLE>
<BR>
<BR>
<CFOUTPUT>
<INPUT TYPE="button" NAME="Newlineitem" Value="Add New Line Item" OnClick="addlineitem()">
</CFOUTPUT>
<CENTER>
</FORM>
<CFELSE>
<CFOUTPUT>
<TABLE WIDTH=100% BORDER=1>
<TR>
<TD>
<STRONG>Purchase Order:</STRONG>
#getorder.purchaseorder#
</TD>
<TD>
<STRONG>Taxable?</STRONG>
<CFIF #getorder.taxable# is 1>Y<CFELSE>N</CFIF>
</TD>
<TD>
<STRONG>Shipping Method:</STRONG>
#getorder.shippingmethod#
</TD>
</TR>
</TABLE><BR><BR><BR>
</CFOUTPUT>
</CFIF>
<CFIF #parameterexists(readonly)# is "No" and #getorder.orderopen# is 0>
<!-- ****************Create a hidden form to use for validating bookid's*********-->
<!--****************** And autofilling the book description field ************-->
<!--***(This technique detailed in Add. Form Data Validation Techniques)*********-->
<!--************************************This is form id 1************************-->
<FORM ACTION="/cgi/dbml.exe?template=/que/advforms/sampleapp/validatebook.cfm"
METHOD="POST" target="lineitems">
<INPUT TYPE="hidden" NAME="bookid">
<INPUT TYPE="hidden" NAME="formid">
<CFOUTPUT>
<INPUT TYPE="hidden" NAME="orderid" VALUE=#orderid#>
<INPUT TYPE="hidden" NAME="customerid" VALUE=#customerid#>
</CFOUTPUT>
</FORM>
</CFIF>
<!--******************Edit/Display fields contained in the ORDERITEMS table*************-->
<CFIF #parameterexists(readonly)# is "Yes" or #getorder.orderopen# is 1>
<TABLE WIDTH=100%><TR><TD>
<TABLE BORDER=1 WIDTH=100%>
<TH><FONT SIZE="-1">Qty</FONT></TH>
<TH><FONT SIZE="-1">Book#</FONT></TH>
<TH><FONT SIZE="-1">Desc</FONT></TH>
<TH><FONT SIZE="-1">UnitPr</FONT></TH>
<TH><FONT SIZE="-1">Sale Pr</FONT></TH>
<TH><FONT SIZE="-1">Total</FONT></TH>
<CFOUTPUT query="getlineitems">
<CFIF quantity greater than 0>
<TR>
<TD ALIGN="RIGHT"><FONT SIZE="-1">#numberformat(quantity,'(____.__)')#</FONT></TD>
<TD ALIGN="RIGHT"><FONT SIZE="-1">#bookid#</FONT></TD>
<TD><FONT SIZE="-1">#title#</FONT></TD>
<TD ALIGN="RIGHT"><FONT SIZE="-1">#numberformat(unitprice,'($____.__)')#</FONT></TD>
<TD ALIGN="RIGHT"><FONT SIZE="-1">#numberformat(saleprice,'($____.__)')#</FONT></TD>
<TD ALIGN="RIGHT"><FONT SIZE="-1">#numberformat(total,'($____.__)')#</FONT></TD>
</TR>
</CFIF>
</CFOUTPUT>
</TABLE>
</TD></TR><TR><TD ALIGN="RIGHT">
<TABLE BORDER=1>
<CFOUTPUT>
<TR><TD ALIGN="RIGHT"><STRONG>Tax</STRONG></TD>
<TDALIGN="RIGHT">#numberformat(calc.taxtotal,'($______.__)')#</ ¬TD></TR>
<TR><TD ALIGN="RIGHT"><STRONG>Total</STRONG></TD>
<TD ALIGN="RIGHT">#numberformat(calc.total,'($______.__)')#</ ¬TD></TR>
</CFOUTPUT>
</TABLE>
</TD></TR></TABLE>
<CFELSE>
<TABLE>
<TR>
<TD>
<!--Note: Each line item is a separate form (form id 2 through (getlineitems.recordcount + 1))-->
<!--You can write a function using Javascript and Cold Fusion to ¬recursively submit each form!-->
<TABLE BORDER=1 WIDTH=100%>
<TH>Qty</TH><TH>Book#</TH><TH>Desc</TH><TH>UnitPr</TH><TH>Sale Pr</TH><TH>Total</TH><TH>Del</TH>
<CFOUTPUT query="getlineitems">
<FORM ACTION="/cgi/dbml.exe?template=/que/advforms/sampleapp/savelineitems.cfm" METHOD="POST" target="lineitems" >
<INPUT TYPE="hidden" NAME="orderline" VALUE=#orderline#>
<INPUT TYPE="hidden" NAME="orderid" VALUE=#orderid#>
<INPUT TYPE="hidden" NAME="customerid" VALUE="#customerid#">
<INPUT TYPE="hidden" NAME="lineitems" VALUE=#getlineitems.recordcount#>
<INPUT TYPE="hidden" NAME="lineitemrow" VALUE=#getlineitems.currentrow#>
<TR>
<TD><INPUT TYPE="text" NAME="quantity" VALUE="#quantity#" SIZE=3
OnFocus="helpme(`Enter the quantity of the book to be purchased')" OnChange="calctotal(#currentrow#)"></TD>
<TD><INPUT TYPE="text" NAME="bookid" VALUE="#bookid#" SIZE=3
OnFocus="helpme(`Enter the ID Number of the book to be purchased')" OnChange="validbook(#currentrow#)"></TD>
<TD><INPUT TYPE="text" NAME="title" value="#title#" SIZE=15
OnFocus="helpme(`This is a calculated field')" OnChange="validbook(#currentrow#)"></TD>
<TD><INPUT TYPE="text" NAME="unitprice" VALUE="#unitprice#"
OnFocus="helpme(`Enter the unitprice of the book to be purchased')" SIZE=8></TD>
<TD><INPUT TYPE="text" NAME="saleprice" VALUE="#saleprice#"
OnFocus="helpme(`Enter the sale price of the book to be purchased')" SIZE=8
OnChange="calctotal(#currentrow#)"></TD>
<CFIF #total# is not "">
<TD><INPUT TYPE="text" NAME="total" VALUE="#total#" SIZE=8 OnFocus="helpme(`This is a calculated field')"></TD>
<CFELSE>
<TD><INPUT TYPE="text" NAME="total" VALUE="0" SIZE=8
OnFocus="helpme(`This is a calculated field')"></TD>
</CFIF>
<TD><INPUT TYPE="SUBMIT" NAME="ACTION" VALUE="Del" OnClick="saveorder(0)"></TD>
</TR>
</FORM>
</CFOUTPUT>
</TABLE>
</CENTER>
<BR>
</TD></TR>
<TR><TD ALIGN="RIGHT">
<CFOUTPUT>
<FORM NAME="submitform">
<P ALIGN=RIGHT>
<TABLE BORDER=1>
<TR>
<TD VALIGN="RIGHT">
<STRONG>Tax</STRONG>
</TD>
<TD>
<INPUT TYPE="text" NAME="taxtotal" value="#calc.taxtotal#" SIZE=8
OnFocus="helpme(`This is a calculated field')">
</TD>
</TR>
<TR>
<TD VALIGN="RIGHT">
<STRONG>Total</STRONG>
</TD>
<TD>
<INPUT TYPE="text" NAME="total" value="#calc.total#" SIZE=8
OnFocus="helpme(`This is a calculated field')">
</TD>
</TR>
</TABLE>
</TD></TR></TABLE>
<BR>
<CENTER>
<!--Note: These are Javascript buttons, not form submit buttons!-->
<INPUT TYPE="button" NAME="updateinvoice" VALUE="Update Invoice" OnClick="saveorder(0)">
<INPUT TYPE="button" NAME="updateinvoice" VALUE="Close Invoice" OnClick="saveorder(1)">
</FORM>
</CFOUTPUT>
</CENTER>
</CFIF>
</BODY>
</HTML>
The code for updating the order is contained in Listing 20.21. After the line items have been validated, the order is allowed to be saved. A <CFUPDATE> on the order table is performed. After the update is complete, the template checks for the existence of any ordered items. If any exist, this template loads another template to start the recursive process of saving line items.
<CFIF #orderid# is not 1>
<CFUPDATE DATASOURCE="a2zdata" TABLENAME="orders"
FORMFIELDS="customerid,purchaseorder,shipto,shipcompany,shipaddress1,shipaddress2, ¬shipcity,shipstate,shipzip,shipmethodid,shippingcharge,taxableorderopen"></ ¬CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
function savelineitems() {
top.display.document.forms[2].submit()
}
function reloadorder(){
top.display.location.href="advforms/sampleapp/¬order.cfm?orderid=#orderid#&customerid=#customerid#"
}
</SCRIPT>
</CFOUTPUT>
<CFIF #lineitems# greater than 0>
<BODY ONLOAD="savelineitems()">
<STRONG>Last Action: </STRONG>Order Header Information Saved
<CFELSE>
<BODY ONLOAD="reloadorder()">
<STRONG>Last Action: </STRONG>Order Saved
</CFIF>
</BODY>
</HTML>
The most difficult part of designing any CF application involves saving one-to-many relationships. Listing 20.22 uses CF and JavaScript to recursively execute form submissions located in separate frames. It also acts as a mechanism for deleting line items. If a user presses the Delete button, named action, located to the right of the line item, the button is submitted along with the rest of the fields in the form.
<CFIF #parameterexists(action)# is "Yes">
<CFQUERY NAME="deletelineitem" DATASOURCE="a2zdata" >
DELETE from orderitems where orderid=#orderid# and ¬orderline=#orderline#
</CFQUERY>
<CFELSE>
<CFUPDATE DATASOURCE="a2zdata" TABLENAME="orderitems"
FORMFIELDS="orderid,orderline,bookid,quantity,unitprice,saleprice">
</CFIF>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<CFSET #nextform# = #lineitemrow# + 2>
<CFOUTPUT>
<SCRIPT LANGUAGE="JavaScript">
function recycle(){
top.display.document.forms[#nextform#].submit()
}
function refreshorder(){
top.display.location.href="advforms/sampleapp/order.cfm?orderid=#orderid#&customerid=#customerid#"
}
function saveall(){
top.display.document.forms[0].submit()
}
</SCRIPT>
</CFOUTPUT>
<CFIF #parameterexists(action)# is "No">
<CFIF #lineitems# greater than #lineitemrow#>
<BODY ONLOAD="recycle()">
<CFIF #parameterexists(action)# is "Yes">
<STRONG>Last Action:</STRONG>Line Item Deleted
<CFELSE>
<STRONG>Last Action:</STRONG>Line Item Saved
</CFIF>
<CFELSE>
<BODY ONLOAD="refreshorder()">
<STRONG>Save Complete</STRONG>
</CFIF>
<CFELSE>
<BODY ONLOAD="saveall()">
</CFIF>
</BODY>
</HTML>
As mentioned previously, JavaScript cannot create additional form elements after a page has completed loading. Because our interface puts all line items and order information on the same form, this leads to a potential problem. If the user edits the shipto field and then adds a line item, we must be sure to save the changes made to the shipto field and insert a new line item into the orderentry table, before reloading the order.cfm file. Listing 20.23 addresses these concerns.
<CFTRANSACTION>
<CFQUERY NAME="getlineno" DATASOURCE="a2zdata">
SELECT max(orderline) as nextorderline
FROM orderitems
WHERE orderitems.orderid=#orderid#
</CFQUERY>
<CFIF #getlineno.nextorderline# is "">
<CFSET #nextline# = 1>
<CFELSE>
<CFSET #nextline# = #getlineno.nextorderline# + 1>
</CFIF>
<CFQUERY NAME="insertlinenumber" DATASOURCE="a2zdata">
INSERT INTO orderitems (orderid,orderline)
values (#orderid#,#nextline#)
</CFQUERY>
</CFTRANSACTION>
<CFINCLUDE TEMPLATE="/que/advforms/common/header.cfm">
<SCRIPT LANGUAGE="JavaScript">
function saveorder(){
top.display.document.forms[0].submit()
}
</SCRIPT>
<BODY ONLOAD="saveorder()">
<STRONG>Last Action: Added New Line Item</STRONG>
</BODY>
</HTML>
While the order entry system provides a robust framework for working with one-to-many relationships and multiple frames, there are some issues left unresolved. For instance, if a user is editing changes on the order form and then decides to select a different customer or view a different invoice without first saving the order, those changes will be lost. The techniques to handle this occurrence are presented in this chapter and are left as an exercise for you, the reader. Good Luck!
Using the topics discussed in this chapter, you should be able to design and implement web-based systems that function almost like a native windows database app. For adding even more functionality, including modifying your code to be multiuser ready, generating e-mail based notifications, and storing user's preferences, read the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.