Database Results Column Sorting

Introduction

Many applications allow you to sort information extracted from a database. Typically, this is done using the column headings of the results table. Using the technique described here, you can use the column headings of the Database Results Wizard to sort the database results. You can sort on multiple columns by simply clicking on a new column headings. The sort order can be reversed by clicking the same column a second time.

Here's how it's done (This procedure requires JavaScript to be enabled on the user's browser.).

Technique

The ASP script presented here modifies the sort criteria each time the users selects a sort column and creates JavaScript on the DRW page to populate the DRW search form with the new orderby clause and then submit the query form.

Implementation

The procedure may sound complicated but it isn't. You'll need three things;

an ASP script to calculate the 'Order By' clause (below)
a custom DRW query that uses the 'Order By' clause.
a button or hyperlink that executes the query.

ASP Script

Place this above the <html> tag on your query page;

<%
const orderBy_default = ""
const orderBy_field = "orderby"
const orderBy_form = "search"

function orderBy(field)
dim order, fields
order = request(orderBy_field)
If order = "" Then order = orderBy_default 
fields = split(order, ",", -1)
if fields(0) = field OR fields(0) = field & " asc" then
fields(0) = field & " desc"
elseif fields(0) = field & " desc" Then
fields(0) = field
else
fields(0) = field & "," & fields(0)
end if
orderBy = "document." & orderBy_form  & "." & orderBy_field & ".value ='" & join(fields, ",") & "';document." & orderBy_form & ".submit();void(0);"
end function
%>
<html>

DRW Custom Query

Create a custom DRW query with an order by clause. In the example below, ::orderby:: is replaced by a database field name and a sort order (asc or desc) when the query is run.

SELECT * FROM Table Order By ::orderby::"

Set a default value in the DRW for the orderby clause. For example;
LastName asc

Modify the 1st line of the ASP script (orderBy_default) to match your default orderby clause. For example:
const orderBy_default = "id asc"

NOTE: If an MS-Access database field name has spaces (e.g. Last Name), always enclose the field name in brackets (e.g. [Last Name])

The Interface

The DRW will automatically create a search form using the 'orderby' query parameter as well as any other parameters you include in the query.

Query Form - Name the form 'search' and make the input type of the 'orderby' field "HIDDEN". For example;

<form name="search">
<input TYPE="HIDDEN" NAME="orderby" VALUE="<%=Request("orderby")%>" size="20">

NOTE: If you use different names, you must change line 2 and 3 in the script (orderBy_form and orderBy_field) and in the DRW custom query (::orderby::).

There are two ways to present the sorting option. You can use buttons or a hyperlink. The example below creates a sorting option for a database field called 'LastName'.

Hyperlinks - make the column heading created by the DRW into a hyperlink and enter a script as the URL: javascript:<%=orderBy("LastName")%> where 'LastName' is the column you want to sort. The resulting link will look like this in HTML mode;

<a href="javascript:<%=orderBy("LastName")%>">LastName</a>

Buttons - replace the DRW column headings with a button. Each column can have it's own button and each button is placed in it's own form. Simply enter a script for the onclick event of the button in HTML mode: onclick="<%=orderBy("LastName")%>" where 'LastName' is the column you want to sort. The resulting button will look like this in HTML mode;

<form>
<input type="button" onclick="<%=orderBy("LastName")%>" value="LastName">
</form>

Back to Stephen Travis' Thingumajig

This page was last reviewed on: September 16, 2006