Documentation

cf_sorTable

Display database records in a paged, sortable table
Copyright 2004 ESWsoftware
Visit us at www.eswsoftware.com

Version:	1.2b (Release notes)
Date:	20041002
Required:	Adobe ColdFusion 5.0 or greater, or BlueDragon 6.1 or greater See System requirements for more detail
Recommended:	Adobe ColdFusion MX 6.0 or greater
Recent changes:	20041002 * fixed CF5 locking issue 20041002 * new link attribute mishandled - fixed 20040929 * added French, Dutch, and German resource bundles 20040921 * added individual link attribute for each column 20040921 * added localization facilities, added locale attribute 20040914 * added BlueDragon support by adding a custom QoQ 20040805 * wrapped a cftry around cfhtmlhead (which throws an error if used after cfflush 20040619 * added defaults for border, cellpadding, cellspacing 20040522 * Bugfix: link="" was throwing an error 20040517 * changed default thisPage to #cgi.script_name#?

What is it?
What's new in version 1.2?
Installation
Usage
The tags
cf_sortable attributes
cf_column attributes

1. What is it?

cf_sorTable provides a table of records from a query record set you provide. You specify the columns you want to appear in the table. cf_sorTable splits the results into multiple pages of a length you may specify, and allows sorting of the records by any column in the table. Each record may link to a page (or several different pages) where that record may be edited.

All the table layout is managed through cascading style sheets, giving you extensive control over the generated table's appearance. See the examples.

2. What's new in version 1.2?

BlueDragon Server support (see http://www.newatlanta.com/bluedragon/ )
Now fully localisable to your language and country
Multiple links now available per record
Issues resolved: unset link attribute, cfflush incompatibility

If you are upgrading from version 1.0, you must be aware of the following changes, which will affect your existing code:

The src attribute has been removed and replaced with a resourcePath attribute. See the instructions below on setting this correctly.
You no longer need to include a stylesheet in your page manually or supply the class attribute.

3. Installation

If you are not familiar with installation of custom tags, please read this brief article first: Installing and using custom tags. It will save you time and frustration. You may also like to try the Installation Wizard which will provide a custom recommendation for your environment.

Install the sortable.cfm, column.cfm and resourcebundle.cfm files, along with the sortable folder in your server's custom tags folder or another preferred location.

Copy the resources folder to a web- accessible location on your web site. Make sure the supplied image test.gif is found in the resources folder. Open your browser and type in the URL of the resources folder on your web site. Add /test.gif on the end. If a large green check mark appears along with a message, then you have typed the address correctly. If not, keep trying.

Once you see the message, strip the http:// and your domain name off the beginning of the URL but leave a leading slash. Strip the test.gif off the end but leave a trailing slash. What remains is the correct value for your resourcePath attribute!

It is important that the resourcePath attribute is set correctly so that the style sheets and images for your table are used. If your table appears plain and unstyled then either this path is incorrect or you have specified a non-existent skin.

4. Usage

To display a table of records follow these steps. Create a query record set using cfquery tags or other means (for example, cfdirectory also creates a record set).

Add the opening and closing cf_sorTable tags to your page. Between them, add a series of cf_column tags, one for each column of your table.

<cf_sortable
  ...
>
  <cf_column
    ...
  />
  .
  .
  .
</cf_sortable>

You can also use cfmodule or, in CFMX, use cfimport. See the following page for general information about calling custom tags: Installing and using custom tags.

Note that for this tag to successfully page through records and sort columns, it must be aware of the address of the current page. If you find the first page appears OK but the sorting and page links don't work, then this is your problem. Set the cf_sorTable thisPage attribute to the Web URL of the current page. cf_sorTable will expect to be able to place variables on the end of this address in the format:

&sortBy=col&sortDirection=desc&offset=276

So you may need to end your thisPage URL with a ? , e.g. listrecords.cfm? becomes listrecords.cfm?&sortBy=col&sortDirection=desc&offset=276

index.cfm?fuseaction=listrecords becomes index.cfm?fuseaction=listrecords&sortBy=col&sortDirection=desc&offset=276

In order to be able to edit your records, you must provide the cf_sorTable link attribute along with the key attribute. The link attribute specifies the web URL of the editing page. key is the name of the column that can uniquely identify this record (it doesn't need to be the database table's primary key, although that would be a typical choice). cf_sorTable will append the key to the value of the link attribute like this (assuming our key is called "id"):

&id=1234

Your link URL may need to end with a ? , e.g. editrecord.cfm? becomes editrecord.cfm?&id=1234

index.cfm?fuseaction=editrecord becomes index.cfm?fuseaction=editrecord&id=1234

Note that from version 1.2, you may also supply a link attribute for individual columns.

Note: It is recommended that you do not use cf_sorTable in combination with the cfflush ColdFusion tag as cf_sorTable may not be able to add the stylesheet information to your page's head. If you do, you may need to add a link to the skin's style sheet manually.

5. The tags

<cf_sorTable 
  anchor="anchor"
  key="primary_key_column"           [REQUIRED IF LINK SET]
  link="URL"
  locale="locale"                    [DEFAULT: getLocale()]
  query="query_name"                 [REQUIRED]
  rows="number_of_rows_per_page      [DEFAULT: "25"]
  skin="skin"                        [DEFAULT: "glacier"]
  sortBy="initial_sort_column"       [REQUIRED]
  sortDirection="asc" or "desc"      [DEFAULT: "asc"]
  resourcepath="web_path"            [DEFAULT: "/resources/"]   
  thisPage="URL"                     [DEFAULT: "#cgi.script_name#?"]
  any_other_attributes
>

  <cf_column
    caption="column_caption"         [DEFAULT: "column"]
    column="column_name"             [REQUIRED]
    link="URL"
	type="column_type"               [DEFAULT: "string"]
    expression="expression"          [DEFAULT: none]
    any_other_attributes
  />
	
  .
  .
  .
	
</cf_sortable>

6. cf_sortable attributes

anchor="anchor"

When set, a page anchor is inserted just above the table so that the page will automatically scroll to the table position whenever any links within the table are clicked. This is particularly useful on very long pages. The anchor must be unique. See HTML 4.01 Specification, section 12.2.

key="primary_key_column" [REQUIRED IF LINK SET]

This is the name of a column in your record set that will uniquely identify any row. You need this if you want to be able to edit your columns so that your editor page knows which record you want to edit.

link="URL"

This is the URL of the page your records should link to, presumably to view or edit the record details. It is up to you, the developer, to write the code for this page. See the notes above about the format of this URL.

locale="locale" [DEFAULT: getLocale()]

The locale is used to select the appropriate language resource bundle. It is recommended that you use the ColdFusion function, setLocale() to set your page's locale, and ignore this attribute. For example, add <cfset setLocale("English (United States)")> to your Application.cfm file.

query="query_name" [REQUIRED]

This is the name of your query record set. It may be a record set generated using cfquery or any other means. The record set does not need to be sorted, as cf_sortable will sort it.

resourcepath="web_path" [DEFAULT: "/resources/"]

This attribute specifies the web path to the client resources (stylesheets and images) that control the appearance of the table. See the section above on setting this correctly.

rows="number_of_rows_per_page [DEFAULT: "25"]

This is the number of records to appear on an individual page.

skin="skin" [DEFAULT: "glacier"]

The skin attribute controls the visual appearance of the table. A skin is simply a folder containing a stylesheet and a few images. Two skins come with cf_sorTable. These are "glacier" and "finestre". You can create your own skins or modify those included.

sortBy="initial_sort_column" [REQUIRED]

This is the name of the column you want the record set sorted by when the page first loads.

sortDirection="asc" or "desc" [DEFAULT: "asc"]

This is the order you want your sortBy column sorted when the page first loads. Allowed values are "asc" and "desc".

thisPage="URL" [DEFAULT: "#cgi.script_name#?"]

This is the web path from the current page to the current page. This is required so that the sort links and page links work. See the notes above on the format of this URL, but remember that cf_sorTable will attempt to add variables to the end of the URL, so you will need a ? in there.

any_other_attributes

Any other attributes are passed through to the generated HTML table tag. For example:

border="1"
cellspacing="2"
style="background-color : gray"

7. cf_column attributes

caption="column_caption" [DEFAULT: "column"]

This is the caption that will appear at the top of the column. If left out, the column attribute value will be used.

column="column_name" [REQUIRED]

This is the name of the column in your record set.

expression="expression" [DEFAULT: none]

This is an advanced feature. Feel free to skip it for now if you are new to cf_sorTable. You can tell cf_sorTable to evaluate an expression instead of displaying the value in the standard way. To do this, specify the expression attribute. Use the word "value" in your expression to denote the value of the field. For example, the simple expression: expression="round(value)" will display rounded off numbers in this column. The expression: expression="iif(value, value, de(""-""))" will display a hyphen (-) instead of a number if the value is zero. Note the de() stops ColdFusion from attempting to evaluate the hyphen. Note that the column is still sorted on the value, not the expression result.

link="URL"

You may set separate URLs for each column in your table. When a cell in this column is clicked, the ID of the record is appended to this URL.

type="column_type" [DEFAULT: "string"]

This is the type of data in the column. The following types are recognised:

boolean
date
datetime
email
numeric
price
time
url
string

Any other types will be formatted the same as string. cf_sorTable will however apply a class to the data matching the type. So there may be some use in adding custom types, for custom formatting.