For Adobe ColdFusion application servers For BlueDragon application servers 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#?

Contents

  1. What is it?
  2. What's new in version 1.2?
  3. Installation
  4. Usage
  5. The tags
  6. cf_sortable attributes
  7. cf_column attributes
Back to top

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:

  • 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.
Back to top

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.

Back to top

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.

Back to top

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

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"
Back to top

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.
any_other_attributes
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 class attribute for the sorted column. If you specify your own class attribute, then cf_sorTable will not generate this attribute, and the sorted column will not be highlit.
Doper Studios said on 11/10/2004 at 5:54:04 AM:
I love this tag! Not only is it functional, easy to use, but it's so usefull. My clients love it too. I mean what looks better, sorTable or some clunky old text to rummage through? I did have one question; is there any way to add an image as a link rather than the results from the DB?
matthew said on 2/22/2005 at 5:58:40 PM:
A few people have asked for that. Currently there is no elegant way but it can be done.
Add the following attribute to your column:
expression="'<img src=""/images/'&value&'.jpg"">'" -- you'll need to add your appropriate image path in there.
Michael Williams said on 10/18/2005 at 2:17:20 PM:
I just bought this tag and was wondering how to you target a frame in a link?
Victoria said on 11/5/2005 at 1:37:37 AM:
Our tables have many records. Is there a way to search for a particular record (ie:ID#) and it be highlighted?
matthew said on 11/5/2005 at 8:20:11 AM:
There is currently no built-in way to target a frame. You could modify cf_sorTable by replacing onclick="location.href='#columnLink#'" with something like "top.frames['framename'].location = '#columnLink#'" -- I haven't checked that JavaScript. If anybody else is interested in this feature, please say so.
matthew said on 11/5/2005 at 8:25:07 AM:
regarding highlighting records, it's conceivable that your highlighted records could be spread across multiple pages and thus be hard to find. I usually approach this situation by only displaying matching records (as in for example iTunes).

cf_sorTable currently has no built in record filtering although that will probably be a part of the next release. You can certainly use it in combination with a form, and just use cf_sorTable to display the matching query results. Example here: http://www.tramper.co.nz/index.cfm?&view=tracksearch&key=&tracksearchform=form&days=2&grade=4&gradeOrEasier=Yes&location=[User]&range=-1&staticKeys=&layout=table&sortBy=gradeId&sortDirection=asc&offset=1
jessie said on 4/3/2007 at 3:11:04 AM:
How can I build a url with some data from the recordset, not only the value of the column? i.e. in a row I would like to put the id and the price of a product but I would like to show the name of the product in the link... thanks