Sometimes a record set will need to combine data from different tables. We can implement this by creating a view in the database (example 2) or by writing the join directly within the table attribute. Here we have also added a clause to restrict the records to those below 45° south.

Features of this example:

The tables are joined within the table attribute, so we don't need to set up a database view.
A where attribute controls the subset of records to be displayed.
As pointdescriptioncode appears in both tables, we must qualify the column with the table name.
The expression attribute is used for latitude and longitude to call a user-defined function.
Query caching using the cachedwithin attribute.
The 'glacier_lavender' skin.
Locale-specific formatting for 'English (US)' (change this using the selector at bottom right).

Source code for this example

<!--- 
Note that the database for this example must be set up as a datasource first. The Microsoft Access database is located in the examples folder. Place the datasource name in the request.dsn variable.

Microsoft Access is not recommended for web purposes, and is provided here for demonstration purposes only. Better performance will be achieved with enterprise databases.
--->

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

<cfif structKeyExists(url, "relatedId")>
	<cfoutput><p>Record selected: #htmlEditFormat(url.relatedId)#</p></cfoutput>
</cfif>

<esw:sortableplus 
	link="example3.cfm?relatedid=##relatedid##"
	table="namedata inner join pointdes on namedata.pointdescriptioncode = pointdes.pointdescriptioncode"
	dbms="Microsoft Access"
	datasource="#request.dsn#"
	sortBy="Latitude"
	key="relatedid"
	style="width : 600px"
	where="latitude < -45"
	cachedwithin="#createTimeSpan(0, 0, 10, 0)#"
	skin="glacier_lavender"
>

	<esw:column
		column="relatedid"
		caption="ID"
		type="numeric"
		sortDirection="asc"
	/>
	
	<esw:column
		column="pointname"
		caption="Name"
	/>
	
	<esw:column
		column="pointdes.pointdescriptioncode"
		caption="Code"
	/>
	
	<esw:column
		column="pointdescriptionname"
		caption="Description"
	/>
	
	<esw:column
		column="Latitude"
		type="numeric"
		expression="##caller.dmsFormat(latitude, 'NS')##"
	/>
	
	<esw:column
		column="Longitude"
		type="numeric"
		expression="##caller.dmsFormat(Longitude, 'EW')##"
	/>
	
</esw:sortableplus>

<cfscript>
	function dmsFormat(value) {
		var temp = abs(value);
		var d = int(temp);
		var m = 0;
		var s = 0;
		var signIndicator = arrayLen(arguments) gt 1;
		var result = "";
		temp = (temp - d) * 60;
		m = int(temp);
		temp = (temp - m) * 60;
		s = int(temp);
		result = "#d#&##176; #numberFormat(m, "00")#&##8242; #numberFormat(s, "00")#&##8243;";
		
		if ( signIndicator )
			if ( value lt 0 )
				result = result & " " & right(arguments[2],1);
			else 
				result = result & " " & left(arguments[2],1);
		else
			if ( value lt 0 )
				result = "-" & result;
		return result;
	}
</cfscript>

To run the example, you will need to:-

Add the Microsoft Access database as a ColdFusion datasource.

Modify the path in the cfimport statement at top.