The Cold Fusion Web Database Construction Kit
Most Cold Fusion development involves creating one of two types of applications--data entry and data display. Although every application is different (and so are the interfaces and user interaction techniques that you will employ), you will often find that you are spending much of your development time creating the same basic data entry and data display screens.
To jump start the development process, Allaire created the Cold Fusion Web Application Wizards. These two wizards interactively prompt the user for details about the application and then create complete working Cold Fusion templates. These templates can then be modified and built upon.
The wizard-generated applications are also useful learning tools because the created templates demonstrate many useful techniques for data collection, validation, and formatting.
Wizards have been appearing in many applications over the past few years. Application installation programs use them to prompt for installation and setup options, word processors use them to simplify complex formatting operations, database applications use them to create tables, and spreadsheets use them to perform different calculations.
Wizards are popular because they simplify complex or tedious tasks without imposing a lengthy learning curve upon the user. Wizards break down an operation into multiple steps and at each step the user is prompted to provide information or select specific options. The Wizard then prompts the user for more information, based on the provided responses, until enough information is collected to perform an operation.
Although every Wizard in every application is different, they all have similar interfaces and all work in much the same way. Almost all Wizards have the same navigation buttons as listed in Table 13.1. Once you have learned how to use one Wizard, you've learned them all.
| Button | Description |
| Cancel | Cancel the Wizard and return to the title screen. |
| < Back | Return to the previous Wizard screen. |
| Next > | Advance to the next Wizard screen. |
| Finish | Advance to the last Wizard screen, accepting default values and options in all screens passed. |
Cold Fusion comes with two Web Application Wizards; one to create data entry screens and the other to create data drill-down interfaces.
NOTE: The Wizard-generated Cold Fusion templates use the server module style URL syntax instead of the CGI syntax. This means that the Cold Fusion Web Application Wizard can only be used if you are using a server that supports ISAPI, NSAPI, or WSAPI.
See Chapter 2, "Introduction to Cold Fusion," and Chapter 29, "Server Modules," for more information on the Cold Fusion server modules and URL syntax.
These Wizards generate complete working applications and support an extensive array of features and options. The generated application, however, seldom does exactly what your application design document requires. Rather, the Wizards create a foundation of solid, reliable, and commented code on which you may build your own application.
Furthermore, the Wizards are valuable learning tools. The templates they generate demonstrate different techniques for data entry, validation, and output display and formatting.
NOTE: The Web Application Wizards do not create database tables for you. To generate applications with the Wizards, you must have already created any database tables required by your application.
Select the "Web Application Wizards" from the Cold Fusion program group to display the Wizard title screen as shown in Figure 13.1.
The Wizard title screen has two buttons, one to run the Data Entry Application Wizard and the other to run the Data Drill-Down Application Wizard. Click on either of these buttons to start the appropriate Wizard.
Figure 13.1 The Cold Fusion Web Application Wizard is used to generate basic Cold Fusion templates.
TIP: If Cold Fusion is installed on a network file server, the Cold Fusion Application Wizards can be run on any workstation on that network. The only prerequisites, however, are that the ODBC data source must be setup on the workstation, and that data source should preferably be named exactly the same as the data source on the web server computer itself. This ensures that the templates generated on your workstation will work correctly when executed on the web server.
The Data Entry Application Wizard, as its name implies, helps you create data entry screens with supporting Cold Fusion code. This Wizard performs the following functions:
Back in Chapter 5, "Designing an Application," and Chapter 6, "Database Fundamentals," you designed and created a mechanism for tracking employees' vacation dates. We'll now use the Data Entry Application Wizard to create the data entry form to add vacations to the table.
There are nine steps involved in creating a data entry form with the Data Entry Application Wizard.
Step 1: Naming the Application Click the Data Entry Application Wizard button
to start the Wizard. A dialog box presents like the one shown in Figure 13.2. This
dialog box explains what the Wizard does and prompts you to name your application.
The name you specify in the Application Title is used as the HTML TITLE in the generated
page.
Figure 13.2 The first Data Entry Application Wizard screen prompts you for the application name.
Enter Book A Vacation in the Application Title field and click the Next > button to advance to the next Wizard screen.
Step 2: Specifying the Output File Location Next you need to tell the Wizard
where to place the templates that it will generate. The Wizard displays a default
destination as shown in Figure 13.3. To specify a different output directory, click
the Browse button to display the Select Directory dialog box as shown in Figure 13.4.
Once you enter or select the desired directory, click OK.
Figure 13.3 When generating templates with a Web Application Wizard, you must specify the output file location.
Figure 13.4 Templates may be stored on local or network drives.
NOTE: The output location that you specify for Cold Fusion Web Application Wizard templates must be accessible by Cold Fusion and your web server. If a template is stored in a directory that is not beneath the web server document root, and not in a Cold Fusion mapped template directory, then it will be inaccessible to Cold Fusion.
Once you are satisfied with the Destination Directory, click the Next > button.
Step 3: Selecting the ODBC Data Source The Wizard is now ready for you to
specify the ODBC data source into which collected data is to be inserted. Select
the A2Z data source as shown in Figure 13.5, and then click the Next > button.
Figure 13.5 When creating a data entry form with a Web Application Wizard, you must select an existing ODBC Data Source into which new data will be inserted.
See the section in Chapter 8, "Introduction to SQL," entitled "Creating an ODBC Data Source," for a detailed explanation of ODBC data sources and how they are used.
NOTE: Some database systems, for example Microsoft SQL Server, require that you log into the database before your operations are performed. If your database system requires that you log into it to gain access, make sure you specify a user name and password in the Security Information fields before proceeding to the next screen.
Step 4: Selecting the Database Table Now that the Wizard knows which ODBC data source you will be using, it prompts you to select the destination table. As shown in Figure 13.6, the Wizard displays a list of all the tables available via the ODBC data source that you just specified.
See the section in Chapter 6, "Database Fundamentals," entitled "Understanding Databases," for an explanation of databases, tables, and other important database related terms.
We're going to create a vacation data entry form, so select the Vacations table and click the Next > button.
Figure 13.6 To make the selection process simpler, the Web Application Wizards display a list of all available destination tables.
Step 5: Selecting the Data Entry Fields The next step is to specify which
fields are to be included in the data entry form. The dialog shown in Figure 13.7
is split into two columns.
The left column is the Available Fields list. This list contains every field in the table you just selected. The list sorts alphabetically and not in the actual order in which the columns appear in the table itself.
The right column is the Fields to Display column. Any fields that you select for inclusion in the form appear in this column. Unlike the Available Fields column, this column is not sorted alphabetically. Instead, fields are listed in the order in which they were selected. Why is this important? When the Wizard creates the final form, the fields are displayed in the order in which they appear in this column.
Figure 13.7 Data entry form fields are selected from the Available Fields list.
Between the two columns are a set of four buttons. These buttons are used to move fields back and forth between the two lists. Table 13.2 lists the buttons and their descriptions.
| Button | Description |
| [Delta] | Move the selected field from the Available Fields list to the Fields to Display list. |
| ->> | Move all fields from the Available Fields list to the Fields to Display list. |
| ® | Move the selected field from the Fields to Display list to the Available Fields list. |
| <<- | Move all fields from the Fields to Display list to the Available Fields list. |
We need all three fields to be selected but we cannot use the ->> button because the order that the field names appear in the Available Fields column is incorrect. In our data entry form, we want the vacation start data field to appear before the vacation end data field.
First select the EmployeeID field and click the button to move it to the Fields to Display column. Next select the VacationStart field and move it over and then move over the VacationEnd field.
Figure 13.8 The order of fields in the data entry form is based on the order in which the field names appear in the Fields to Display list.
TIP: Double-clicking on a field name moves it immediately to the other column.
Once you have selected the fields you need, and in the order in which you need them, click the Next > button.
Step 6: Specifying Field Properties and Options Now comes the fun part. The
next dialog, as shown in Figure 13.9, contains a spreadsheet that lists all of the
selected fields in the correct order along with any associated properties. The various
columns are explained in Table 13.3.
Figure 13.9 The Data Entry Application Wizard can be used to modify individual form field properties.
| Column | Description |
| Field Name | The table column name. |
| Caption | The text to appear as a field caption (defaults to field name). |
| Type | Field type (defaults to TextBox, but can be changed to Drop-Down Listbox, Single SelectBox, Multiple SelectBox, TextArea, CheckBoxes, Option Buttons, or Hidden Field). |
| Required | "No" if this is not a required field, "Yes" if it is (defaults to "No"). |
| Validation | Any specified field validation rules (defaults to "None"). |
To set options or properties for any field, double-click on it. This displays the Enter Field Properties dialog, as shown in Figure 13.10.
Figure 13.10 The Entry Field Properties dialog allows you to set field options and activate validation rules.
Double-click on the VacationStart field to display its properties dialog.
TIP: The first item in the Entry Field Properties dialog is the Database Field drop-down listbox. To set the properties for a field other than the one you double-clicked on, just select it from this list. This enables you to set the options for as many fields as you need without having to open and close the dialog repeatedly. When you are finished making the necessary changes to all the fields, click Done button.
The first thing we need to do is change the label. The Label text is the descriptive text that appears next to the field in the final form. The default text is the table field name itself and probably is the desired field label. We'll use the label Start Date, so type Start Date into the Label field.
The Form Field Type option is used to specify the type of this field. The default option is TextBox, a simple, plain-text field, which is exactly what we need for our date field, so we'll leave the option as is.
The Default Content is used to set the field's INPUT VALUE attribute so that the field display is pre-filled with a default value. We have no need for a default value, so leave this field blank.
Next, we need to specify the size of the field. The TextBox Size option specifies the width of the field in characters. Whatever value you provide here is used to set the field's INPUT SIZE attribute. The maximum length of data permitted in a field is set in the Maximum Length option. This value is used to set the field's INPUT MAXLENGTH attribute. For our date field, set both of these values to 10, which is large enough for users to enter dates properly but not so large that it wastes precious screen space.
See Chapter 11, "Cold Fusion Forms," and Chapter 12, "Using Forms to Add or Change Data," for detailed coverage of how HTML forms are used with Cold Fusion.
The bottom section of the dialog is used to specify data entry validation rules.
This VacationStart field is a date field, so we want to make sure that only valid date values are entered into this field. To do this, select "must be a Date" from the Validation Rule list. Now, if a user enters a non-date value, the error message in the "Error message when violated" field is displayed, and the user knows to correct the entry. You may customize the error message if you wish, or you may leave it as is.
To make sure that a date is entered and that the field is never left empty, check the "the form field cannot be left blank" checkbox. If a user now tries to submit the form with an empty VacationStart field, the message in the "Error message when left blank" field will be displayed. You also may customize this message.
See Chapter 18, "Form Data Validation," for a complete explanation of the Cold Fusion for validation rules and options.
Once you have set these options, your dialog should look like the one in Figure 13.11. Verify that the options are all set correctly and then set the properties for the VacationEnd field. All the options should be exactly the same as the VacationStart field except for the Label which should read End Date.
Figure 13.11 The options available to you in Entry Field Properties will vary based on the Form Field Type you select.
The last field we need to set is the EmployeeID field. This field value will never be set explicitly by the user. It is, instead, hidden inside the form so that it submits along with the user-supplied fields for processing. Set the EmployeeID Form Field Type to Hidden Field. All the other options can be left as is.
Once you have set all these options, your properties spreadsheet should look like the one shown in Figure 13.12. Once you have verified that the properties are all correct, click the Next > button.
Figure 13.12 The field properties spreadsheet always updates after any changes are made, and is therefore useful in verifying that all the properties were set correctly.
Step 7: Specifying Output Style, and Page Text The next Wizard screen,
shown in Figure 13.13, prompts you to select an output style. The "Data Entry
Application Wizard" supports three different output styles:
Figure 13.13 The Data Entry Application Wizard lets you select from one of three output styles.
Figure 13.14 The "Tabular form using HTML 3.0 tables" output style creates a table with each field in a separate row.
Figure 13.15 The "Preformated form" output style uses an HTML <PRE> tag to display data in a simple non-proportional font.
Figure 13.16 The "Simple unformatted form" output style creates a simple HTML form that is well suited for custom modification.
We'll use the default option, "Tabular form using HTML 3.0 tables," so make sure that option is selected.
Beneath the output style options are two text boxes. The first, entitled "Customize the head of the page:," allows you to modify the default HTML code that will be used at the top of the final generated template, before the data entry form. The second box, entitled "Customize the footer of the page:," enables you to modify the HTML code that will be used after the data entry form.
You may modify any or all of the code in these boxes. You may include any HTML tags, links to other sites or pages, and any other Cold Fusion tags.
We'll modify the page footer to include the name of the Human Resources Department that is responsible for this page. Insert this line before the default footer text:
<P><CENTER><I>A2Z Books Human Resources Department</I></CENTER>
Verify that the footer now looks like the screen shown in Figure 13.17, and then click the Next > button.
Figure 13.17 The default page header and footer can be modified to include any HTML or CFML tags.
Step 8: Specifying the Post Processing Display Before the Wizard can generate
your application, there is one more piece of information that you must provide. The
screen shown in Figure 13.18 enables you to choose the page that displays after the
user submits a record for insertion. You have two options here:
Figure 13.18 Once the user supplied data is inserted into the table you may either display a confirmation message, or redirect the user to another Web page.
We just want a simple confirmation message, so make sure you select the "Wizard- generated page" option.
Cold Fusion has the ability to transmit form results via e-mail to any valid Internet mail address. The "Data Entry Application Wizard" generates the appropriate CFMAIL code to automate this process if you provide sender and recipient e-mail addresses in the fields provided. We don't need this feature for this data entry screen, so leave the e-mail address fields blank.
See For more information about the CFMAIL tag and how it can be used to send form responses to e-mail recipients, see Chapter 21, "Interacting with E-Mail."
Click the Next > button to generate the templates.
Step 9: Generating the Templates The Wizard has now created the complete application,
and you should see a confirmation message like the one shown in Figure 13.19. The
message contains a description of the templates that were generated and what each
one does, as well as instructions on how to test the new templates.
Figure 13.19 The Wizard confirmation message contains instructions describing how to test the new application.
To test the data entry screen, point your browser at http://yourservername/a2z/entryform.cfm, and replace "yourservername" with the name or IP address of your own web server. You'll see a screen like the shown in Figure 13.20. The form has two fields, a submit button, the title text we specified, and the custom footer text.
NOTE: The Web Application Data Entry Wizard always generates two templates, one named ENTRYFORM.CFM and the other named DATAENTRY.CFM.
Figure 13.20 You can test the Wizard-generated applications with your web browser.
Before you actually add any records with this form, let's take a look at the generated template which is shown in Listing 13.1.
As you can see, the Wizard inserts comments into the template describing what each section of code does. This is a good practice, and one that we'll talk more about later on.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<!----------------------------------------------------------------------
This is the HTML page the wizard generated for the entry of your data
into the database. You can customize this page to improve the visual
impact of your application.
------------------------------------------------------------------------>
<HTML>
<HEAD><TITLE>Book A Vacation</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="entryform.gif"></CENTER>
<CENTER><H1>Book A Vacation</H1></CENTER>
<!-----------------------------------------------------------------------
It following section contains the HTML form with for fields
corresponding to the appropriate fields in your database table. You
can use HTML to customize the look and the behavior of this form.
Do not change the names of the form field as they have to match your
database fields.
------------------------------------------------------------------------>
<FORM ACTION="DataEntry.CFM" METHOD="POST">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="">
<INPUT TYPE="hidden" NAME="VacationStart_date" VALUE="The value in the field (Start Date) must be a valid date.">
<INPUT TYPE="hidden" NAME="VacationStart_required" VALUE="You cannot leave the field (Start Date) empty.">
<INPUT TYPE="hidden" NAME="VacationEnd_date" VALUE="The value in the field (End Date) must be a valid date.">
<INPUT TYPE="hidden" NAME="VacationEnd_required" VALUE="You cannot leave the field (End Date) empty.">
<TABLE>
<TR><TD ALIGN="right">Start Date</TD><TD><INPUT TYPE="text" NAME="VacationStart" SIZE="10" MAXLENGTH="10"></TD></TR>
<TR><TD ALIGN="right">End Date</TD><TD><INPUT TYPE="text" NAME="VacationEnd" SIZE="10" MAXLENGTH="10"></TD></TR>
</TABLE>
<CENTER><INPUT TYPE="submit" VALUE="Submit"></CENTER>
</FORM>
<P><CENTER><I>A2Z Books Human Resources Department</I></CENTER>
</BODY>
</HTML>
See Chapter 12, "Using Forms to Add or Change Data" for complete coverage of Cold Fusion forms and how they are used for data entry.
You can specify an employee's vacation dates and store them into the Vacations table (as shown in the data entry form shown in Listing 13.1), but there is no way to specify which employee is booking the vacation.
In Chapter 13, "Using Forms to Add or Change Data," we discussed the use of hidden fields within a form and how they can be used to pass fixed values to the destination template. The code generated by the Wizard has a hidden field in the form, but it is not set to any value, as shown below.
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="">
The first change that we have to make to this template is to require that an EmployeeID is passed to it, and the value is then passed to the hidden EmployeeID form field. The updated template is shown in Listing 13.2. As you can see, we added a test at the beginning of the template to ensure that an EmployeeID was passed.
We also modified the hidden field so that #EmployeeID# was passed to the VALUE attribute. Doing so also requires that the hidden field be enclosed within a <CFOUTPUT> block, so the field name is replaced with the actual passed value.
See the section in Chapter 10, "Cold Fusion Basics," entitled, "Understanding Cold Fusion T emplates," for a detailed explanation of the Cold Fusion <CFOUTPUT> tag.
The final two changes we made were changing the default text of the submit button to "Add Vacation Dates," and renaming the generated templates to our own names.
Why would you want to rename the templates? The Cold Fusion Wizards always generate templates with exacty the same file names, so if you need to run the Wizard again to create a second application, those new templates overwrite the first ones you created. It is a good idea to always rename Wizard-generated templates--of course you'll also have to change any links that reference the templates. We renamed entryform.cfm to addvac1.cfm, and dataentry.cfm to addvac2.cfm, and therefore had to modify the FORM ACTION attribute as follows:
<FORM ACTION="addvac2.CFM" METHOD="POST">
<CFIF #ParameterExists(EmployeeID)# IS "No">
Error! No EmployeeID was specified!
<CFABORT>
</CFIF>
<!----------------------------------------------------------------------
This is the HTML page the wizard generated for the entry of your data
into the database. You can customize this page to improve the visual
impact of your application.
---------------------------------------------------------------------->
<HTML>
<HEAD><TITLE>Book A Vacation</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="entryform.gif"></CENTER>
<CENTER><H1>Book A Vacation</H1></CENTER>
<!----------------------------------------------------------------------
It following section contains the HTML form with for fields
corresponding to the appropriate fields in your database table. You
can use HTML to customize the look and the behavior of this form.
Do not change the names of the form field as they have to match your
database fields.
---------------------------------------------------------------------->
<FORM ACTION="addvac2.CFM" METHOD="POST">
<CFOUTPUT>
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="#EmployeeID#">
</CFOUTPUT>
<INPUT TYPE="hidden" NAME="VacationStart_date" VALUE="The value in the field (Start Date) must be a valid date.">
<INPUT TYPE="hidden" NAME="VacationStart_required" VALUE="You cannot leave the field (Start Date) empty.">
<INPUT TYPE="hidden" NAME="VacationEnd_date" VALUE="The value in the field (End Date) must be a valid date.">
<INPUT TYPE="hidden" NAME="VacationEnd_required" VALUE="You cannot leave the field (End Date) empty.">
<TABLE>
<TR><TD ALIGN="right">Start Date</TD><TD><INPUT TYPE="text" NAME="VacationStart" SIZE="10" MAXLENGTH="10"></TD></TR>
<TR><TD ALIGN="right">End Date</TD><TD><INPUT TYPE="text" NAME="VacationEnd" SIZE="10" MAXLENGTH="10"></TD></TR>
</TABLE>
<CENTER><INPUT TYPE="submit" VALUE="Add Vacation Dates"></CENTER>
</FORM>
<P><CENTER><I>A2Z Books Human Resources Department</I></CENTER>
</BODY>
</HTML>
Now we're ready to test the templates. Point your browser at http://yourservername/a2z/addvac1.cfm?EmployeeID=7, (again replacing "yourservername" with the name or IP address of your own web server) to display the updated web page as shown in Figure 13.21. Enter 02/01/97 in the Start Date field, and 02/08/97 in the End Date field, and then click the Add Vacation Dates button. You should receive a confirmation screen like the one shown in Figure 13.22.
Figure 13.21 Each time you make changes to a template, you should reload it in your browser to ensure that nothing was inadvertently broken while the modifications were being made.
Figure 13.22 The default Wizard-generated destination template displays a simple insertion notification message.
Try adding a few more vacation dates, but this time leave fields blank, or enter invalid dates. The validation rules we selected in Step 6 should catch and prevent any invalid data entry.
You have successfully used the Cold Fusion Data Entry Application Wizard to create a data entry application, and then updated the generated code to meet your own requirements. This Wizard is a useful tool and one that will help shorten the development cycle of any future Cold Fusion projects.
The Data Drill-Down Application Wizard is used to create data drill-down applications, which we described in detail in Chapter 10, "Cold Fusion Basics." This Wizard performs the following functions:
We'll now use the Data Drill-Down Application Wizard to create an application that will let us view vacation dates booked by any employee.
There are 12 steps involved in creating a data drill-down application with the Data Drill-Down Wizard.
Step 1: Naming the Application Load the Web Applications Wizard if it is not
already running, and then click the Data Drill-Down Application Wizard button to
start the Wizard. An introductory screen presents, as shown in Figure 13.23, which
lists the templates that will be created, and explains how they all work together.
Figure 13.23 The Data Drill-Down Application Wizard introductory screen lists the templates that the Wizard will create.
Once you have read the introduction, click the Next > button to name your application, as shown in Figure 13.24. Enter Employee Vacations in the Application Title field, and then click the Next > button.
Figure 13.24 The name provided in the Application Title field is used as the HTML TITLE for all the generated templates.
Step 2: Specifying the Output File Location Next you need to tell the
Wizard where to place the templates that it generates. The Wizard displays a default
destination as shown in Figure 13.25. To specify a different output directory, click
the Browse button to display the Select Directory dialog as shown in Figure 13.26.
Once you have entered or selected the desired directory, click the OK button.
Figure 13.25 When generating templates with a Web Application Wizard, you must specify the output file location.
Once you are satisfied with the Destination Directory, click the Next > button.
Step 3: Selecting the ODBC Data Source Now the Wizard is ready for you to
specify the ODBC data source into which collected data is to be inserted. Select
the A2Z data source as shown in Figure 13.27, and then click the Next > button.
Figure 13.26 Templates may be stored on local or network drives.
Figure 13.27 When creating a data entry form with a Web Application Wizard, you must select an existing ODBC Data Source into which new data will be inserted.
See the section in Chapter 8, "Introduction to SQL," entitled "Creating an ODBC Data Source," for a detailed explanation of ODBC data sources and how they are used.
Step 4: Selecting the Database Tables Unlike the Data Entry Application Wizard we discussed earlier, the Data Drill-Down Application Wizard enables you to create applications that interact with multiple tables. This way, you can drill down through an Employee's table, for example, and then display vacations from a Vacations table for the selected employee.
The table selection screen is split into two columns. The left column lists Available Tables, and the right column lists the Involved Tables, i.e. whatever tables you have selected.
The selection buttons between the two columns are explained earlier in this chapter in Table 13.2.
Select tables "Departments", "Employees", and "Vacations", as shown in Figure 13.28, and then click the Next > button.
Figure 13.28 The Data Drill-Down Application Wizard lets you include multiple tables in your application.
Step 5: Specifying Table Relationships As we saw in Step 4, this Wizard
allows you to include multiple tables within your data drill-down application. Wherever
possible, the Wizard attempts to automatically ascertain the relationship between
these tables. It does this by comparing field names in the various tables in an attempt
to find obvious matches.
See Chapter 6, "Database Fundamentals," for a complete explanation of relational databases, primary and foreign keys, and how tables are joined.
For example, in our application, the Wizard correctly identified the relationship between the "Employees" and "Vacations" tables. Both have a field named EmployeeID, so the Wizard assumed that this field must be the joining field, that is, the field that is used to link the two tables together. The screen shown in Figure 13.29 shows that the Wizard identified the relationship "Employees.EmployeeID = Vacation.EmployeeID".
TIP: If the Wizard incorrectly assumes a relationship, you can manually correct it. To do this, you just select the incorrect relationship from the relationship list, and click the Remove button.
Figure 13.29 The Data Drill-Down Application Wizard attempts to automatically detect table relationships.
In our case, the Wizard successfully detected the relationship between the "Employees" and "Vacations" tables. It did not, however, detect the relationship between the "Employees" and "Departments" tables. We'll have to set this one manually.
The correct relationship between the "Employees" and "Departments" tables is via the department ID field. Select Employees.DepartmentID from the left drop down list, and select Departments.ID from the right list. Then click the Add button to add this new relationship to the relationship list.
Figure 13.30 Table relationships can be added or removed manually as needed.
The screen should now look like the one shown in Figure 13.30. Once you have verified that the relationships are set correctly, click the Next > button.
Step 6: Specifying the Search Fields You can search for specific records for
drilling down with the Wizard-generated data drill-down templates. You need to specify
which fields to search and the type of search, and Cold Fusion then generates both
the search screen and the dynamic SQL required to perform the search.
See the section in Chapter 11, "Cold Fusion Forms," entitled "Creating Dynamic SQL Statements," for an explanation of dynamic SQL statements and how they are created and used.
The next Wizard screen prompts you for the fields your users will search. As shown in Figure 13.31, the Field list contains a listing of every field in the involved tables.
You may select any of these fields from that list. The first field we need is the FirstName field in the "Employees" table, so select Employees.FirstName from the list box.
TIP: The order that fields appear in the final search screen is based upon the order in which they are selected in the Data Drill-Down Application Wizard. By selecting the fields in the desired sequence, you control the order the fields appear on the search screen.
Figure 13.31 The Fields drop-down listbox lists all the search fields available to you.
Once you have selected the search field, you have to tell the Wizard what type of search. The Wizard supports eight different types of searches, and you may select any from the Matching Condition drop-down listbox.
We'll allow users to search for employees whose name begins with the user-supplied search string, so select begins with from the Matching Conditions drop-down list box.
Then click the Add button to add the FirstName field to the list of search fields.
See the section in Chapter 8, "Introduction to SQL," entitled "Filtering Data," for thorough coverage of SQL search conditions and how each search type is used.
Figure 13.32 You may select any of eight search types from the Matching Conditions drop-down listbox.
Next we need to add the LastName field so that users may also search on that field. Select Employees.LastName from the Fields list, and begins with from the Matching Conditions list. Then click the Add button to add the LastName field to the search fields list.
Your screen should now look like the one in Figure 13.33. Once you have verified that the search fields were specified correctly, click the Next > button.
Figure 13.33 The Data Drill-Down Application Wizard lists all search fields and the search type that you selected for each.
Step 7: Specifying Field Properties and Options Now you may specify search
fields options and properties. For help on doing this, see Step 6 of the Data Entry
Application Wizard earlier in this chapter.
Set the FirstName field caption to First Name and the LastName field caption to Last Name. Set the TextBox Size and Maximum Length options for both fields to 30.
Verify that your screen looks like the one shown in Figure 13.34, and then click the Next > button.
Figure 13.34 You may specify data validation rules for any of the specified search fields.
Step 8: Building the Results Page The Wizard now knows how to implement
the dynamic searching of employees based on the search fields you just specified.
Your next task is to instruct the Wizard as to what data to display in the results page. The field selection screen is once again split into two columns. The left column contains "Available Fields," and the right column contains "Fields to Display." Between the two columns are the selection buttons (see Table 13.2 earlier in this chapter if you need a review).
TIP: The order in which fields are selected is the order in which they'll appear on the results page. You can, therefore, control the order of data in the results page by selecting fields in a specific order.
Select the following fields in this order: Departments.Department, Employees.LastName, Employees.FirstName, Employees.MiddleInit, Employees.PhoneExtension.
Your Wizard screen should now look like the one shown in Figure 13.35, with your selected fields on the right.
Figure 13.35 You may select as many or as few fields to display in the search results screen.
Now click the Next > button.
Step 9: Customizing the Results Page You may now customize the results page.
The Wizard screen, shown in Figure 13.36, contains default values for the Caption
and Width of each field. The default Caption is always the field name itself, and
the default Width is always 20 characters wide.
Figure 13.36 The Wizard enables you to customize the Caption and Width of each field to display in the results page.
| Field | Caption | Width |
| Departments.Department | Department | 30 |
| Employees.LastName | LastName | 30 |
| Employees.FirstName | FirstName | 30 |
| Employees.MiddleInit | Middle | 1 |
| Employees.PhoneExtension | Extension | 4 |
Set the Captions and Widths as listed in Table 13.4, and then click the Next > button.
Step 10: Building the Details Page Next, you must specify what information
is to be displayed on the details page. This is the last page in the drill-down,
so this is where you display any needed drill-down information. In our case, this
is the page that will actually list all the vacations booked by a specific employee.
Once again, this screen is split into two columns, one for "Available Fields" and one for "Fields to Display," and the selection buttons are in between.
In our case, the only information we want to display on the detail page is any vacations booked by a specific employee. So select Vacations.VacationStart and Vacations.VacationEnd, in that order.
Figure 13.37 Although you can select the fields to display in the details page, there are no Wizard- based formatting options for these fields. Any formatting will, therefore, have to be done manually later on.
Your Wizard screen should now look like the one in Figure 13.37. Verify that you have selected the right fields, and in the correct order, and then click the Next > button.
Step 11: Specifying the Detail Page Unique Identifier There is now only one
last step to perform before the Wizard generates the application templates for you.
In order for the Wizard to correctly construct a SQL statement that will select exactly the details you are looking for, you must specify which field contains the unique identifier for the detail records. In other words, determine the field that will allow the selection of just the records you want, and no others.
Usually this would be the primary key fields. In our case, however, we want all records that match a specific value to be displayed. We are not looking for a specific vacation, rather, we want all vacations booked by a specific employee.
See Chapter 6, "Database Fundamentals," for a complete explanation of relational databases, primary and foreign keys, and why records need unique identifiers.
The way we uniquely identify employees is with their employee ID, which is stored in the EmployeeID field. Select Vacations.EmployeeID from the Unique Identifier drop-down list box, as shown in Figure 13.38, and then click the Next > button to generate the templates.
Figure 13.38 In order for the Wizard to correctly generate a SQL statement to display just the data you want, you must specify the Unique Identifier that it will use.
Step 12: Generating the Templates The Wizard has now created the complete
data drill-down application, and you should see a confirmation message like the one
shown in Figure 13.39. The message contains a description of the templates that were
generated, what each one does, and instructions on how to test the new templates.
Figure 13.39 The Wizard confirmation message contains instructions describing how to test the new application.
Now let's test the new drill-down application. Point your browser at http://yourserver/a2z/search.cfm (once again replacing "yourservername" with the name or IP address of your own web server). You'll see a screen like the one shown in Figure 13.40.
Search for "Kim Black", or leave the fields blank and submit the search. Either way, you'll retrieve "Kim Black" because she is the only employee we added vacation dates for.
Look at the output shown in Figure 13.41--it is not exactly what you expected. Kim's name appears three times because she has three vacations booked. If you had entered 50 vacations for her, her name would appear 50 times on the results page.
The default generated SQL statement is not performing the intended search, so it will need fixing manually.
Now try clicking on any of the links containing Kim's name. You'll see a detail screen similar to the one shown in Figure 13.42. The information that displays is correct, but the formatting is terrible and the vacations are listed out of order.
So, that template will need manual modification too.
Figure 13.40 The Wizard-generated search screen allows users to insert text in any, all, or none of the search fields.
First of all, let's fix the results page. If you look at the code in Listing 13.3, you'll see that a dynamic SQL SELECT statement is built based on the search fields passed. The statement selects six fields from three tables which are joined together using the relationship information we provided in step 5 earlier.
Figure 13.41 The default SQL statement generated for the results page displays the employee's name once for each vacation date booked.
Figure 13.42 The generated drilled-down details page lacks any formatting at all.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
<!--This Cold Fusion Template is responsible for the display of the
Results Page. It uses fields passed from the Search Page
to run a query against the database and retrieves the fields
displayed on this page. -->
<!--Run the query using fields entered in the previous page.-->
<CFQUERY NAME="GetResults" DATASOURCE="A2Z"
SQL="SELECT Departments.Department,Employees.LastName,Employees.FirstName,Employees.MiddleInit, Employees.PhoneExtension,Vacations.EmployeeID
FROM Employees,Departments,Vacations
WHERE Employees.EmployeeID = Vacations.EmployeeID AND Employees.DepartmentID = Departments.ID">
<CFIF #Employees__FirstName# IS NOT "">
<CFSQL SQL=" AND Employees.FirstName LIKE `#Employees__FirstName#%' ">
</CFIF>
<CFIF #Employees__LastName# IS NOT "">
<CFSQL SQL=" AND Employees.LastName LIKE `#Employees__LastName#%' ">
</CFIF>
</CFQUERY>
<!--Now assemble the HTML of the Results Page -->
<HTML>
<HEAD><TITLE>Employee Vacations</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="results.gif"></CENTER>
<CENTER><H1>Employee Vacations</H1></CENTER>
<!--You can enter your own HTML here if you would like to customize
the look of the Results Page-->
<!--Display result data from the database.-->
<CFTABLE QUERY="GetResults">
<CFCOL HEADER="Department" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#Department#</A>">
<CFCOL HEADER="Last Name" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#LastName#</A>">
<CFCOL HEADER="First Name" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#FirstName#</A>">
<CFCOL HEADER="Middle" WIDTH="1" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#MiddleInit#</A>">
<CFCOL HEADER="Extension" WIDTH="4" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#PhoneExtension#</A>">
</CFTABLE>
<!--Inform user that no results were retrieved if no records found-->
<CFIF #GetResults.RecordCount# IS 0>
<H2>No record were found matching your criteria</H2>
<P>Please press the BACK button and try again.
</CFIF>
</BODY></HTML>
To retrieve just one occurrence of each name, we need to use the SQL DISTINCT keyword. All we need to do is to add the word DISTINCT after the SELECT, but before the names of the selected columns. The modified template is shown in Listing 13.4.
See Chapter 16, "Advanced SQL," to learn more about the SELECT DISTINCT keyword, as well as other advanced SQL techniques.
<!--This Cold Fusion Template is responsible for the display of the
Results Page. It uses fields passed from the Search Page
to run a query against the database and retrieves the fields
displayed on this page. -->
<!--Run the query using fields entered in the previous page.-->
<CFQUERY NAME="GetResults" DATASOURCE="A2Z">
SELECT DISTINCT Departments.Department,Employees.LastName,Employees.FirstName,Employees.MiddleInit, Employees.PhoneExtension,Vacations.EmployeeID
FROM Employees,Departments,Vacations
WHERE Employees.EmployeeID = Vacations.EmployeeID AND Employees.DepartmentID = Departments.ID
<CFIF #Employees__FirstName# IS NOT "">
AND Employees.FirstName LIKE `#Employees__FirstName#%'
</CFIF>
<CFIF #Employees__LastName# IS NOT "">
AND Employees.LastName LIKE `#Employees__LastName#%'
</CFIF>
</CFQUERY>
<!--Now assemble the HTML of the Results Page -->
<HTML>
<HEAD><TITLE>Employee Vacations</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="results.gif"></CENTER>
<CENTER><H1>Employee Vacations</H1></CENTER>
<!--You can enter your own HTML here if you would like to customize
the look of the Results Page-->
<!--Display result data from the database.-->
<CFTABLE QUERY="GetResults">
<CFCOL HEADER="Department" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#Department#</A>">
<CFCOL HEADER="Last Name" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#LastName#</A>">
<CFCOL HEADER="First Name" WIDTH="30" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#FirstName#</A>">
<CFCOL HEADER="Middle" WIDTH="1" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#MiddleInit#</A>">
<CFCOL HEADER="Extension" WIDTH="4" TEXT="<A HREF=""Detail.CFM?Vacations__EmployeeID=#EmployeeID#"">#PhoneExtension#</A>">
</CFTABLE>
<!--Inform user that no results were retrieved if no records found-->
<CFIF #GetResults.RecordCount# IS 0>
<H2>No record were found matching your criteria</H2>
<P>Please press the BACK button and try again.
</CFIF>
</BODY></HTML>
Now let's execute the search again. This time Kim's name only appears once, as seen in Figure 13.43. The DISTINCT keyword never retrieves duplicate records, and as we were only retrieving records for a single EmployeeID, only one record retrieves.
All that's left now is to update the details page. There are two problems with the details page.
Figure 13.43 To display only one occurrence of repeated information, you can use the SELECT DISTINCT keyword.
){kind=link}
<!--This Cold Fusion Template retrieves and displays the data
on the Detail Page. It uses the Unique Identifier field
passed in the URL link that is invoked when user clicks
on a record on the Results Page to retrieve the detail record.-->
<!--Run the query using the Unique Identifier passed in the URL.-->
<CFQUERY NAME="GetDetailData" DATASOURCE="A2Z"
SQL="SELECT Vacations.VacationStart,Vacations.VacationEnd
FROM Vacations
WHERE
Vacations.EmployeeID = #Vacations__EmployeeID# ">
<!--Now assemble the HTML of the Detail Page -->
<HTML>
<HEAD><TITLE>Employee Vacations</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="detail.gif"></CENTER>
<CENTER><H1>Employee Vacations</H1></CENTER>
<!--You can enter your own HTML here if you would like to customize
the look of the Detail Page-->
<!--Display detail data from the database.-->
<CFOUTPUT QUERY="GetDetailData">
<PRE>
Vacations.VacationStart: #VacationStart#
Vacations.VacationEnd: #VacationEnd#
</PRE>
</CFOUTPUT>
</BODY></HTML>
The vacations display out of order. As you can see in Listing 13.5, the SQL SELECT statement that retrieves the vacations has no ORDER BY clause. To sort the vacations by start date, we need to add the clause:
ORDER BY VacationStart
The second change we need to implement is a formatting enhancement. The default generated page, as seen in Figure 13.42, lacks any formatting at all. Because the details that display are fields with a Date / Time data type, displaying the raw field contents without any formatting, creates a very confusing and difficult-to-read page.
See the section in Chapter 7 entitled "Data types," for a detailed discussion of different data types and why they are used.
To display dates properly, we need to use the Cold Fusion DateFormat() function that converts a date from its raw SQL form (which contains just numbers and also includes a time portion) to a readable date format. The following line of code creates a list item with two dates, the vacation start date and end date, both of which are formatted using the DateFormat() function.
<LI>#DateFormat(VacationStart)# - #DateFormat(VacationEnd)#
See For more information about the DateFormat() function, and the formatting options it supports, see Appendix A, "Cold Fusion Reference."
Instead of displaying the dates as pre-formatted text, the code in Listing 13.6 has been updated to create an ordered list. The <CFOUTPUT> block is between the <OL> and </OL> tags, so each vacation is output as a list item.
The new and improved details screen is shown in Figure 13.44.
<!--This Cold Fusion Template retrieves and displays the data
on the Detail Page. It uses the Unique Identifier field
passed in the URL link that is invoked when user clicks
on a record on the Results Page to retrieve the detail record.-->
<!--Run the query using the Unique Identifier passed in the URL.-->
<CFQUERY NAME="GetDetailData" DATASOURCE="A2Z">
SELECT Vacations.VacationStart,Vacations.VacationEnd
FROM Vacations
WHERE Vacations.EmployeeID = #Vacations__EmployeeID#
ORDER BY VacationStart
<!--Now assemble the HTML of the Detail Page -->
<HTML>
<HEAD><TITLE>Employee Vacations</TITLE></HEAD>
<BODY>
<CENTER><IMG SRC="detail.gif"></CENTER>
<CENTER><H1>Employee Vacations</H1></CENTER>
<!--You can enter your own HTML here if you would like to customize
the look of the Detail Page-->
<!--Display detail data from the database.-->
<OL>
<CFOUTPUT QUERY="GetDetailData">
<LI>#DateFormat(VacationStart)# - #DateFormat(VacationEnd)#
</CFOUTPUT>
</OL>
</BODY></HTML>
Figure 13.44 The best way to format record information is to use the Cold Fusion formatting functions, like DateFormat().
You have successfully used the Data Drill-Down Application Wizard to create a basic drill-down interface. You only had to make minor modifications to the generated code, the Wizard did most of the tedious work for you.
As you can see, the Web Applications Wizards are tremendous time savers.
In this chapter you learned how to use the two Web Applications Wizards. We created a data entry application to book vacations using the Data Entry Application Wizard, and an application to check employee's booked vacations using the Data Drill-Down Application Wizard. Both of these Wizards are tremendous time savers, and the code they generate is a great foundation on which to build your own world class applications.
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}