A few years ago, when IT Solutions decided to implement an Agile methodology to manage our development projects, we needed a project management tool powerful enough to handle all of our projects, flexible enough to change as we worked out our methodology, and accessible enough to allow remote connectivity from our various client locations. Of course we turned to FileMaker to get the job done. As our methodology came together, the foundation for our solution was put into production. With data pouring in, the inevitable need for reporting grew, and with it the need to compare estimates and progress against actual time spent.
We were at a crossroads since our time entry system is based on Microsoft SQL Server. As a relatively small division of IT Solutions, it wasn’t reasonable for the application development project team to dictate how time is tracked. Fortunately for us, FileMaker 9 introduced a brand new feature called External SQL Sources (ESS), which allows real time connections between a FileMaker system and a separate SQL system.
After making the connection to our SQL- based time tracking database, our project management solution is complete. Employees enter all of their time in one place, whether it’s project development, support work, or internal meetings and initiatives. I have real- time access to those records and can relate a project record in my FileMaker system with the time entry records relevant to that project in SQL Server. I can report project progress at any time based on real time data from both systems.
Here’s a look at how ESS works.
Setting up an ESS connection
Connecting a FileMaker database to an external SQL data source is relatively easy, even if you don’t have a lot of experience working with SQL databases. While technical instructions are outside the scope of this article, I will present some high- level concepts on what it takes to get up and running with ESS.
The most important key is setting up a DSN (data source name) on your computer if your database is local, or on the server if you’re hosting databases using FileMaker Server. The DSN contains all of the information that FileMaker will need to know how to access the external data source, such as the database location and credentials. Setting up the DSN depends on your operating system and the kind of database you would like to connect to. There are detailed instructions for setting up a DSN available in a public technical brief called “Introduction to External SQL Sources”, available at www.filemaker.com. Simply enter “tech brief ess” in the Quick Search field at the top of the main page of the FileMaker web site to access it.
After your DSN is setup, you can create an external data source for it in your FileMaker database. Access External Data Sources from the Manage sub-menu, which is located on FileMaker’s File menu. From there you can add references to other FileMaker databases or to ODBC (external) data sources.
When you add a new reference, selecting ODBC will display the DSN field and the Specify button. This button will display a list of configured DSN entries. After selecting the appropriate DSN and entering a username and password for the external system, FileMaker is ready to use the external data.
Tables from the external source can be added to the relationship graph as table occurrences and accessed like any native FileMaker table. The data source can be accessed by selecting the Data Source drop down at the top of the Specify Table box when adding a new table occurrence to the graph. The ESS tables will then appear in the box.
Working with ESS tables
Because table occurrences on the relationship graph can be based on ESS tables, you can reference them just like any of your native FileMaker tables. You can use them in relationships, base layouts on them, or reference their fields in calculations and scripts. FileMaker will even add the ESS tables to the table list in the Manage Database window. ESS tables are called shadow tables and are listed in italics so you can differentiate between them and tables that are internal. While you cannot add or delete fields in an ESS table, you can add calculation or summary fields to your shadow table in FileMaker, thus extending FileMaker’s powerful calculation engine and reporting functionality to your external data.
What ESS is and is not
While it may be tempting to use FileMaker as a front end to a SQL database , FileMaker, Inc. has made it very clear that the intention of ESS is real- time access to some of the data in your external data sources. Supported external systems that you can connect with include: MS SQL Server 2000, MS SQL Server 2005, MS SQL Server 2008, Oracle 9i, Oracle 10, Oracle 11g, MySQL 5.0 Community Edition, and My SQL 5.1 Community Edition.
This feature was never designed to allow FileMaker to act as a fully functional “front end” for SQL databases. It was, however, designed as a tool to allow FileMaker developers to further integrate their solutions into an organization’s overall data strategy.
Over the past few years, we’ve learned both the power and limitations of ESS. If you have any questions about whether or not ESS is right for your integration challenges, please do not hesitate to contact me at firstname.lastname@example.org or 866.PICK.ITS.