The next [ava-based field to look at is created with the CFGRIDtag. The CFGRIDtag is used to create a grid control.or rows and columns, which resembles a spreadsheet or a word processor table.
Grids are ideally suited to displaying the results of a query because they enable the presentation of multiple fields from multiple records in a query. In addition, grids provide an easy way to enable the editing of multiple records from a database when used in conjunction with the CFGRIDUPDATE tag.
Figure 15.22 provides an example of a grid control.
These grids can be used in several ways:
• Data presentation
• Data selection
• Data editing
You will consider each of these in turn and then look at additional features of grid controls such as those that control the appearance of elements in the grid .
The first step in using a grid control is to present query data in a useful way for browsing. When used in this way data in the grid an not be selected and when the form containing the grid is submitted no data is sent from the grid Held.
This is the ideal place to start working with grids because it enables you to learn the mechanics of building grids from your queries without having to deal with some of the complexities inherent in using grids for data selection an a editing.
Because grids are useful when working with query result sets, we need to start our discussion of creating grids with a query that you can use in your examples. Because you have used the employee data database throughout this chapter continue to use it here with the following query:
Given this query you can build a simple grid for browsing the data in this query result by using the CFGRIDand CFGRIDCOLUMtaNgs. CFGRIDis used to create the grid by specifying at a minimum the name of the grid, the query to use, and a selection mode. In the case of a grid for displaying data for browsing, you want to use SELECTMODEB=r”owse” which indicates that the grid is for viewing data and that selection is not possible:
The first thing you probably noticed in both grids is that the column headers are the names of the fields This can be less than ideal. For instance Last name and First name should appear as Last Name and First Name and Salary might be more descriptive as Monthly Salary.
These changes can be achieved by using the HEADERattribute of the CFGRIDCOLUMN tag to specify alternate column headers:
<CFGRID NAME-‘GridTest’ QUERY-‘ Employees’ SELECTMODE-‘Browse’)
<CFGRIDCOLUMN NAME-‘LastName’ HEADER-‘Last Name’)
<CFGRIDCOLUMN NAME-‘FirstName’ HEADER-‘First Name’)
<CFGRIDCOLUMN NAME-‘Salary’ HEADER-‘Monthly Salary’)
This code produces the grid shown in Figure 15.24:
Another problem with the salary grid is the way in which the numeric values in the salary column are displayed. Numeric values should be aligned to the right side of the column so that the digits align correctly. This is achieved by using the CFGRIDCOLUMN tag’s DATAALIGNattribute, which can specify three possible alignments for the data in a column: Left, Cente r , and Right. The default alignment for a column is left aligned.
For the salary column you will want to use the following tag:
<CFGRIDCOLUMN NAMEz’Salary’ HEADER-‘Monthly Salary’ ATAALIGN-‘Right’).
Another aspect of presenting the salaries is to present them in proper currency format such as $1000.00. You can achieve this by using the NUMBERFORMAT attribute of the CFGRIDCOLUMN tag. This attribute takes as its value a mask defining how the number should be displayed. This Vlask uses the same syntax and special characters as the . NumberFormat function discussed in Chapter 12, “Grouping, Nesting, and Formatting Output.”
By default the rows of the grid reflect the order in which the records are returned in the query result set (in our case the query is ordered by employee ID number). However grids can dynamically sort presented data based on the data in any column. To enable this feature you need to enable sorting using the SORT attribute of the CFGRID tag. This attribute can be set to YES or NO (NOis the default value) and when set to YES causes ascending and descending sort buttons to be displayed as shown in Figure 15.26.
To sort a column click the column’s header: this selects and highlights all elements in . the column as shown in Figure 15.27
The next step is to click the appropriate sort button. For instance, to sort in descending order based on the data in the selected column, simply click the Z -> A button.
The buttons used to select ascending or descending order for sorting can use custom text instead of the default A -> Z and Z -> A. Alternate text is specified using the SORT ASCENDING BUTTON
attribute and the SORTDESCENDINGBUTTON attribute. Therefore he CFGRID tag
<CFGRID tlAME=’GridTest’ QUERY=’Employees~ SELECTMODEz’ Browse’ SORT-‘Yes’ SORTASCENDINGBUTTON=’Ascending’ SORTDES(tNDINGBUTTON=’Descending’)
produces a grid with the custom sort buttons shown in Figure 15.28.
The next step in working with grids is to move from browsing data to selecting data. You can specify three data selection modes by using the SELECTMODE attribute of the CFGRID tag. The three modes are:
Single Allows selection of a single cell in the grid. Figure 15.29 shows a single cellbeing selected.
Column Allows selection of an entire column in the grid. Figure 15.30 shows a column being selected.
Row Allows selection of an entire row in the grid. Figure 15.31 shows a row being selected.
Commonly, grid controls are used for selectirlg entire rows because this action represents the selection of a record from a database. Therefore, we will explain this in detail Refer to the ColdFusion documentation for more information on selecting single cells or columns,
The CFGRIDtag for selecting entire rows requires the use of the SELECTMODER=o”w” attribute. When the form containing this type of selection grid is submitted, the selected data becomes accessible through form variables that reflect the column names from the grid. Each column’s data for the selected record is reflected through the variable Form.GridName.ColumnName.
Forexample, consider the employee data examples. If you have a grid containing columns for all the fields in your table and the grid is named GridTest, then you can access the selected row’s fields with the following five variables: ,
The last major application for grid controls is data editing. When using SELECTMODE= “Ed; t·,you can allow users to edit the contents of a displayed grid and then process the information after the form is submitted, either by reflecting the changes back into the database used to create the grid or by performing other relevant actions.
When using this select mode, the user can highlight and change the values stored in the individual cells of the grid. Figure 15.32 shows a cell being edited
In addition to simply updating existing content, you can also allow users to add and delete rows from the grid and then have these changes reflected back to the database. The ability to add and delete rows is enabled by setting the INSERT and DELETE attributes to Yf S. By default they are NO. Setting these attributes to YES produces the results shown in Figure 15.33 with Insert and Delete buttons next to the sort buttons we discussed earlier.
When inserting a row it is inserted as the last row of the grid as shown in Figure 15.34.
As with sort buttons, it is possible to customize the Insert and Delete buttons using the INSERT Bun ON and DELETE Bun ON attributes. For instance using INSERT BUnON Add Row”and DELETE BunON=”Del. Row”in the CFGRIDtag produces the buttons shown in Figure 15.35
The primary purpose of editing grid data is to allow the user to directly edit data stored in a database. Given this, you need a way to reflect these changes back to the database when the form is submitted. This can be achieved in two ways
• Using the CFGRIDUPDATtaEg
• Using CFQUERYtags
Before looking at the process of moving user changes into the database you need to consider the issue of key values. In order for our different methods of moving changes into the database to work you need to be able to conclusively determine which record have been edited. The easiest way to do this is by being sure that you include the primary key of the table in question in your grid .
You included the primary key of your employee table in all the grid examples in this section. However there may be compelling reasons to prevent display of a column while still including the values in the grid and the data submitted with the form. This is: done by using the DISPLAY attribute of the CFGRID COLUMN tag. When the attribute is set to NO a column can be included in the grid but not displayed. By default the DISPLAY attribute is set to YES.
For instance, if you use
<CFGRIDCOLUMN NAME-‘ID” DISPLAY3’NO’)
in producing your grid you can produce a grid with the ID column hidden like the one in Figure 15.36 but which includes the value of the ID fields from the database table.