Navigation Letters Server Behavior*

This behavior will allow you to navigate through a recordset that contains a Repeat Region behavior or a Horizontal Looper behavior. It should be used on pages that show only partial recordsets. It will give you a set of links like this:

A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

The delimiter that's used between the links is completely user-defined and can be anything. If nothing is input, it defaults to a single space between the links. Styles work with the links, as well. Simply select the behavior in the Server Behaviors >> DWTeam >> Recordset Navigation menu, or the placeholder on the page, or the code in source view and apply a style to it.

There are three versions of this server behavior.

• The first version is named simply Navigation Letters. It is available for ASP and ColdFusion.
• The second version is named Navigation Letters - Access. It is available for ASP and ColdFusion
• The third version is named Navigation Letters. It is available in PHP.

The reason for the three different behaviors that do the same thing is this: the first version runs more quickly for ASP and ColdFusion with database servers like MS SQL Server, whereas the second version was necessary to maintain compatibility with MS Access. The second version will actually work with any database, providing you know the SQL code for LIKE for your RDBMS. The third version is a PHP-only version that works with MySQL and other databases.

Navigation Letters -- Original version

The server behavior requires that you set up a recordset beforehand with a variable in place that will activate the links properly. This variable is named tfm_navLetter and should be placed in the SQL query builder like this:

ASP variables defined in the recordset definition dialogue box

Cold Fusion variables defined in the recordset definition dialogue box

Then in your query, make sure you have a clause that uses the variable:

SELECT * FROM mytable WHERE tfm_navLetter

or

SELECT first, last, address, state FROM mytable WHERE state = 'NY' AND tfm_navLetter

The variable will also take care of the ORDER BY clause, so make sure not to include an ORDER BY clause in your SQL statement.

Navigation Letters -- Access version

This version requires that you define your own query and set your own order by clause. The procedure for applying the SB is a little different, so make sure you address the differences in the query and variables used.

The server behavior requires that you set up a recordset beforehand with a variable in place that will activate the links properly. This variable is named navLetter and should be placed in the SQL query builder like this:

ASP variables defined in the recordset definition dialogue box

Cold Fusion variables defined in the recordset definition dialogue box

Then in your query, make sure you have a clause that uses the variable:

SELECT * FROM mytable
WHERE mycolumn LIKE 'nav_Letter%'
ORDER BY mycolumn

or

SELECT first, last, address, state
FROM mytable
WHERE state = 'NY'
AND last LIKE 'nav_Letter%'
ORDER BY last

Notice that the variable contains a percent sign to indicate a fuzzy match in the data. This allows the query to find all matches of that letter. Also, an ORDER BY clause is used that orders the data alphabetically. For the default value you can use a % instead of the letter A if you want to include all records the first time a user hits your page.

Navigation Letters -- PHP version

This version requires that you define your own query and set your own order by clause. The procedure for applying the SB is a little different, so make sure you address the differences in the query and variables used.

The server behavior requires that you set up a recordset beforehand with a variable in place that will activate the links properly. This variable is named navletter and should be placed in the SQL query builder like this:

ASP variables defined in the recordset definition dialogue box

Then in your query, make sure you have a clause that uses the variable:

SELECT * FROM mytable
WHERE mycolumn LIKE 'navletter%'
ORDER BY mycolumn

or

SELECT first, last, address, state
FROM mytable
WHERE state = 'NY'
AND last LIKE 'navletter%'
ORDER BY last

Notice that the variable contains a percent sign to indicate a fuzzy match in the data. This allows the query to find all matches of that letter. Also, an ORDER BY clause is used that orders the data alphabetically. For the default value you can use a % instead of the letter A if you want to include all records the first time a user hits your page.

*(not available in JSP)