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.

Hotel | City | Rate | Units | Phone | Stars | Description
Aspen Square | Aspen | 85.00 | 105 | 925-1000 | 4 | The 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 Lodge | Aspen | 52.00 | 35 | 927-3191 | 3 | Riverfront rooms (on the Frying Pan River) just 16 miles from Aspen/Snowmass. Seven restaurants nearby. Deluxe Continental Breakfast.
Boomerang Lodge | Aspen | 72.00 | 33 | 925-3416 | 3 | Nicely landscaped grounds. Some fireplaces. Balconies or patios. Cable TV, radios, phones. Kitchens and laundry. Sauna, whirlpool, heated year round pool.
Innsbruck Sports Motel | Aspen | 74.00 | 30 | 925-2980 | 3 | Imaginative decor, some refrigerators. Cable TV, phones, year-round heated pool. No pets.
Limelite Lodge | Aspen | 62.00 | 60 | 925-3025 | 3 | Quality 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.

- column=formula You should include one `column=` option for each column you want to display in the table. The formula specifies what data will be displayed in the column. For text fields you can simply include the name of the field. For date fields you must use the datepattern( function, while for numeric fields you must use either the str( or pattern( function.

- title=text If you specify any titles at all you must include one `title=` option for each column in the table.

- width=number If you specify any widths at all you must include one `width=` option for each column in the table. The width is specified in points, for example `width=72` for a column that is 1 inch wide.

- align=left/center/right If you specify any alignment at all you must include one `align=` option for each column in the table. The alignment for each field may be left, center or right (for example `align=right`).

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:

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-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.

- font=font name This option specifies the font to use to display the table. (If left blank the browsers default font will be used.) On your own computer you can use any font you want because you have complete control over what fonts are installed and available for use. A web page, however, is the opposite situation since you have zero control over what fonts may be available when the page is viewed. Because of this it is usually best to pick from a restricted subset of fonts that are almost universally available. The fonts that are most commonly available are: Arial, Comic Sans MS, Courier, Georgia, Helvetica, Times and Verdana. If the font name contains spaces or punctuation it must be surrounded with quotes, for example `font="Comic Sans"`.

- fontsize=1..7/+1..+7/-1..-7 This option specifies the size of the text to be used for the table. But take note — the size is not specified in points. Instead it is specified using a special HTML text size specification. Absolute values (1–7) specify a fixed font size from extremely small (1) to huge (7). Negative values specify sizes smaller than the text size in the rest of the page, for example -2 is two sizes smaller than normal font. Positive values specify sizes larger than the text size in the rest of the page, for example +2 is two sizes larger than normal font.

- fontcolor=color By default Panorama web tables use black text, but you can use any color you want. HTML colors are defined using a hexadecimal notation for the combination of Red, Green, and Blue color values (RGB). The lowest value that can be given to one of the light sources is 0 (hex #00). The highest value is 255 (hex #FF). For example `fontcolor="0000FF"` represents pure blue, `fontcolor="00FF00"` represents pure green, and `fontcolor="FF0000"` represents pure red. Using this notation you can specify millions of different colors.

- titlecolor=color The text in the title row is normally the same color as the rest of the text in the table (see fontcolor above) but you can specify a different color, for example `titlecolor="660000"` for dark red.

#### Table Borders and Spacing

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

- border= points This option specifies the thickness (in points) of the border around each cell in the table. If the value is set to 0 then there is no border at all.

- cellspacing= points This option specifies the space between each cell in the table (in points).

- cellpadding= points This option specifies the space between the contents of each cell (text or image) and the edge of each cell (in points).

#### 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.

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-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.

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-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

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-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.

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-3221 | $ 59.00 | ****

#### Table Sort Order

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

- sort=field This option tells the server to sort the database before generating the table. If there is more than one sort option, the database will be sorted by each field in turn (for example State, then City, or Last, then First). You can include as many sort options as you like.

- sortdirection=up/down This option tell the server whether to sort in ascending (up) or descending (down) order. If more than one sort field is specified then you should also have a sortdirection option for each field. If there is no sortdirection option at all then all sorts are assumed to be ascending (up).

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. ￼￼￼￼￼￼￼

- domain=ip address This option should be set to the ip address of your web server, for example `http://www.provue.com` or `http://192.168.1.3`. You can omit this option if you have set up the domain name in the Server Configuration, which we highly recommend.

- linkProcedure=proc This option tells the server what detail page to link to. If the table is linking to a Panorama web form (see Web Forms) then this option should be set to `form~` followed by the name of the form, for example:

  `linkprocedure="form~Invoice"`

  If the table is linking to a procedure that generates the detail page then simply use the name of the procedure.

  `linkprocedure="DisplayRecord"`

  This procedure must be able use the extra parameters in the URL to locate the specific record to be updated (an example of such a URL is shown below – the procedure can call the urlSelect to process this URL format automatically, see What Record Are We Talking About?).

- linkOptions=html This option can be used to insert additional text into the link tags that are generated. For example, if you want the detail form to open in a new window, you can set `linkOptions={target=new}`. You could also use the link options to open the detail form in another frame.

- linkField=field This option specifies a field (or multiple fields) to be used to identify the link. This option is only needed for databases that are not shared — if the database is shared then the server will automatically use the shared record ID to identify the link. You may use more than one link field, but the combination of all the link fields you specify should uniquely identify a single record in the database. If our ColoradoHotels database is not shared we can uniquely identify a single record with two fields, City and Hotel, so the options should be set up this way:

  `linkField="City" linkField="Hotel"`

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.)

Hotel | Phone | Rate | Rating
Aspen Square | 925-1000 | $ 85.00 | ****
Best Western Aspenalt Lodge | 927-3191 | $ 52.00 | ***
Boomerang Lodge | 925-3416 | $ 72.00 | ***
Innsbruck Sports Motel | 925-2980 | $ 74.00 | ***
Limelite Lodge | 925-3025 | $ 62.00 | ***
Sands Motel | 366-3581 | $ 22.00 | *
The Molly Gibson Lodge | 925-2580 | $ 75.00 | ****
Ullr Lodge | 769-7146 | $ 48.00 | ***
Hotel Jerome | 920-1000 | $ 295.00 | ***
International Hotel | 555-9876 | $ 129.50 | **
Snowflake Inn | 925-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.

- recordsperpage=num This option specifies the maximum number of records that will be displayed in a single page. You can either type in a value or select from the pop-up menu.

  If this option is left blank then the Panorama Server will not automatically split the table into multiple pages and the rest of the options below will be ignored (because no page navigation bar will be generated).

  Note: If you specify a maximum number of records we highly recommend that you also set up a specific sort order for this table. This will make sure that the same order is used for each page when navigating from page to page to page.

- pagenumber=num This option specifies the page of the table to be displayed. Usually you should simply omit this option so that the first page is displayed.

- previouspagecaption=text The first and last links in the page navigation bar are usually `PREVIOUS` and `NEXT`, displayed in bold. Use this option to change the text of the link to the previous page. For example, if your web site is in Italian you might change this caption to `<b>PRECEDENTE</b>`. You can put any HTML tags you want into the caption. If you want to display an image for this link (for example a back arrow) simply use an `<img>` tag for the caption. (If this option is omitted the default is `<b>PREVIOUS</b>`.)

- nextpagecaption=text The first and last links in the page navigation bar are usually `PREVIOUS` and `NEXT`, displayed in bold. Use this option to change the text of the link to the next page. For example, if your web site is in Italian you might change this caption to `<b>SUCCESSIVO</b>`. You can put any HTML tags you want into the caption. If you want to display an image for this link (for example a forward arrow) simply use an `<img>` tag for the caption. (If this option is omitted the default is `<b>NEXT</b>`.)

- pagenavfont=fontname This option specifies the font to be used for the page navigation bar. (If left blank the browsers default font will be used.) On your own computer you can use any font you want because you have complete control over what fonts are installed and available for use. A web page, however, is the opposite situation since you have zero control over what fonts may be available when the page is viewed. Because of this it is usually best to pick from a restricted subset of fonts that are almost universally available. The fonts that are most commonly available are: Arial, Comic Sans MS, Courier, Georgia, Helvetica, Times and Verdana.

- pagenavfontsize=1..7 This option specifies the size of the text to be used for the page navigation bar. But take note — the size is not specified in points. Instead it is specified using a special HTML text size specification. Absolute values (1–7) specify a fixed font size from extremely small (1) to huge (7). Negative values specify sizes smaller than the text size in the rest of the page, for example -2 is two sizes smaller than normal font. Positive values specify sizes larger than the text size in the rest of the page, for example +2 is two sizes larger than normal font.

- pagenavfooter=template The page navigation bar is usually centered below the bottom of the table. However you can move the navigation bar to the top of the table, or include the navigation bar at both the top and bottom of the table. You can also customize the HTML tags that are used to separate the page navigation bar from the table and the rest of the page.

  The header and/or footer can include any HTML tags you want. Whatever you type in must, however, include the special tag `<pagenavlinks>` (this must be all in lower case). When the page is displayed the `<pagenavlinks>` tag will be replaced with the actual tags and text for page navigation bar. (If the `<pagenavlinks>` tag is missing then the navigation bar will be missing also!)

  If both the header and footer are left blank then a default footer will be used. This default centers the page navigation bar below the table. If either the header or footer is filled in then the default won’t be used. So if you want the navigation bar to appear above the table but not below, simply fill in the Page Navigation Header but leave the Page Navigation Footer blank. (Note: If this option is omitted the default is `<p><center> <pagenavlinks> </center><p>`.)

- pagenavheader=template See pagenavfooter (previous item).

#### 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:

Hotel | Phone | Rate | Rating
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.

- cell=template This template controls the HTML rendered for each cell. `<data>` is replaced by the actual data generated by the formula for each column. For example to render each cell in italic the option would be:

  `cell={<td><i><data></i></td>}`

- row=template This template controls the HTML rendered for each row. `<data>` is replaced by the actual data cells for each row. For example to render each row so that the data is vertically centered the option would be:

  `row={<tr valign=center><data></tr>}`

- table=template This template controls the HTML rendered for the overall table. `<data>` is replaced by the actual data rows of the table. For example to render the table so that the total table width is 600 points the option would be:

  `table={<table width=600><data></table>}`

- titlecell=template This template controls the HTML rendered for each cell in the title row. `<data>` is replaced by the actual column names. For example to render each title cell using bold italic the option would be:

  `cell={<td><b><i><data></i></b></td>}`

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

See Also

- HTML Export -- custom exports into an HTML file.
- htmlarraytable -- generates an HTML table from an arrray.
- htmldatamerge -- uses a template to generate HTML you want from a database.
- htmlextractimages -- extracts the image URLs from an HTML page.
- htmlextractlinks -- extracts the links from an HTML page.
- htmlformitemnames -- returns a list of form input elements (*INPUT* tags, etc.) within an HTML page.
- htmltablecell( -- extracts the data from a cell in an HTML table.
- htmltablecellexists( -- returns true if an HTML table contains the specified cell, false if it doesn't.
- htmltablecellraw( -- extracts the data from a cell in an HTML table (including tags, if any).
- htmltableheight( -- calculates the height (number of rows) in an HTML table.
- htmltablerow( -- extracts the data from a row in an HTML table.
- htmltablerowraw( -- extracts the raw data (including tags, if any) from a row in an HTML table.
- htmltabletoarray -- converts an HTML table into a text array.
- htmltablewidth( -- calculates the width (number of columns) in an HTML table.
- xtag( -- generates an HTML/XML tag.

History

Version | Status | Notes
10.0 | No Change | Carried over from Panorama 6.0.