Building A Complete HTML Document In FileMaker Pro
by Geoff Mitchell (gsm@geoffmitchell.com)
RATING: Intermediate
VERSION: FileMaker Pro 3/4/5
PLATFORM: Macintosh & Windows
TECHNIQUE FILE: HTML.FP3
What I am about to tell you should be kept strictly confidential. It should in no way be communicated between yourself, your users and especially the firm you are billing for your development time. Here it is: you can create a single script that will instantly build and create a pre-formatted, customized HTML document using data from your database in a just a couple of hours.
It'll be our little secret...
I'm not talking about FileMaker's built in ability to save a found set of data into an HTML table. That's convenient, but limited to a single table, and requires additional work to customize the entire web page to your specific site needs.
Nor am I talking about FileMaker's ability to publish live databases on the web either. There are circumstances where content needs to be controlled, or you don't have your database running on a web server. In a reporting environment, a static web page provides a wider range of control and restriction. Your need to publish data on a web page can be as simple as an employee directory, a listing of product information or just your personal list of a growing DVD/Home Video collection.
What I am talking about is having a single script in your database that can instantly create the finished, web-ready, fully linked and customized HTML, every time, with a single click.
Did I mention how simple this is? Well, it did take some serious time figuring out how to approach it, and numerous trials and errors that I will spare you with this article.
Getting Started
First, you'll need to know the HTML code that will make up your web page. This is just a matter of opening an existing web page as a model or quickly designing one using an HTML editor or web page design application.
Your database will need an available global field in which to build your HTML text. As a matter of habit, I always create one global text field in a database specifically for use in manipulating text or juggling data as needed. One global text field in a database can make the most amazing things possible.
For this exercise, we'll use the example of maintaining and updating your own list of DVD's for your personal website. This is a very basic example, but this process will work with any database structure you design, from private to corporate needs.
Your personal web page needs to show a simple table of your DVD's. Your script will create the html code for you, starting with the header information, then looping through each of the database records to gather the specifics for those records, and ending with the closing html codes. All this text gets created in the global text field, and is copied to the clipboard for immediate pasting into a text editor. You can even extend this to save the global field as a text file export at the end of the script if you like.
The way you'll approach building the HTML involves three steps. Your script will first insert the HTML for the top of the web page, right up to the start of the table where you'll be getting database records. We'll then step through each record and add a new row to the HTML with that record's data. Finally we'll add the closing HTML text and have a global field containing the complete web page.
One final note before we jump in. A caveat about text strings in a Script. Your script is mainly managing text, and you can only have so many characters between operators. You'll find it essential to break up the long text strings with a quote, ampersand and quote [ " & " ]. You'll find this type of structure in my example technique file HTML.FP3 as well.
Set global1 to ""
Set global1 "<HTML>¶
<HEAD>¶
<TITLE>Sample HTML from FileMaker Pro</TITLE>¶ </HEAD>¶
<BODY BGCOLOR=#FFFFFF>¶
<CENTER><BR>¶
<IMG SRC=http://www.geoffmitchell.com/images/dvd.gif><BR>¶ <B>My Video Collection</B>¶
<P>Last Updated " & DateToText(Today) & "</P></CENTER>¶ <BLOCKQUOTE><BLOCKQUOTE>¶
<HR>¶
¶
<P>"
Go to Record/Request/Page [First]
Loop
Set Field ["- global1", "- global1 & "¶" & Title &" [" & Year & " " & Category &"]<BR>""]
Go to Record/Request/Page [Exit after last, Next]
End Loop
Set Field [- global1 & "¶<P>¶<HR>¶This WebPage was completely created using FileMaker Pro...."]
Go to Layout ["Detail"]
Copy [Select, "- global1"]
Go to Layout [original layout]
Go to Record/Request/Page [First]
Show Message ["Your HTML has been created, copied and is now ready for pasting directly into a text editor."]
Your HTML has been created, copied and is now ready for pasting directly into a text editor.
Remember that you can extend this process to any range of HTML demands. You may need to jump a few text hurdles in your script, but it can be done with a little patience.
When run, this script will build the HTML documents from the specifications in your script. In this example, the HTML text is copied to the clipboard for pasting into a text editor.
You can enhance this script by performing an export of the global field of a single record as a text file. (Doing so requires narrowing down the found set to a single record. Your 'found set' will be lost unless you build in some provision to mark and re-find the found set after the export, but the effect will mean a completed HTML document created with no user interaction, which is pretty impressive stuff).
Perform an export of your database record to a Tab Separated Text file, exporting ONLY the Global1 filed containing your HTML text. Then create the following script.
Find All
Omit
Find Omitted
Export Records [Restore, No dialog]
Find All
If you find that you have need for frequent editing of your HTML code, you can easily change your approach by creating global fields for the HTML header, body, and footer, so changes can be made without having to work in ScriptMaker. This approach would require you incorporate 'Merge' values to replace with real data when the HTML is created. For example, the HTML body field would contain a variable like <<*TITLE*>>. Your script would set the global field to the header, then loop through each record, adding the HTML body field, and then performing a Replace of the text string "<<*TITLE*>>" with the field value.
Happy FileMaking!
Geoff Mitchell has been developing in FileMaker pro for seven years, including a solution still used by Apple Computers, where he is employed fulltime. Check out his website at http://www.geoffmitchell.com.