Display database records in a paged, sortable table
Copyright 2004 ESWsoftware
Visit us at www.eswsoftware.com
|Version:||1.2b (Release notes)|
Adobe ColdFusion 5.0 or greater, or
BlueDragon 6.1 or greater
See System requirements for more detail
Adobe ColdFusion MX 6.0 or greater
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?
- 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.Back to top
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:
srcattribute has been removed and replaced with a
resourcePathattribute. See the instructions below on setting this correctly.
- You no longer need to include a stylesheet in your page manually or supply the
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.
resourcebundle.cfm files, along with the
sortable folder in your server's custom tags folder or another preferred location.
resources folder to a web-
accessible location on your web site. Make sure the supplied
test.gif is found in the
resources folder. Open your
browser and type in the URL of the
resources folder on your web
/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
test.gif off the end but leave a trailing slash. What
remains is the correct value for your
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.
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
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:
So you may need to end your
thisPage URL with a
? , e.g.
In order to be able to edit your records, you must provide the
link attribute along with the
key attribute. The
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
the value of the
link attribute like this (assuming our key is
Your link URL may need to end with a
? , e.g.
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>Back to top
6. cf_sortable attributes
- 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.
- 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
- query="query_name" [REQUIRED]
- This is the name of your query record set. It may be a record set
cfqueryor 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"]
skinattribute 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
sortBycolumn sorted when the page first loads. Allowed values are
- 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
- 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
columnattribute 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.
- 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
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.
- Any other attributes are passed through to the generated HTML
table tag. For example:
align="center" style="width : 10%"Note that cf_sorTable generates a
classattribute for the sorted column. If you specify your own
classattribute, then cf_sorTable will not generate this attribute, and the sorted column will not be highlit.