Porting from FileMaker Pro to PostgreSQL

Version 0.2 Last updated 24th August 2001

FileMaker databases

FileMaker Pro is a database originally developed on the Macintosh by Claris, a subsidiary of Apple Computer. Around 1998, Claris split, some components going with Apple (like ClarisWorks, which became AppleWorks) and FileMaker became it's own company. FileMaker Pro is now the only regularly used cross-platform (PC and MAC) database system. In general, it is more user friendly than Microsoft Access. ODBC and SQL have come relatively late to FileMaker, thus the DBMS is not as standards-compliant as other are.

For versions of Filemaker Pro 6 and earlier: FileMaker files are the equivalent of individual PostgreSQL tables. FileMaker is a relational DBMS, but the relationships are between different files in FileMaker Pro. Often, what is considered a single database is several or many individual FileMaker files. So when converting from FileMaker to PostgreSQL, take each file one at a time, and recreate them as PostgreSQL tables.

For versions of Filemaker Pro 7 and later: All tables can be contained in a single Filemaker Pro file, or they can be in individual files (which will commonly be the case with databases converted from version 6). If working with a multi-table Filemaker Pro file, the same basic process can be followed: create tables in PostgreSQL to match the tables in Filemaker Pro, then export and import the data.

FileMaker Pro databases, in a generalized description, have 4 basic components:

• User Interface (UI)
  These are the text boxes, buttons, menus, layouts, etc. available allowing you to enter, display and manipulate data, as well as change the format of reporting of data.
• Field Definitions
  The data definitions on individual fields you create.
• The Data
  The data itself.
• Scripts
• Scripts written in FileMaker for added functionality.

"User Interface" and "Script" components are specific to FileMaker, having no equivalents in either SQL or in PostgreSQL, and are thus, sadly, not possible to port to PostgreSQL without complete ground up re-writes. Since PostgreSQL has a variety of front-ends available to provide the user interface (pg_access, web-based interfaces using perl, ColdFusion and other web programming languages), the user interface must also be re-written from the ground up. To facilitate this process, diagramming your basic metaphors and the way users interact with your system is very useful.

"Field Definition" components and the FileMaker data however, can be converted to PostgreSQL tables fairly easily.

Field Definitions

In order to convert a database from FileMaker to PostgreSQL, the most common method will be to manually create each table, and make decisions about field type definitions.

There are several different data types in FileMaker; text, number, date, time, container, calculation, summary and global.

Date and time definitions have their obvious counterparts in PostgreSQL.

Text is just like "text" in PostgreSQL, basically unlimited in size. There are no equivalents in FileMaker to char and varchar data types. If you have small text fields, for things like Zip Codes, Phone numbers and the like, you might want to use the varchar or char data types instead of text.

In FileMaker, the number type does not have a precision - it can be an integer, or a float. What distinguishes them in a FileMaker database is the formatting of the output, not the storing of the number - the number is stored as floating point. So when porting to PostgreSQL, determine what fields can be integers and what fields should be floating point.

The other data types provide for very specific problems in terms of converting the database from FileMaker to PostgreSQL. Calculation and Summary field types in FileMaker will have to be ported either by using functions within PostgreSQL, or by including these calculations in the programming interaction between PostgreSQL and the front-end.

Data export from FileMaker and import into PostgreSQL

Exporting data from FileMaker is quite easy. There are a number of formats available, and many provide important options that might make life a little easier.

The easiest method is probably getting data as delimited text, then creating a script or program to get that information into the PostgreSQL database. If you'd like a copy of the script I use, email me and I'll send you the latest version.

As well, hand editing this file to make it correspond to INSERT statements is another possibility, although time consuming, if there are many records.

FileMaker Pro and ODBC

FileMaker Pro is ODBC compliant, so it's possible to use FileMaker Pro to get data from a PostgreSQL database in this way.

Michelle Murrain, Ph.D.