Take Your Web Site to New Levels

Formatting Your Database Results Into Dynamically Sorting Columns

One of the great things about programs like Microsoft Outlook and Windows Explorer is the ability to click on column headings and change the sorting of column data on the fly. This tutorial will show you how to accomplish the same thing using only the FrontPage Database Results Wizard AND a little FrontPage Magic.bv

This technique is way cool. Check out the following table. You can click on any of the hyperlinked headings to sort the table by that column.

Let's Recreate This Example

For this tutorial, we'll use the customer's table from the Northwind Database. Essentially, this table is created in three steps:

1. Walk through the basic Database Results Wizard to create the table
2. Just before finishing, use the back button to return to Step 2 to add a little custom SQL
3. Create the hyperlinks to change the sorting preference.

So let's get started. On a blank page, select Insert-Database-Results. In Step 1, choose the Northwind database from the list of database connections.

In Step 2, select the Customers table.

Click the next button to move to Step 3.

In this step, we need to set a sort order so click the More Options button.

Now click the Ordering button.

Set the Sort Order to ANY of the fields for the moment. We'll be altering this later so just pick any field (CustomerID in this example).

Click Ok to close the Ordering dialog and again to close the More Options dialog.

Now comes the FrontPage Magic. Now that you're back in Step 3, click the Back button to return to Step 2. Up until this point, as we have made selections in the DRW, FrontPage has been creating a query that will select the records based upon our selection. What we need to do now is alter that slightly For this reason, we return to Step 2 and select the Custom SQL option.

When you open the Custom SQL dialog, you'll see the SQL code that has been created so far in the DRW process.

Essentially, this query tells FrontPage to select ALL of the fields ( * ) from the Customers table and sort them in ascending order based upon the CustomerID field.

We want to change the sorting of our columns though based upon whatever column heading our visitor selects. We do this by removing the field name from the sort order and replacing it with a generic field name. We can then pass the name of any of the four columns in order to change the sort order on the fly.

You can call your sort field name anything you want. For this example we'll call it SortColumn. To add the SortColumn parameter, highlight the word CustomerID in the Custom Query window and then click the Insert Parameter button. Type SortColumn in the Form Field Name and press OK.

You'll notice now that the ORDER BY section of the query has changed to the field name ::SortColumn::

Since we only want to display a few fields in our table, replace the * with the names of the fields you would like to display. Separate each with a comma. Your Custom Query should look like the following:

Click OK to close the Custom Query dialog and then Next to move to Step 3 of the DRW. Our next step is to set a default field that you would like the table to sort by when it first opens. Click the Defaults button.

When the Defaults dialog opens, you'll see the SortColumn field listed with no default value.

Select SortColumn from the list and click the Edit Button. In the Default Value dialog, type the name of the field that you would like your report sorted by when it first opens. In this case, I've selected the CustomerID field.

Click OK to close the Default Value dialog. Your Defaults screen will change to resemble the next image:

Click OK to close the Defaults dialog and again to move to Step 4 of the DRW. Select Table - one record per row. Click Next.

In the final step of the DRW, you can split your records into small groups OR have them all display together. When you are happy with your choices click Finish to close the Database Results Wizard.

Your completed Database Results Wizard region will resemble the following:

Now Add the Hyperlinks to Make the List Sortable

If you open your page on the internet, you'll see your table with all of the data inserted. You'll notice that it is sorted based upon the CustomerID field which we selected as our default.

In this step, we'll insert a hyperlink for each column heading that will tell the page to re-load and sort based upon the link we select. Let's start with the CustomerID field.

Use your mouse to select the CustomerID heading. Right click and select Hyperlink Properties. For the page to link to, select this same page. Now click the parameters button to open the Add Parameters screen. You'll see the name of this page in the Path screen. Under the Query string section, there should be nothing listed. Click the Add button to open the Modify Parameter screen.

Type SortColumn under the Name field and the name of the field you want to sort by (in this case CustomerID) under the Value field.

Click OK to close the Modify Parameter screen. You'll now see your parameter listed in the Query String section.

Click OK to close the Hyperlink Parameters screen and again to close the Hyperlink Properties screen. You now have a column title that will reopen the page and sort the page based upon the CustomerID.

Do the same for each of the other columns, changing the parameter value in each case to match the column name. Now, when you reload the page, you can click on any of the headings and have the page automatically re-sort based upon your selection. All it takes is a little FrontPage Magic.