SNAPSHOT: Getting started, FMP 9 and ESS

From Dwayne Wright -

The following are my notes for studying for the FileMaker 9 certification exam as I study up on this topic. Thought I might as well put them on the blog for reference material. Feel free to add any comments you might have. These notes I believe are accurate but I'm still learning all the nuances of FileMaker 9 myself.

FileMaker 9 and ESS (External SQL Source)
You need to setup a DSN before you can connect FileMaker to an ODBC source. On Macinosh OSX, this is an application called ODBC Administrator and it can be found in the Utilities sub folder of the Applications folder. On Windows, it is a control panel setting in the Administrative Tools area.

Officially Supported ODBC (SQL) data sources at the time I'm noting this
- SQL Server 2005
- SQL Server 2000
- Oracle 10g
- Oracle 9g
- MySQL 5.0 Community Edition

Once you have the DSN setup for the computer, ODBC sources are added to a FileMaker 9 database solution as an external data source. This is akin to the way you add external FileMaker files but you are adding the ODBC source instead. Just like after you add an external file reference to your FileMaker solution, you can then add that files tables to your relationship graph, after adding the ODBC external data source, you can then begin to add the tables from the ODBC data source to the relationship graph.

After you place one of the tables from the External SQL data source, a new table will magically appear in your tables tab of your Manage dialog box. This is known as a shadow table of the SQL source you placed on the relationship graph. You can add unstored calculation and summary fields in the shadow table and they will not transfer over to the actual SQL table. The same is true for auto enter and validation options applied to shadow fields. These act as a bit of a filter for your FileMaker behavior for those fields but doesn't place those settings in the actual SQL source field.

You can remove fields from the shadow table as well. This does not actual remove them from the SQL source, just from the display within the shadow table. From the way I understand it, removal of fields you don't need has the following benefits.

- less fields from the SQL source FileMaker needs to pull from as you navigate from record to record
- less fields that will show up in dialog boxes showing a list of fields (like sorting)
- helps you organize things a bit more efficently.

Clicking the Sync... button in the middle of the Manage database dialog box will restore the removed fields and synchronize the FMP shadow table with the SQL source table.
More info about the author and FileMaker in general, contact me at

© 2007 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.