Museums and the Web 1999

Best of the Web

Archives & Museum Informatics
2008 Murray Ave.,
Suite D
Pittsburgh, PA
15217 USA

Join our Mailing List.

Published: March 1999.


The Use of FileMaker for Museum Databases (Collection, Contact and Program Databases)

James Swanson, Whyte Museum of the Canadian Rockies, Canada

Database programs that create web pages in response to user input are familiar in search sites and commercial sites. These dynamic sites require that the database be available to a host computer on the internet. Databases can also be used to create static web pages, which can be uploaded via FTP to a host machine. This function has many applications for museums, especially museums that have their web sites hosted by an internet service provider. The creation of static pages also allows robots to index these pages for access through internet search sites. Data that hides behind a dynamic database is not indexed by external robots.

Web pages as viewed in browsers are interpreted from HTML documents that consist of ASCII characters. Databases can create HTML documents by applying calculations to fields values in database records. Since the resulting document is text, calculations predominantly use text (or string) functions, such as setting a result to a concatenation of string and field values. Following are examples of how HTML documents may be created using the popular cross-platform database program FileMaker Pro. Some of the techniques require version 4.0 or better. FileMaker allows field names to include spaces and be up to 60 characters long

Creating HTML documents from single records

Consider a database of objects in a museum’s collection. The database will contain fields such as catalog number, object name, object title, object creator, and creation date. We want to create an HTML document that displays the values in these fields. One method of accomplishing this is to define a field that is calculated from the values in other fields:

"<html><head><title>Museum " & catalog number & "</title></head>" &
"<A href=""index.html"">Home</a>" &
"<p>Catalog number= " & catalog number &
"<p>Object name=" & object name &
"<p>Creator= " & object creator &
"<p>Title= " & object title &

In FileMaker’s convention, string values are contained within double quotes. To insert a double quote in the resulting string, you insert two double quotes in the calculation. Concatenation is accomplished with the ampersand.

In the case of object number 657, a 1508 painting by Michelangelo entitled “Expulsion from the Garden,” the result would be:

<html><head><title>Museum 657</title></head>
<A href="index.html">Home</a>
<p>Catalog number= 657
<p>Object name=painting
<p>Creator= Michelangelo
<p>Title= Expulsion from the Garden

This calculated field could be exported, or copied and pasted into a HTML editing program. FileMaker 4 does not have the built-in ability to name the exported file according to a value in the field (for instance, this HTML document might be called 675.html). We will later discuss options for naming of exported files.

Calculated field difficult to edit when the calculation references many fields and makes extensive use of quotation marks. An alternative is to create a prototype field using codes for the data that should be substituted with field values in the current record. The prototype field is a defined as a global field that contains text. Text is inserted in the field in FileMaker’s browse mode, rather than through a field calculation. For convenience, codes that are to be substituted with field values are prefaced with *.

<html><head><title>Museum *cat</title></head>
<A href="index.html">Museum Home</A>
<p>Catalog number= *cat
<p>Object name= *name
<p>Creator= *creator
<p>Title= *title

A calculated field is then created to perform the substitution. FileMaker’s function for substitution is

Substitute(text, search string, replace string)

The substitution function is case sensitive and will replace all occurrences of the search string. This requires that care be taken when choosing codes so that they are not substituted unintentionally; for instance, if you used “*w” and “*width” as codes, and first substituted a field with value “150” for “*w”, the second code would be converted to “150idth” and would not be replaced by a subsequent substitution for “*width”.

The initial step in transforming the prototype field to the required value is to substitute the catalog number for *cat. If our global field was called “object prototype”, the first step in the calculation would be:

Substitute(object prototype, “*cat”, catalog number)

The code term is in quotation marks because it is a string value. The three additional substitutions that are required can be nested:

Substitute(Substitute(Substitute(Substitute(object prototype,
"*cat", catalog number),
"*name",object name),
"*creator", object creator),
"*title", object title)

This expression appears complicated but is easily built up by reiterating the substitute function. Note that when specifying calculations in FileMaker, you can insert returns to aid in comprehension; they do not affect the calculation. If you wish to insert a return character in the calculation, FileMaker provides a carriage return marker in its list of operators.

The substitution method makes it simpler to edit the prototype HTML, which can be pasted in from a text document developed for the purpose. For instance, suppose that we also want to include a link to an image file, named with the catalog number plus “.jpg”. Create a prototype HTML document and test its appearance in a web browser (these simplified examples do not include components such as meta tags and image parameters):

<html><head><title>Museum *cat</title>
</head><body bgcolor="#FFFFFF">
<A href="index.html">Museum Home</A><hr>
<table cellpadding=5><tr>
<td><img src="*cat.jpg"></td>
<td><p>Catalog number= *cat
<p>Object name= *name
<p>Creator= *creator
<p>Title= <i>*title</i>

The substituted result would be:

<html><head><title>Museum 657</title>
</head><body bgcolor="#FFFFFF">
<A href="index.html">Museum Home</A><hr>
<table cellpadding=5><tr>
<td><img src="657.jpg"></td>
<td><p>Catalog number= 657
<p>Object name= painting
<p>Creator= Michelangelo
<p>Title= <i>Expulsion from the Garden</i>

Because of its ease of creation and modification, the method of substituting prototype fields will be used in the remaining examples.

Creating HTML with values from multiple records

Often we want to represent data as a list of field values from several records; for instance, a listing of upcoming events or exhibitions at a museum. Each event has a database record, which will result in a table row in the HTML document. For an event record which has a title, date and description, the prototype table row might be:


The calculation for the substitution field would be:

Substitute(Substitute(Substitute(event prototype,
"*date", MonthName(event date) & " " & Day(event date)),
"*title", event title),
"*desc", event description)

In this example we have modified the way the date is displayed by using date functions. The result of the calculation would resemble:

<td><h2>March 11</h2></td>
<td><h2>Museums and the Web Conference</h2>
Over 400 museum workers from around the world converge on New Orleans.</td>

Including heading and closing text

To create a complete HTML document, the table row data must be prefaced by opening HTML statements and the declaration of the table, and followed by the closing of the table and HTML closing statements. One way to accomplish this is by a refinement in the calculation of the substitution field that detects whether a particular record is the first or last in the series (or “found set”, in FileMaker’s terminology), and including header or closer text contained in global fields:

Case(Status(CurrentRecordNumber)=1,event heading,"")
Substitute(Substitute(Substitute(event prototype,
"*date", MonthName(event date) & " " & Day(event date)),
"*title", event title),
"*desc", event description)
Substitute(event closer,"*date",MonthName(Today) & " " & Day(Today) & ", " & Year(Today)),"")

The first case statement uses a status function to determine if the record is the first in the list; if so, it includes the event heading field. The second case statement determines if the record is the last in the list; if so, it includes the event closer field, after performing a substitution on its date value.

When the resulting substituted field is exported, it creates a complete HTML document. It is straightforward to create a script that searches for upcoming events, sorts the records by date, and exports the result to a HTML file as a calendar of events. Most museum might only need update such a file weekly or monthly.

A similar procedure could be used to create an index of object records. Using our previous example, to create an index sorted by creator, the object row prototype could be:

<tr><td>*creator</td><td><A href="*cat.html">*title</a></td></tr>

After substitution, this would provide a table with links to the individual object files, stored as “catalog number.html”.

Setting fields by looping

A more versatile method of creating an HTML document with values from multiple records is to calculate the necessary text by looping through the relevant records. This text is stored in a master global field which may be exported or copied and pasted. This is done through a script with the following steps:

Go to Record/Request/Page [First}
Set field [“master field”, “heading field”]
Set field [“global text holder”, ""]
If [“global text holder” <> MonthName(event date)]
Set Field [“master field”, “master field & month heading”]
Set field [“global text holder”, MonthName(event date)]
End If
Set Field [“master field”, “master field & event prototype substituted”]
Go to Record/Request/Page [Exit after last, Next]
End Loop
Set Field [“master field”, “master field & closing field”]

This example includes a method for creating special headings for each month. The script first ensures that it starts at the first record in the found set. The master field is then set to the heading field (substitution calculations can be applied if necessary). Next, a global text field that will be used to detect when the month changes is initialized to empty. Then begins a loop that will step through each record in the found set. If the month of the current record is not the same as the month stored in the global text holder, a month heading is appended to the master field (again, substitutions could be applied). The “Set Field” calculation in this step demonstrates the means of appending data to a field:

Set Field [“master field”, “master field & month heading”]

The first parameter after the “Set Field” is the name of the field to be set by the calculation; in this case, “master field”. The second parameter denotes the calculation to be applied; in this case “master field & month heading”. In other words, the master field is set to itself plus another value.

The next step in the loop sets the global text holder to the month of the event of the current record. Since this step is within the “if” statement, it only occurs when the month has changed. After the “end if” statement, the event prototype substituted field is appended to the master field. Then the script brings in the next record for processing, or if the current record is the last record, exits the loop. Finally, the closing field (perhaps substituted) is appended to the master field.

Exporting rows only

An alternative to exporting the complete HTML file is to export a document containing only the table rows, and embed the rows in a master document containing the other HTML expressions. This is useful if the surrounding HTML is complicated or frequently altered or if it contains more than one table. The table row document can be copied and pasted into the master document.

Converting extended characters

If the fields of interest contain accented characters or other characters that are not in 7-bit (“low”) ASCII , it is necessary to convert the result so that extended characters are expressed as HTML entities (for example, “” becomes “&eacute;”). This conversion can be accomplished with a calculation employing nesting substitutions. Because of the number of possible accented characters, this calculation will look something like:

Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( Substitute( field to substitute,"","&Auml;"), "", "&Aring;"), "", "&Ccedil;"), "", "&Egrave;"), "", "&Ntilde;"), "", "&Ouml;"), "", "&Uuml;"), "", "&aacute;"), "", "&agrave;"), "", "&acirc;"), "", "&auml;"), "", "&atilde;"), "", "&aring;"), "", "&ccedil;"), "", "&eacute;"), "", "&egrave;"), "", "&ecirc;"), "", "&euml;"), "", "&igrave;"), "", "&icirc;"), "", "&iuml;"), "", "&ntilde;"), "", "&oacute;"), "", "&ograve;"), "", "&ocirc;"), "", "&ouml;"), "", "&otilde;"), "", "&uacute;"), "", "&ugrave;"), "", "&ucirc;"), "", "&uuml;"), "", "&deg;"), "", "&szlig;"), "", "&Agrave;"), "", "&Atilde;"), "", "&Otilde;"), "", "&Acirc;"), "", "&Ecirc;"), "", "&Euml;"), "", "&Egrave;"), "", "&Iacute;"), "", "&Icirc;"), "", "&Iuml;"), "", "&Igrave;"), "", "&Oacute;"), "", "&Ocirc;"), "", "&Ograve;"), "", "&Uacute;"), "", "&Ucirc;")

If the substitute calculation field is defined to act on a global field, this calculation can serve to convert text for any number of fields, by first setting the global field to the field to be converted, and putting the result back in a field in the record. In the example, the field to be substituted would be a global field. A script would then set that global field to the field to be converted, and then set a field on the record to the value of the calculation.

Set Field [global field, field to be converted]
Set Field [converted result field, global field]

The conversion of a number of records can be done by a looping script as part of the export procedure. The entity conversion can also be embodied in an external database file, and applied to fields via a relational calculation. This allows the calculation to be created once and called from a number of files.

Naming the HTML File

These procedures allow the creation and export of arbitrarily complex HTML documents from records in a FileMaker database. However, FileMaker does not provide a method of naming the exported file from a value in the database. This is not a problem with an event listing file that always has the name “event.html”; the name can be embedded in the export script step, and whenever the script is run, it will update the “event.html” file. But what if the collection contains hundreds or thousands of objects? One does not relish the notion of stepping through each record and specifying a file name during an export. There are a couple of workarounds to this restriction, one using AppleScript on the Macintosh platform, and the other using an inexpensive cross-platform FileMaker plug-in.


AppleScript is a language that applications running under the Macintosh operating system can use to communicate with each other. Scriptable applications, such as FileMaker, provide an extensive array of methods for other programs to tell them what to do. FileMaker provides the ability to execute an AppleScript via a script step:

Perform AppleScript [specified field]

This script could be used to tell a text editing application to create a file with contents specified by the database; it could be used to tell the Finder to create a new folder to accommodate a hierarchy in data represented by the database. Utilizing AppleScript in this manner requires that the specified field be a valid AppleScript statement. This statement can be derived from a substitution on a prototype field, which has been ascertained to be a valid statement in Script Editor, the program Apple provides to create scripts. For example, to tell the finder to make a folder based on a field value, one might say:

tell application "Finder"
if exists folder "*PATH:*FOLDER NAME" then
make new folder at folder “*PATH" with properties {name:"*FOLDER NAME"}
end if
end tell

A calculated field would substitute values for the codes in the prototype; executing the script would create a new folder with the specified path, if one did not already exist.

Another example involves using a text editor, in this case BBEdit, to create a file with values copied from a database field. A global text field would be created and something similar to this entered:

tell application "BBEdit 5.0"
save window 1
end tell

This field would be the basis for a substitution calculation. The field would be invoked after a FileMaker script had copied the value to be pasted into the new file. The use of AppleScript requires familiarity with the language, especially in the trapping of errors.

Troi File Plug-In

A simpler solution to the naming of files, and one that works on Macintosh and Windows systems, utilizes the plug-in architecture inaugurated in FileMaker 4. Plug-ins allow developers to provide additional functionality to FileMaker; the Dutch company Troi Automatisering ( has created an inexpensive cross-platform plug-in that adds external functions to FileMaker, allowing the database to create a file of specified, name, path, contents and type. All these parameters are specified by script steps that utilize calculations provided by the plug-in:

Set Field ["filespec","*path & *file name"]
Set Field ["global temp field", "External("TrFile-SetDefaultFileSpec", filespec)"]
Set Field ["global temp field", "External("TrFile-SetDefaultCreator",file creator)"]
Set Field ["global temp field", "External("TrFile-SetDefaultType", file type)"]
Set Field ["global temp field", "External("TrFile-CreateFile",filespec)"]
Set Field ["global temp field", "External("TrFile-SetContents", text)"]

Running this script would create a file with a name, path, and contents specified from fields in the database. The plug-in also allows appending data to an existing file, so it can be utilized in a scripting loop to create tables.


With the use of its built-in functions and additional facilities available via plug-ins, the FileMaker program can be used to represent much of the information a museum might want to publish on the internet. By the creation of static web pages, this representation is available to museums that do not host their own internet sites.