Panorama > Code > Generating & Analyzing HTML
 
htmldatatable
htmldatatable
HTML
,
OPTIONS

The htmldatatable statement generates an HTML table from selected data in the current database.

Parameters

This statement has two parameters:

html – field or variable that the output HTML will be place into.

options – list of options that specify what fields should be included in the table and how the table should be formatted. If there are no options supplied the statement will automatically include all fields in the database.

Description

This statement generates an HTML table from selected data in the current database. This simple example creates an HTML table from all fields in the currently select records, then opens that table in your web browser.

local cgiHTML
htmldatatable cgiHTML
filesave "table.html",cgiHTML
openurl "file://"+dbinfo("folder","")+"table.html"

The output generated will look something like this (of course depending on the contents of the current database). This table isn’t super pretty, but it sure didn’t take much programming to create.

HotelCityRateUnitsPhoneStarsDescription
Aspen SquareAspen85.00105925-10004The center of activities in downtown Aspen. Luxury condominiums with kitchens, fireplaces, color TV, private balconies, daily maid service, underground parking, heated pool, Jacuzzi and saunas.
Best Western Aspenalt LodgeAspen52.0035927-31913Riverfront rooms (on the Frying Pan River) just 16 miles from Aspen/Snowmass. Seven restaurants nearby. Deluxe Continental Breakfast.
Boomerang LodgeAspen72.0033925-34163Nicely landscaped grounds. Some fireplaces. Balconies or patios. Cable TV, radios, phones. Kitchens and laundry. Sauna, whirlpool, heated year round pool.
Innsbruck Sports MotelAspen74.0030925-29803Imaginative decor, some refrigerators. Cable TV, phones, year-round heated pool. No pets.
Limelite LodgeAspen62.0060925-30253Quality accommodations at reasonable rates in the heart of Aspen. Next to beautiful Wagner Park, 1 block to Wheeler Opera House, unique shopping and many fine restaurants. Heated Pools, HBO, in-room refrigerators. Guest laundry on premises.

Table Options

To customize the appearance of the table you’ll need to specify some options in the second parameter of the htmldatatable statement. The options are specified as assignments, similar to the options in many HTML tags. Here is a basic example (remember that { and } can be used as quote characters).

htmldatatable cgiHTML,{border=0 font="Verdana" fontsize=-1}

The available option tags are described in the following sections.

Table Field Layout Options

There are four options that allow you to specify what fields will be included in the table and how they are titled and arranged: column, title, width and align.

This example shows these option in use to create a table with four columns: Hotel, Phone, Rate and Rating. (Note: In this example the options are shown formatted on separate lines for clarity, but this formatting is not necessary.)

htmldatatable cgiHTML,|||
  column="Hotel"
    title="Hotel"
    width=200
    align=left
  column="Phone"
    title="Phone"
    width=70
    align=center
  column={pattern(Rate,"$ #,.##")}
    title="Rate"
    width=70
    align=right
  column={rep("*",Stars)}
    title="Rating"
    width=50
    align=right
|||

Here’s the table rendered by this procedure:

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

Table Font, Font Size and Color

There are four “template” options that allow you to specify how text will be displayed within the table.

Table Borders and Spacing

These options that allow you to specify borders and spacing within the table. 

Table Background Colors

The color option allows you to specify a background color for the table.

htmldatatable cgiHTML,|||
  color="FFFFCC"
  column="Hotel"
    title="Hotel"
    ...

In this example the background color has been set to pale yellow.

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

If you include two, three or more colors these colors will alternate as each row is rendered.

htmldatatable cgiHTML,|||
  color="EEFFFF"
  color="CCFFDD"
  column="Hotel"
    title="Hotel"
    ...

In this example the rows alternate between light blue and green.

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

The colorpattern option customizes the pattern used to alternate between colors. For example the option colorpattern="1112" will render three rows in the first color, then one row in the second color.

htmldatatable cgiHTML,|||
  color="EEFFFF"
  color="CCFFDD"
  colorpattern="111222"
  column="Hotel"
    title="Hotel"
    ...

This pattern produces three rows with a light blue background then three with a green background

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

The titlecolor option allows you to specify a separate background color for the title row, while the titlefontcolor option specifies the color of the title text.

htmldatatable cgiHTML,|||
  titlecolor="000099"
  titlefontcolor="FFFFFF"
  color="EEFFFF"
  color="CCFFDD"
  colorpattern="111222"
  column="Hotel"
    title="Hotel"
    ...

This example displays the title with white text on a blue background.

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

Table Sort Order

If you want the table to appear in sorted order you can use one or more sort options.

This modified version of the DisplayTable procedure sorts twice. First the database is sorted by the Stars field (rating), from highest rating to lowest. Then within each rating the hotels are sorted by rate, again from highest to lowest.

htmldatatable cgiHTML,|||
  sort=Stars sortdirection=down
  sort=Rate sortdirection=down
  titlecolor="000099"
  titlefontcolor="FFFFFF"
  color="EEFFFF"

You might think that instead of including the sort options in the table specification you could simply sort the database in the procedure, like this.

field Stars sortdown
field Rate sortdownwithin
htmldatatable cgiHTML,“ ... ”

In fact this will work if the table is displayed all on one page. However if the table may split into multiple pages (see “Splitting a Long Table into Multiple Pages” below) then the sort options must be included in the table specification. If they aren’t the additional pages may not be sorted properly.

Linking Individual Table Rows to Detail Pages

In many applications a table is a jumping off point for additional pages. For example a phone list might link to individual address pages, while a product catalog table would probably have links to detail pages for each product listed. This section explains how to set up links from a table to individual pages.

There are four table specification options for setting up the link between the table and the detail pages. 

In addition to setting up these four options, you also need to add at least one pair of <link> and </link> tags to either a column definition (htmldatatable) or a template (htmldatamerge). Here’s an example of how these tags could be added to an htmldatatable specification.

column="<link>+Hotel+</link>"

Here’s an example of how these tags can be added to the template for htmldatamerge. The <link> and </link> tags must be placed inside curly braces { and }.

“<h2>{<link>+Hotel+</link>}</h2>
{pattern(Rate,"$#,.##")}<br>{Phone}<hr>”

Let’s go back to the procedure we created in earlier section for rendering a table. By adding the link tags shown below, we can add links to detail pages from each line of the table.

htmldatatable cgiHTML,“
  color="EEFFFF"
  color="CCFFDD"
   colorpattern="111222"
   linkProcedure="DisplayRecord"
   linkOptions="target=new"
   linkField="City"
   linkField="Hotel"
   column="<link>+Hotel+</link>"
       title="Hotel"
       width=200
       align=left
   column="Phone"
       title="Phone"
       width=70
       align=center
   column={pattern(Rate,"$ #,.##")}
       title="Rate"
       width=70
       align=right
   column={rep("*",Stars)}
       title=Rating
       width=50
       align=right”

With this modification, each hotel name in the table now becomes a link to a detail page. (Note: Clicking on the links in the table below will not work because this page is not connected to a web server, however, they do show what the links look like.)

HotelPhoneRateRating
Aspen Square925-1000$ 85.00****
Best Western Aspenalt Lodge927-3191$ 52.00***
Boomerang Lodge925-3416$ 72.00***
Innsbruck Sports Motel925-2980$ 74.00***
Limelite Lodge925-3025$ 62.00***
Sands Motel366-3581$ 22.00*
The Molly Gibson Lodge925-2580$ 75.00****
Ullr Lodge769-7146$ 48.00***
Hotel Jerome920-1000$ 295.00***
International Hotel555-9876$ 129.50**
Snowflake Inn925-3221$ 59.00****

When a form is linked to a table, the server automatically embeds a record ID into the form so that when the Submit button is pressed Panorama will know what record to update.

Splitting a Long Table into Multiple Pages

By default a web table displays all of the selected records in the database. If there are hundreds or thousands of records selected, this can make the table very unwieldy (and slow to load). Fortunately the Panorama Enterprise Edition Server can automatically split a large table into manageable pages, with links so you can navigate to different sections of the table. Here’s an example of a long table that has been split into multiple pages. A search for cities containing boulder in the name has turned up 38 matching records. This table has been configured to display a maximum of 15 records so the table is split into three separate pages.

The Panorama server has automatically generated a “navigation bar” at the top and bottom of the table that allows you skip from page to page.

The options below control the maximum table size and the generation of a navigation bar. In most cases you can simply set the recordsperpage option and omit the rest of the options, simply using the default values.

Displaying an Empty Table

If the database selection is empty (no records match the selected criteria) the server will display an empty table, like this:

HotelPhoneRateRating
 
No records matched your selection criteria.
  Press your browser's Back button to try another selection.  
 

The message displayed in the empty table can be customized with the emptytablemessage option. You can include any text you like in this option, including HTML tags.

htmldatatable cgiHTML,|||
    emptytablemessage={<center>
        Sorry, no hotels match your search!<b>
        Press the <b>Back</b> button to try again.}
		|||

You could even include a link to a new search form as part of the message.

Table HTML Layout

The font, border, spacing and background color options give you almost complete control over the appearance of the table. If you need even more control there are four “template” options that allow you to specify HTML templates for each component of the table.

Consult an HTML book or web site for more information about the <table>, <tr>, <td> and <th> tags.

See Also

History

VersionStatusNotes
10.0No ChangeCarried over from Panorama 6.0.