Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Click column heading to sort, click again to sort in other direction.

Alternate Sorting

Click column heading to sort, click again to sort in other direction.

by  TruthInSatire  Posted    (Edited  )
How do you click on a column heading to change the sort order of the report, then click again to sort the other direction?

This is a pretty common problem, I think everyone has run into it at least once. It's pretty simple to make a report sortable by clicking a heading. Just pass a url variable with the column you want to sort on. It's the clicking again to reverse the sort order that gets tricky. This cfscript will help do that. Like all my other FAQ's there isn't that much code and a ton of comments. I try to highlight the actual code.
Code:
[color gray]<!--- set the default column to sort by. here ours is called Lname --->[/color]
[b]<cfparam name = "session.lastCol" default = "Lname">
<cfscript>
	function changeOrder(colName){[/b]
		[color gray] /*If this is the first time the page is loaded we have to be sure to change the default column to "DESC".  Otherwise the first time a user clicks the link to sort it the other direction nothing will happen.*/[/color]
		[b]if(not isdefined("url.sortCol")){[/b]
			[color gray]/*If the default column name and the name passed to the function are the same set the next sort order to "DESC"*/[/color]
			[b]if(session.lastCol eq colName){
				newOrder = "DESC";
			}else{
				newOrder = "ASC";
			}[/b]
			[color gray]/*return the query string that will sort the query. In this example the return may look like sortCol=Lname&sortOrder=DESC*/[/color]
			[b]return "sortCol=" & colName & "&sortOrder=" & newOrder;
		}else{[/b]
			[color gray]/*Check to see if the user is clicking on the same column to change the sort order.*/[/color]
			[b]if((session.lastCol eq url.sortCol) AND (colName eq url.sortCol)){[/b]
				[color gray]/*change the sort order in the link*/[/color]
				[b]if(url.sortOrder eq "ASC"){
					newOrder = "DESC";
				}else{
					newOrder = "ASC";
				}
				return "sortCol=" & colName & "&sortOrder=" & newOrder;
			}else{[/b]
				[color gray]/*if the user clicked a different heading to sort by we must change the last sort column held in the session variable and change the next sort order to DESC if the column is not a clicked column the sort order will go back to ASC*/[/color]
				[b]if(colName eq url.sortCol){
					session.lastCol = colName;
					newOrder = "DESC";
				}else{
					newOrder = "ASC";
				}
				return "sortCol=" & colName & "&sortOrder=" & newOrder;
			}
		}
	}
</cfscript>[/b]
[color gray]<!--- Sample query--->[/color]
[b]<cfquery>
	SELECT		Lname, title, empNumber
	FROM		myTable
	ORDER BY[/b]
			[color gray]<!--- if this is the first time the page loads it will sort on the column you set as the default in ASC order.  If the url variable exists it will sort by them instead. --->[/color]
				[b]<cfif isdefined("url.sortCol")>
					#url.sortCol# #url.sortOrder#
				<cfelse>
					#session.lastCol# ASC
				</cfif>
</cfquery> [/b]

Here we have 3 columns, Lname, positionTitle, and EmpNumber but you can have any number of columns with any valid name.

<cfoutput>
<tr>
  <td><a href = "yourpage.cfm?[color red]#changeOrder('Lname')#[/color]">Name</a></td>
  <td><a href = "yourpage.cfm?[color red]#changeOrder('positionTitle')#[/color]">Title</a></td>
  <td><a href = "yourpage.cfm?[color red]#changeOrder('EmpNumber')#[/color]">Employee Number</a></td>
</tr>
</cfoutput>
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top