Using Visual FoxPro to Generate Data-centric Web Pages

Introduction

The current version of Visual FoxPro is 9.0; a newer version is well on its way. VFP is a tool that handles most business applications which have moderate levels of both transactional processing and data size.  Its data-centric origins are aided by a powerful object model with its own programming language that can handle queries not possible with the standard SQL. These features have made it very attractive.

While it remained apart like a loner from other Microsoft products, version 9.0 will see an enhancement in the next version, code-named, Sedna which will integrate with the .Net framework. Even in version 6.0 it had excellent support for bringing data to the web in a painless manner, even better than MS Access. The main objective of this tutorial is to export a table to Visual FoxPro from MS Access and show how easy it is to generate web pages.

Visual FoxPro can work with different database products such as Lotus, Dbase, Paradox, Excel, and so on. Fig.1 shows a table in an MS Access database called ToFoxPro.mdb that is to be exported to a Dbase IV format table. The Orders table has some 830 records.

Fig.2 shows the design view of the table. In this view it is easy to see the primary key, the indexing of the columns, and so on.

The table can be exported by clicking on File in the main menu and then clicking on Export after highlighting the table to be exported. This brings up the window "Export Table 'Orders' To" shown in Fig.3, where it opens with the default of exporting to an MS Access file type. You must use the drop down to select dbase IV (*.dbf) format as shown. Visual FoxPro can also handle dbase III and dbase5 file types. When you choose the format to export to, the title of the window changes to "Export Table 'Orders' As." Now you click on the Export button and you will see the Orders.dbf file in the default directory, My Documents.

Now this file can be accessed from Visual FoxPro. Start Visual FoxPro from its shortcut on Start ->  All programs. From the File menu click on open,  which opens up the default folder with default file extensions, Project(*.pjx, *.fpc, *.cat) which you change to Table(*.dbf) by the drop down and the My Documents folder to search. This will show up the Orders.dbf you created earlier. Highlight this and click OK. This brings up the Code Page window as shown where you choose the locale related code.

In here, you choose the Windows radio button and click on Select which sets the locale related code page to 1252 Windows ANSI. It rebuilds the names of tags to remove white space in conformity with its own naming scheme. Now you may browse and look at the table by going View ->Browse from the main menu as shown in Fig.5.

This brings up the Orders.dbf table to be displayed as shown in Fig.6. Comparing with the original table you may notice the changes that have taken place. The Formatting has changed considerably from the original as well as the column names.

Fig.6

Visual FoxPro Table Features

The next couple of figures show the features of the table object in Visual FoxPro as seen in its UI. Click on the Table Designer drop down item in Fig.5 shown above. A Table Designer -order.dbf window comes into view as shown in Fig.7 with three tabs that contain all the features of the table. Again you may notice the precision of the numeric data type and the date data type change.

When you move to the tab Indexes you will see the following window shown in Fig.8. It shows the primary key as well as the columns indexed. The exporting has preserved the indexing of the columns. The third tab, Table, shows some general information such as name of table, number of rows, and so on.

Fig.8

Creating a web page from the table

There are lots of wizards in Visual FoxPro. They can be accessed by going to Tools ->Wizards as shown in Fig.9.

Fig.9

Choosing fields to be included in the web page

A click on web publishing takes you to Step. 1 of the publishing activity as shown in Fig.10. Here a couple of fields from the available fields on the left have been transferred to the right. These are the ones that will finally make it to the web page. Click on the Next button, which takes you to Fig.11.

Fig.10

Sorting the fields chosen

This brings you to Step.2 where you make a sorting choice. In this case the records are sorted first by Customer ID followed by Order date as shown in Fig.11. Click on the Next button.

Fig.11

Choosing a presentation style

This brings you to Step. 3 of web page generation shown in Fig.12. Presentation is the key to a pleasant user experience. There are lots of built-in styles and data layout decorations that can be chosen. You may choose ones that are similar to those I've chosen here. When you click next, you come to the last step, Step.4.

Step. 4 has a few options that you can choose as shown in Fig.13. You can give a title to the web page, but this will only go between <Title></Title> of the web page. However, if you elect to edit the page you can add custom text to the page. Here the first option has been taken. When the Finish button is clicked a web page with the same name as the table, namely orders.htm, is created in the default directory.

Fig.13

Displaying the web page created

Fig.14 shows a part of the orders.htm as seen in a browser. It comes ready with navigational features and a pleasing background. You can step through the records and the chosen fields are shown in the right hand pane. In the background, Visual FoxPro has created several other auxiliary htm pages to make this navigation possible. All these pages are held together in a frame set as seen in the following source code for orders.htm. The orders_0.htm, orders_1.htm, and orders_2.htm are also saved to the default directory.

<head>
<title>
Orders from MS Access
</title>
</head>
<frameset rows="80,*">
<frame name="frame0" id="frame0" frameborder=0 src=orders_0.HTM>
<frameset cols="20%,*">
<frame name="frame1" id="frame1" frameborder=0

src=orders_1.HTM>
<frame name="frame2" id="frame2" frameborder=0

src=orders_2.HTM>
</frameset>
</frameset>
</html>

Visual FoxPro makes it very easy to create a web page even better than Data Access Pages, a technology that came with Access 2000. The web page creation feature was present in Visual FoxPro from much earlier times. When I was consulting with WorldCom, someone there once told me that it was the ease with which the data was made available as a web page  that made them choose this software. With the added editable options available it is very easy to create a custom look and feel for the reports.