SorTable+

SorTable+ is a ColdFusion custom tag that queries a database and displays a table of records. Results are split into multiple pages and may be sorted or filtered. SorTable+ is designed to handle large record sets. It does this be by shifting responsibility for selecting, filtering, and sorting data to the database server and retrieving only the records required for display.

Version:	1.0 (Release notes)
Date:	20070908
Required:	CFML server: Adobe ColdFusion 5.0 or greater, or BlueDragon 6.1 or greater Database server: Microsoft Access, or Microsoft SQL Server, or MySQL, or PostgreSQL

Contents

1. Installation
2. Standard usage
3. SorTable+ tag attributes
1. Interface control attributes
2. Record set attributes
3. Other attributes
4. Column tag attributes
1. Basic attributes
2. Button type attributes
3. Image type attributes
4. Other attributes
5. Column types
6. Working with forms

Back to top

1. Installation

Copy the customtags, resources, and examples folders to the root of your website.

If you choose to place these files in a different location, you will need to adjust the cfimport taglib attribute and the SorTable+ resourcePath attribute accordingly.

Note that running the examples will require the creation of database tables. It is not necessary to set these up in order to understand the examples. The examples folder should be removed from production websites.

Back to top

2. Standard usage

A typical implementation of SorTable+ using the most common attributes:

<cfimport prefix="esw" taglib="/customtags/eswsoftware/">

<esw:sorTablePlus
	dbms="dbms" 
	datasource="datasource" 
	table="table"
	key="primary_key_column"
	cachedwithin="timespan"
	link="URL"    
	sortBy="initial_sort_column"   
>
	
<esw:column
	column="column_1"
	caption="column_1_caption"
	type="numeric"
/>

<esw:column
	column="column_2"
	caption="column_2_caption"
	type="string"
/>

<esw:column
	column="column_3"
	caption="column_3_caption"
	type="date"
	sortDirection="desc"
/>	

</esw:sorTablePlus>

Back to top

3. SorTable+ attributes

Interface control attributes

Attribute	Req/Opt	Default	Description	Example
link	Optional		This is the URL of the page your records should link to, perhaps to view or edit the record details. This attribute is evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	link = "editrecord.cfm?id=##id##"
newRecordLink	Optional		The hyperlink associated with newRecordPrompt.	newRecordLink = "newrecord.cfm"
newRecordPrompt	Optional		This prompt occupies the first line in the record set and may be used in combination with newRecordLink to add new records or for any purpose.	link = "Add a new record"
rowNumbers	Optional	No	Yes: add a column of row numbers at left.	rowNumbers
rows	Optional	25	The number of records to appear on an individual page.	rows = "10"
showFilter	Optional	Yes	Yes: show filter field above the record set No: hide filter field	showFilter = "false"
skin	Optional	glacier	Select the skin, or visual appearance. A skin is simply a folder containing a stylesheet and a few images. Options are: finestre glacier glacier_burntorange glacier_cornflower glacier_crimson glacier_lavender glacier_lime Developers may create their own skins by copying an existing skin folder.	skin = "finestre"
sortBy	Required		This is the name of the column you want the record set sorted by when the page first loads.	sortBy = "dateUpdated"

Record set attributes

Attribute	Req/Opt	Default	Description	Example
cachedwithin	Optional		If specified, the query results will be cached for the specified time span. The timespan may be generated with the ColdFusion function createTimeSpan().	cachedwithin="#createTimeSpan(0,1,0,0)#"
datasource	Required		The name of a datasource that has been set up in ColdFusion Administrator.	datasource = "myDSN"
dbms	Optional	SQL Server	The type of database. The following values are supported: Access Microsoft Access Microsoft SQL Server MSSQL MySQL Postgres PostgreSQL SQL Server T-SQL Transact-SQL Other values are accepted but may not be handled correctly.	dbms = "SQL Server"
key	Optional		This is the primary key of your table or the name of any column that will identify a row uniquely. Queries may return excess records when there are sort order ties. Supplying a primary key will resolve this. You will also need to set this attribute if you are referring to the key in your link without including it as a column.	key = "userId"
table	Required		The database table or view containing records. You may also specify a SQL JOIN clause here. For example: .	table = "users" table = "employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID"
where	Optional		A SQL WHERE clause, used to reduce the record set to a subset of all the records in the table, view, or join.	where = "deleted = 0"

Other attributes

Attribute	Req/Opt	Default	Description	Example
anchor	Optional		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.	anchor = "results"
expressionColumns	Optional		If you referred to any database table columns inside expressions without including them as a column in your display table or specifying them as the key, then you should list them in this attribute.	expressionColumns = "firstName,lastName"
id	Optional	sorTablePlus	The JavaScript ID. If cf_sorTablePlus appears more than once on a page, then this attribute should be set uniquely for each instance.	id = "results"
locale	Optional	#getLocale()#	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. All ColdFusion locales are allowed, but only a selection are supported. It is a straightforward task to add support for desired locales.	locale = "German" locale = "French (Belgium)" locale = "en_GB"
resourcepath	Optional	/resources/	This attribute specifies the web path to the client resources (stylesheets and images) that control the appearance of the table.	resourcepath = "/assets/resources/"
suppressFormTags	Optional	No	SorTable+ adds HTML form elements to the page in order to enable the filter field to function. If SorTable+ is placed inside an HTML form, this attribute should be used to prevent nested form tags. SorTable+ will detect and adjust for cfform or a TerraForm forms automatically. Yes: do not add HTML form tags No: add HTML form tags, unless cfform or TerraForm form detected	suppressFormTags
thisPage	Optional	#cgi.request_uri#?	This is the web path from the current page to the current page. If the sorting, filtering, and paging links do not work, then this attribute is likely set incorrectly. Note that that SorTable+ will attempt to add variables to the end of the URL, so you will need a ? in, or on the end of, the value.	thisPage = "results.cfm?key=#form.key#"
any other attributes	Optional		Any other attributes are passed through to the generated HTML table tag.	border = "1" cellspacing="2" style="background-color : gray"

Back to top

4. Column attributes

Basic attributes

Attribute	Req/Opt	Default	Description	Example
caption	Optional	#attributes.column#	The caption that will appear at the top of the column.	caption = "Date updated"
column	Optional		The name of the record set column linked to this HTML table column for sorting and filtering.	column = "dateUpdated"
link	Optional		Allow this column to link to a different URL from the row link. This attribute is evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	link = "editrecord.cfm?id=##id##"
mask	Optional		Formatting mask for types: date, datetime, numeric, and time.	mask = "yyyy-dd-mm"
sortable	Optional	Yes	Yes: allow the user to sort buy this column No: prohibit sorting by this column	sortable = "false"
type	Optional	string	The type of data in the column. Used to manage filtering and visual formatting. boolean button checkbox date datetime email image numeric price string time url	type = "numeric"
any other attributes	Optional		Any other attributes are applied to each table cell in the column. These attributes are evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	style = "color : white; background-color : black"

Button type attributes

The following attributes apply where type = "button" only.

Attribute	Req/Opt	Default	Description	Example
buttonLabel	Optional	##value##	The label to appear on the button. This attribute is evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	buttonLabel="Edit"
buttonType	Optional	button	The HTML button type: button reset submit	buttonType="submit"

Image type attributes

The following attributes apply where type = "image" only.

Attribute	Req/Opt	Default	Description	Example
imageAlt	Optional		HTML alt value for the image. This attribute is evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	imageAlt="##name##"
imageHeight	Optional		HTML height value for the image.	imageHeight="50"
imageSrc	Optional		HTML src value for the image. This attribute is evaluated. You may embed column values by wrapping in escaped (doubled) pound signs.	imageSrc="/images/##imageId##.jpg"
imageWidth	Optional		HTML width value for the image.	imageWidth="16"

Other attributes

Attribute	Req/Opt	Default	Description	Example
displayColumn	Optional	#attributes.column#	The name of the record set column linked to this HTML table column for display. If expression is provided, then the displayColumn attribute is ignored.	displayColumn = "fullName"
displayType	Optional	#attributes.type#	Used in conjunction with displayColumn, this is the type of the record set column linked to this HTML table column for display.	displayType = "date"
expression	Optional		A ColdFusion expression to be evaluated and displayed instead of the record set value. Any pound signs (#) in the expression should by escaped (doubled). The column value for the current row may be referred to as ##value##. The values of other columns for the current row may be referred to by their column names.	expression = "##firstName## ##lastName##" expression = "##round(value)##" expression = "##iif(value, value, de('-'))##"

Back to top

5. Column types

The column type affects the visual appearance of data in that column.

Column type	Displays as
boolean	Yes or No
button	An HTML button. The button's label and type are specified using buttonLabel and buttonType. The button's action may be controlled by adding JavaScript to the onclick attribute.
checkbox	A column of checkboxes. The values of the boxes will be the values in the column. This will usually be most effective when the column is set to the table's primary key. The checked values may be accessed by embedding the entire SorTable+ table in a form or by using the JavaScript function sorTableGetSelectedValues().
date	A date formatted for the current locale. The formatting is controlled with mask as for lsDateFormat().
datetime	A date and time formatted for the current locale. The formatting is controlled with mask. The mask section to the left of ^ controls the date formatting and is interpretted as for lsDateFormat(); the section to the right of ^ controls the time formatting and is interpretted as for lsTimeFormat(). For example: mask="yyyy-mm-dd^HH:mm:ss".
email	A linked email address. If an unlinked address is preferred, string should be used.
image	An HTML image. The image src, height, width, and alt attributes are specified using imageSrc, imageHeight, imageWidth, and imageAlt.
numeric	A numeric value. The formatting is controlled with mask as for lsNumberFormat().
price	A price value. The formatting is controlled with mask as for lsEuroCurrencyFormat().
string	A plain string value, displayed without modification.
time	A time value. The formatting is controlled with mask as for lsTimeFormat().
url	A linked URL. If an unlinked URL is preferred, string should be used.

Back to top

6. Working with forms

Embed SorTable+ within a form to allow the selection and submission of multiple records. Example 4 illustrates selecting multiple records and using the JavaScript function sorTableGetSelectedValues() to retrieve a list of keys for the selected rows.

Add a custom form to ffilter the data yourself. Example 5 illustrates this technique, with the form being used to manipulate the value of the SorTable+ where attribute.

Copyright 2007 ESWsoftware