1. Creation of the FileMaker Database
2. Export of the data from FileMaker
3. Creation of an ODBC datasource
4. Creation of the web interface
   1. Index Page/Summary List
   2. Detail Page
5. How this process fits in with our existing site
6. Pros and Cons of our system

Creating single-table databases in FileMaker is straightforward. The first step is to define the fields in the table. In this case, I created a text field for each of the pieces of data in the spec, plus an auto-incrementing ID field. The second step, which is not always necessary, is to revise the layout. Fields that have been defined are automatically added to the layout, but not necessarily in a usable manner.

FileMaker can be used to create a relational database as well as a the single-table one used in this situation. This requires a couple of extra steps, but is still fairly straightforward.

This file can be opened by any FileMaker client with access to the directory. Our real-life configuration is slightly different. Our FileMaker databases are hosted on a dedicated FileMaker server which allows multiple people to access them over the network.

Export of the data from FileMaker [top]

FileMaker includes a scripting language that can control data exports. These scripts can also be triggered by an event, such as closing a database file.

To set up the export of data, I first created a script that would export a tab delimited file to a directory on the web server. Second, in the Document Preferences, I set the script to execute when the file was closed. The net result is that the tab delimited file is updated every time a person finishes working in FileMaker.

In our real-life setup, this is one of the major points at which we include business logic. The export script will include filters that determine which data is exported to be visible on the web.

Creation of an ODBC datasource [top]

ColdFusion (Professional Edition) is compatible with any ODBC data source. To make the data in the tab delimited file exported from FileMaker accessible to ColdFusion, I set up a text data source. This took several steps. First, I created and configured a System DSN named 'ACSF' using the Microsoft text driver. This is also possible to to through the ColdFusion Administrator, but I have found that it does not work well for text data sources. The Microsoft ODBC text driver treats an entire directory as a database, and each compatible file within it as a table. The net result is that the table 'codeoff.tab' is available to ColdFusion within a datasource called 'ACSF.' Second, I edited the schema.ini file to define field names for codeoff.tab.

In our real-life setup, we have a directory already defined as an ODBC text datasource. When we add an export from FileMaker, we skip the first step of this process and update. We only need to edit the schema.ini file to include information on the newly exported data.

Creation of the web interface [top]

The address book is handled within a single URL, http://www.ssw.umich.edu/ssw-web/acsf/index.html. Based on the presence of a the URL variable "id," a template for the summary or detail listing is called.

<!--- include the standard page header ---> <cfinclude template="/inc/head.html"> <!--- if the URL variable "id" is defined, include a template to show the detailed information associated with that ID. Otherwise, show a complete list of names from the database. ---> <cfif isdefined("url.id")> <cfinclude template="_detail.cfm"> <cfelse> <cfinclude template="_list.cfm"> </cfif> <P> <A HREF="how.html">How was this done?</A> <!--- include the standard page footer ---> <cfinclude template="/inc/foot.html">

Index Page/Summary List [top]
The summary template (_list.cfm) contains two primary components. First, it queries the database "ACSF" to get the firstname, lastname, and id field of every entry in codeoff.tab. Second it uses a <cfoutput> statement to loop over the data rows returned by the query, and output an item in a bullet list for each one.

<!--- get a list of names and id's ---> <cfquery name="list" datasource="acsf"> SELECT firstname, lastname, id FROM codeoff.tab ORDER BY lastname, firstname </cfquery> <!--- output the list, with a link to the detail view, using the id as a key ---> <UL> <cfoutput query="list"> <li><A HREF="index.html?id=#id#">#lastname#, #firstname#</A> </cfoutput> </UL> To be added to this list, email <A HREF="mailto:ssw.webmaster@umich.edu">ssw.webmaster@umich.edu</A>

Detail Page [top]
The detail template starts with a section checking the validity of the URL data passed to it. Otherwise, it is very similar to the summary template. It has two primary parts: a query, followed by an output of the data from the query. The primary difference is that the query is constructed to return a single record based on a url variable. The output section then displays the detail data returned by the query.

<!--- error checking on the ID in the url ---> <cfif not (isnumeric(url.id)) or (url.id gt 10000)> <cflocation url="index.html"> </cfif> <!--- get the data from the datasource ---> <cfquery name="list" datasource="acsf"> SELECT * FROM codeoff.tab WHERE id = '#id#' </cfquery> <!--- Output the data from the query. There could be error checking in order to account for blank fields, malformed URL's, and email addresses, but for brevity's sake, this is left out. ---> <cfoutput query="list"> <B>#firstname# #lastname#</B><p> #org#<BR> #address#<BR> #city#, #state# #zip#<P> Phone: #phone#<BR> Cell: #cell#<BR> Fax: #fax#<P> <A HREF="mailto:#email#">#email#</A><BR> <A HREF="#web#">#web#</A><P> </cfoutput>

How this process fits in with our existing site [top]

This address book application is a very simplified model of our data / web infrastructure. The three differences are:

• The size & complexity of the databases involved.
• The complexity of the online applications. Address data on the SSW web site is driven by a single custom tag that is used in multiple places:
  • The main Phonelist: http://www.ssw.umich.edu/people/phonelist/
  • Faculty Profiles: http://www.ssw.umich.edu/faculty/profile-pameares.html
  • The SSW intranet
• Data from the tab delimited files is automatically imported daily into a legacy MS Access database, which serves as the datasource for ColdFusion. This is necessary to maintain the integrity of the legacy data.

Pros and Cons of this system [top]

Pros

• FileMaker is very easy for SSW staff to use for finding, updating, and adding institutional data
• Data collection and management is done independently of the web staff. This frees our time to maintain and improve the site.

• Updates to online information are not live.
• FileMaker Server's robustness, security, and scalability is limited.
• FileMaker can store binary data such as images, but it cannot export it.
• Complex queries directly against FileMaker are slow, and are generally not supported by the ODBC text driver. The workaround is to use an intermediate database such as MS Access.