Sortable Table ColdFusion Custom Tag with jQuery UI

A while back I created a sortable tables custom tag using the WebFX sortable table JavaScript. While this worked pretty well, there were limitations to the sorting and limitations to my tag itself. For no particularly good reason (well other than all the good things I have heard about jQuery), I decided to rebuild this using the new JQuery UI. I am happy to say that, with a little work (and some helpful advice from Todd Sharp), the new custom tag (actually tags) is ready and offers some new and improved features over the old. There are still some limitations I am working on, but I am making the initial release available. You can download the zip attached to this post...so let's look at how it works.Step 1 - Get jQuery
The first thing you need is to download both jQuery JavaScript and the ui.sortabletable.js from the jQueryUI scripts. You can place those files anywhere as the path to the scripts is configurable, just make sure they are all in the same location. You also need the themes which go in the same directory under /themes/. Currently I use the "Flora" theme as that seemed to be the only one that worked. In the future, I hope to make this aspect configurable. I also tried to get the widget shown in the examples to work, but it wouldn't with the tablesorter.js version available with the provided 1.0 download. After much frustration, I figured out that their examples are using a different version where the zebra effect functions properly, which I have included in the download. Hopefully that will be rectified soon.

Using the Tag
To use the tag, you nest the columns tag within the sortabletables tag. The main tag has one required parameter, the query you want to display (query). Optionally you can tell the tag to include the jquery script (includeJQueryScript), the tablesorter.js script (includeUIScript) or the CSS theme (includeCSSTheme). If you do any of these, you will need to provide the other optional argument for the path to the jQuery scripts (jsPath). It is important to note that the tag is smart enough to know when it has already included the scripts and will not do so on any subsequent requests. This is done through setting some caller variables, including one for a tableid; the tableid will allow you to include multiple sortable tables on a page without them conflicting with each other.

Once you have established the table settings, you can include any columns using the cf_sortablecolumn tag. This tag has seven parameters, all of which are optional (though, if you left them all out, it serves no purpose):

column: this is the name of the query column that you would like displayed. Columns are displayed in the order your specify.

title: this is the header text for the column which, if not provided, defaults to the query column name.

format: this allows you to specify how the column is displayed using ColdFusion formatting functions such as dateFormat(), dollarFormat(), etc. It supports any built-in or custom UDF. It is important to note however that the date sorting appears to default to standard string formatting unless you give it both date and time in the format "dateFormat(dateAdded,'mmm dd, yyyy ') & timeFormat(dateAdded,'h:mm tt')". I will look into how this can be customized.

sort: this will set a column to auto-sort when the table initially loads. When provided, allowable values are "asc" or "desc". Columns are sorted in the order they appear, which means that if firstName is first and set to "asc" and lastName is second and set to "asc", then it will sort by firstName and then lastName.

disable: this will disable sorting on that column. It is false by default - meaning that sort is enabled by default.

pre: this is any HTML that will be included in each table cell for this column before the query value. The pre parameter allows you to provide multiple query column values that will be replaced at runtime using the format {column_name}, allowing you to do things like include query parameters in a URL query string. You can also include just the pre without any column value as in the example below.

post: this is any HTML that will appear after the included column value. It does not currently support the included column values as in the pre parameter.

Example
The code below outputs the following example. Notice that it auto sorts on the clickCount and that the dateAdded has been formatted as specified. Also notice that the last column is disabled and actually does not include any query column value. It does however provide two query column values within the pre tag that are replaced at runtime.

<cf_sortabletable jsPath="jquery/" query="#getProjects#">    <cf_sortablecolumn column="title" title="Project Name" />    <cf_sortablecolumn column="clickCount" title="Clicks" sort="desc" />    <cf_sortablecolumn column="dateAdded" title="Date Added" format="dateFormat(dateAdded,'mmm dd, yyyy ') & timeFormat(dateAdded,'h:mm tt')" />    <cf_sortablecolumn disable="true" pre="<a href='#CGI.SCRIPT_NAME#?id={resourceid}&clicks={clickCount}'>edit</a>" /> </cf_sortabletable>

Conclusion
I am pretty happy with the way this tag has turned out and with its improvements over my original tag. Hopefully some of you find this tag useful (I don;t think too many people used the old one...or at least I never heard about it). I may look into working other jQueryUI elements into other tags in the future. If you find bugs or would like to request enhancements, please comment here or email me. Also, if you find this helpful, you could always visit my wish list :).

Download the attachment.

Nice work Brian! I know it may sound trivial, but what if you added a 'sortOrder' attribute... so I could sort in a diff order then the column order?

Check out this Unobtrusive Client-Side Table Sorting, Filtering, and Paging Example.
http://www.javascripttoolbox.com/lib/table/

This javascript library requires minimal changes to your existing HTML tables, can be integrated in less than 5 minutes, allows filtering, grouping, paging, automatic alternate row coloring, skinnable buttons, doesn't require special customtags and can be used on any static website.

Have you had a chance to check out the tablesorter 2 plugin for JQuery? I am not sure If it is the same thing or not because I am new to Jquery but this plugin is awesome and really easy to use. I was thinking of writing up a tutorial on it. Let me know! http://tablesorter.com/docs/

I'm somewhat familiar with the jQuery script. I thought it was nice, but it lacks grouping, filtering and paging. When comparing the two libraries, I chose the one with more features.

Check out the "client-side table filtering" on this demo...
http://www.javascripttoolbox.com/lib/table/examples.php

Even the first example (at the above link):
- sorts the data
- sort 2 distinct groups separately
- sorts on a checkbox field status
- doesn't sort the static footer or group rows

I haven't evaluated the jQuery plugin enough to determine if it is capable of doing this or not... please let me know as I'm always looking around to both find and use the best scripts.

James:

One issue with the example you linked to. The filters options are not modified when a filter is applied. For example filter on "bob" and the index column filter should limit it's options to only the indices that are applicable to "bob".

PS - The jQuery script can allow for paging.

Brian: You selling ad's on your captcha now? Current text = izod.