Home
FrontPage Hosting
mySQL Hosting
Dedicated Servers
Managed Services
Domain Parking

Search our FrontPage Support Area
FrontPage Support Area Site Map

The following documents are geared towards using VirtuFlex with Microsoft FrontPage.

Three Examples of Getting VirtuFlex's
Interface to Get the Job Done
December 4, 1997
W. Curtiss Priest, Ph.D.

The beginner often runs into what appears to be a serious limitation with VirtuFlex, but often the situation simply requires another approach or just thinking ahead.

When an HTML page is constructed by VirtuFlex, most of the "macro" commands are executed prior to data being returned by a "DBASE:SELECT" command. This means that one may not use "if tests" on these values (such as the use of "EVAL:IF").

Yet three approaches to the apparent limitation usually makes it possible to "get the job done."

We use some examples to illustrate three basic ways to proceed. First, we take advantage of VirtuFlex's post-database reading, processing involving functions "applied to" the returned value. While an "if test" can't be used to determine the contents of a returned field, one can use a series of "CONVERT" statements to process the contents, and achieve a satisfactory result.

Secondly, we take advantage of HTML's ability to pass parameters between pages to make the content of the database available later. Often it is only necessary to extract a required piece if information on the page before the one you need it for, to process further.

Thirdly, we employ the ability for the database language to return information from fields processed by aggregate, number, and character functions.

CLASS I of SOLUTIONS: Use of PREPEND, POSTPEND and CONVERT

The first example of this involves a simple matter of going from Microsoft Access' format of "Yes/No" data to the format for HTML checkboxes.

When Access returns Yes or No for a field it is passed by ODBC as 1 for "Yes" and 0 for "No." But HTML uses an optional "CHECKED" attribute in the <FORM.... .

While one might be tempted to use an EVAL:IF to test for a 1, and insert the needed attribute, it is just as easy to use CONVERT:

<input type="checkbox" name="Inactive"
{inactive|convert=1=checked}>Inactive</p>

The Access field name is "Inactive" and the HTML name is also "Inactive."

When the box is checked, the HTML form will return "on" (This is the default return value if no value attribute is specified.)

The convention for whether the box is "pre-checked" is presence off the optional attribute "CHECKED." So, the convert function in VirtuFlex takes the presence of a "1" and converts it to the word "checked." If the value were 0, we are left with a lonely 0 in the input statement which the browser ignores.

However, if you wish to be neater, you can use convert a second time as:

... |convert=1=checked|convert=0= }

This way a zero becomes a space and is fully transparent to the browser.

Note: In FrontPage most interlaced fields may be inserted without HTML markup comments around them. In WYSIWYG, the fields, with their curly braces appear as if they were form values. However, when you introduce the equal sign, FrontPage will want to change the first example to:

{inactive|="1=checked}"

with equal signs around what it thinks is an unquoted string value belonging to inactive|. So, when using conversions insert this part of the form as an "HTMLMarkup"

Later we want to do an SQL Update based on the value of the check box. The code for this looks like:

    Inactive=
    [[EVAL:IF:[[GETDATA:Inactive]]=on:
        -1
        :
        0
        ]]

Here we can use an EVAL:IF, since we are not reading from an "interlace" field value. If the returned HTML value for Inactive is "on" we assign the field value "Inactive" to "-1" (an Access "Yes"); otherwise we assign a 0 (an Access "No").

Notice the use of a "structured" EVAL:IF. See our paper "Structured "EVAL:IF"s in VirtuFlex" for more information on structuring VirtuFlex code.

Second Example of Class I Solutions:

Considerable discussion was held on the VirtuFlex developers' list (virtuflex-dev@virtuflex.com) about how to solve the problem of "not labeling data in the browser" when that data was absent.

The problem:

In a database there may or may not be people's first names. You want not to have the label "First Name" appear if the first name is absent.

The solution:

By combining PREPEND, POSTPEND and CONVERT the desired output is acheived. The macro looks like this:

[[DBASE:SELECT:[Name Table]:item like %:
{name|PREPEND=First Name |POSTPEND=$|CONVERT=First
Name$=|CONVERT=$=}]]

In this example, the "SELECT" function extracts fields from the [Name Table] database. Recall that it is the ODBC convention to enclose table names within brackets that contain spaces.

Since the "SELECT" function requires a conditional, we ask for all records using the % sign.

The field either contains characters (a first name) or is a null string (no characters). We "prepend" our label "First Name" to the first name that was returned. This places that label in front of the string. We then put a $ sign on the end of the string with "postpend."

Now we either have:

First Name$

or something like:

First Name Joe$

If we examine the two conversions for the first case, the first conversion will convert "First Name$" to a null string and nothing will appear in the browser.

If we examine the second case, the first conversion does nothing to the string, and the second conversion removes the $ from the end. (Jason Turim of VirtuFlex devised this clever solution.)

In summary, languages like VirtuFlex are great in their simplicity but that sometimes comes with the need to know "tricks" to deal with certain situations.

CLASS II of SOLUTIONS: Use of Posting Interlace variables

A common practice in examining database information is to provide some data from a table, and then provide the user with the option to display more detailed data.

Sometimes the more detailed data requires reading data from more than one table at a time.

But, if the key field of the second table is contained in the first table, and these keys are not the same some planning is required.

In this last example, we examine a "Member Table" of organizations. These organizations also belong to a geographical site. This information is contained in the "Site Table."

We design one HTML page to allow the user to search for a particular member. When that member is located, the user can double-click on any line from the search (an href tag "click to view" appears on each line.)

The same page is refreshed and as it is, detailed information from the Member Table is used to fill in an area of the screen that was previously blocked out by a transparent gif image. (We simply test for the presence of a posted variable using a VirtuFlex IF test to change the display behavior of the same page.)

A button that was blank before, now has the words "SHOW" and if the user presses this button, a new page appears with considerably more detail. Some of this information comes from the Member Table but some comes from a corresponding Site Table. Yet, on a single page, VirtuFlex cannot read from one table and substitute the field value in a "SELECT" for another table. A "SET:VAR" won't work because those functions are evaluated BEFORE the SELECT returns any field data.

How can data from two or more tables be displayed on the same page when they are dependent?

The answer is to use HTML's hidden fields that are passed from the PRIOR page. When the first page was loaded with detailed information about the organization, it also had access to the key field value for the corresponding Site Table that is to be displayed on the detailed page.

Here are some code snippets to show how this is done:

Step one: Getting the second key field while processing Page One

<a href=/cgi-bin/virtuflex.exe?template=/td/organiza.htm&organ=
[[GETDATA:organ]]&organid=[[GETDATA:organid]]&MemID={MemID}&SiteID=
{Site}>View</a></td>]]

As mentioned above, we give the user the ability to reload the page with more detailed information. this is done by embedding this href. The href reloads the same page, and a VirtuFlex "if test" determines that "MemID" is no longer zero length (it exists) and so displays the more detailed information.

At this time, we also take the "SiteID" out of the database. We have no use for it with Page One. We simply want to pass it along. So &SiteID={Site} now ensures that the reloaded page contains this "POST"ed information.

Step Two: Passing the second key field to Page Two

We will use VirtuFlex's ability to read a variable from Page One using GETDATA. And we use HTML's ability to pass "hidden" variables when a form is processed. Contained on Page One, this looks like:

<input type="hidden" name="template" value="/td/oneorg.htm">
<input type="hidden" name="MemID" value="[[GETDATA:MemID]]">
<input type="hidden" name="SiteID" value="[[GETDATA:SiteID]]">

Here we tell VirtuFlex to load Page Two (called oneorg.htm). We pass the TWO key fields, the Member ID (MemID) and the Site ID (SiteID) as in the hidden INPUT element of the form.

Step Three: Using the second key field to display the data on Page Two

In the first part of Page Two, information is displayed by a "DBASE:SELECT" on the "Member Table" In the lower right of the page (as part of a table embedded within a table), detailed information about the Site is displayed. We access this information using:

[[DBASE:SELECT:[Site Table]:SiteID=[[GETDATA:SiteID]]:

We are able to use a GETDATA on SiteID because it was obtained and passed via steps one and two above.

Step Four: Page Two -- refreshing and retaining the field value

Page Two in this example displays values in form boxes that may be changed. After the first load of the page, each reload processes the values from the forms and reloads the same page again. An "EVAL:IF" determines if we are reloading the page, and if so, does an SQL update. This permits the user to make changes to the database fields, and the fields are redisplayed. (And the database fields that don't change are updated with exactly the same values, but this is fine, and we don't have to bother with testing for changed lines of field data.)

The site data (which is not editable on this page) is simply rewritten to the form again. But, we need to pass the Site ID from one refresh of the page to the next. This is done by hidden values again as:

<input type="hidden" name="template" value="/td/oneorg.htm">
<input type="hidden" name="MemID" value="[[GETDATA:MemID]]">
<input type="hidden" name="SiteID" value="[[GETDATA:SiteID]]">

Notice that these three lines are identical to the ones we used above in step two. The only difference is that these lines are contained on a page that recalls itself, rather than in calling a second page (Page Two).

CLASS III of SOLUTIONS: Use of Database functions (using Access)

One useful piece of information is to know the length of the string being returned for a field. Another useful thing to do is to force all characters to upper case.

While VirtuFlex has both of these functions (LENGTH & CAPS), they cannot be applied directly to the field value.

In a DBASE:SELECT

    this DOESN'T WORK -- [[GETDATA:{Lastname}:LENGTH]]

but

    this WORKS -- {len(LastName)}

See "Introduction to Access Functions" for a listing of all Access functions that are available via ODBC and VirtuFlex.

There are also ODBC functions which are universal across different SQL databases, but these are not currently implemented in VirtuFlex

There is also a technique by which you can define a query in Microsoft Access which includes not only the use of Access functions but full computations as well. The details of this technique are described in "Using ODBC to return a Microsoft Access Query"