Considerations for Migrating to FileMaker 7 at MIT:
Notes Toward the Business Case (*DRAFT*)

by Kevin Cunningham
21 September 2004 [DRAFT]

INTRODUCTORY COMMENT: This document was originally written for a client at MIT, to establish the case for and against migrating to FileMaker 7 (the contract was approved, by the way). Having written the document, it became clear to me that FileMaker 7 is such a strong revision of the software that it should not only dispel the old (and somewhat valid) prejudices against FileMaker as a "poor IT citizen" (cf. my article on Security Issues)– but the new version should at last put FileMaker into consideration with the same class of tools as PHP/MySQL, Postgres, and other middle-range database applications. I'm speaking in the business sense – Return On Investment (ROI), reliability, robustness, available expertise, etc. – it would be foolish and counterproductive to start a religious war about which technology is "better". (Nevertheless, I do think it's fair to say now that Microsoft Access is by no means in the same class as these others.)

Determining whether to employ FileMaker as a technology for a business solution, in other words, has now become an issue of scale and resources, not of technological adequacy: FileMaker is entirely appropriate for mid-size solutions (say, up to 100 client users, or many more web users) but not necessarily for a large "enterprise" solution (though it might be!). Indeed, the decision to use FileMaker may now more commonly turn on the question "Can I find a FileMaker developer?" rather than "Is FileMaker any good?" In other words, workgroups may now decide what technology to use based on who they know and what technology that person happens to be proficient in – there is more than one acceptable technology now. (By the way, these technologies are not mutually exclusive: FileMaker can be front-ended by PHP, for example, and can talk to Oracle databases via ODBC.)

The question thus arises as to whether there is sufficent FileMaker 7 expertise around yet to justify "risking" one's development by using this technology. I think the answer is yes, there is a reasonably large and growing body of FileMaker developers knowledgeable in version 7 to feel comfortable that these developments would not have to be "one-shot" deals by isolated developers. As the most profound evidence of that, I can point to the membership and mission of the Boston-Area FileMaker Developers Association (BAFDA), which I helped found partly to clarify the size, skills, and availability of the FileMaker developer community here in Boston.

The impression may still be current that "FileMaker is simply inadequate", and that "skilled and reliable FileMaker developers are few and far between". It's time to give up both these ideas, because they simply aren't true anymore – at least with the advent of FileMaker 7.

--Kevin Cunningham, October 2004

Contents

• Overview
• What's Involved in Moving to FileMaker 7?
• Advantages of Converting to FileMaker 7
1. Conversion Advantages
2. Revision Advantages
3. Advantages of Using FileMaker vs. Alternative Technologies (Oracle, Access, etc.)
• Decision Points

Overview

Many offices at MIT conduct a good deal of their business through FileMaker databases.

With varying degrees of success, these systems attempt to efficiently model the office workflow – within the constraints of FileMaker version 5-6. So constrained, even the most successfully designed of these systems is subject to certain inherent weaknesses:

• Constrained by limits of file/field size and performance in FM6, they implement features in ways that inhibit robust use of the data.
• Working with limited connectivity options and nonstandard product architecture, the solution (indeed, any FileMaker solution), typically developed informally by some administrative agent in the workgroup, is a source of tension with those who might be expected to maintain it (i.e., IT professionals).
• Perhaps most significantly, these systems pass information across the network without any effective protection (besides obscurity) of either passwords or sensitive student data. This is especially problematic for systems which involve student data or other sensitive data, which should not be transmitted across the network so unguarded.

FileMaker 7, the latest release from FileMaker Inc., would enable these systems to completely transcend these issues. Furthermore, the many improvements of FileMaker 7 would allow for significant efficiencies in design, and FM7’s new relational model would make the revised system more conformant with standard database systems.

What’s Involved in Moving to FileMaker 7?

Migrating to FileMaker 7 can take two main forms:

1. “Convert & Restore” – more or less re-creating an earlier (pre-7) system in FM7, without taking advantage of any of the new capabilities of FM7.
2. “Expansion/Revision” – re-architecting the system using FM7 features and approaches, either by building on a “Converted & Restored” version, or creating a new system in 7 from scratch and importing data from an earlier version.

Because FileMaker 7 is a fundamental reinvention of the FileMaker product line, conversion to FM7 is not as simple as upgrading has been in previous releases of FileMaker. While there are excellent conversion utilities available, even with these aids the migration of a system from pre-7 to 7 is an extensive process, involving a series of complex and work-intensive steps:

1. planning for the conversion (setting up work areas, gathering tools, etc.)
2. analysis of the existing system (determining what functions the system embodies)
3. pre-conversion work (preparing the databases, optimizing the state of the system)
4. actually converting files (usu. several iterations to catch things)
5. post-conversion cleanup (there are many known changes to be examined)
6. post-conversion testing (incl. user testing, to assure that converted system works)
7. data import (if conversion used empty clones)
8. deployment (via server) to users

One FileMaker conversion guide lists 44 steps, while FileMaker’s official conversion checklists note 60 areas to check during the process. Among the areas of the system that need to be scrutinized whenever a conversion is undertaken are the following:

• account/password/privileges (authorization is radically changed)
• table/file definition
• fields (incl. validations, auto-enter, indexing changes, etc)
• relationships (they’re specified through a completely new graphical interface)
• file references (these were not accessible to users in earlier FM versions)
• value lists
• layouts/windows (there can now be multiple windows per underlying table)
• scripts (there are many known gotchas to be checked)
• import/export/printing (these need to be verified)
• other I/O connectivity (ODBC/SQL, links to warehouse)
• web publishing features (there are separate conversion utilities for this too)
• plug-ins (not every plug-in works with FM7)

(Not all of these areas are relevant to every FileMaker-based solution. For instance, many MIT FileMaker systems don’t use web publishing, others don’t use ODBC, some are single user, etc.)

Also useful as part of the conversion is introducing standardization in naming conventions, etc., and documenting parts of the system (now easier in FM7).

Just to be clear: converting to 7 is not usually fraught with problems – there are just many things that need to be checked. A typical low-end system can be converted and got running in a few hours. More complex systems demand more attention paid to them to assure they are converted properly.

That’s just “Convert & Restore”. If the database is to be revised and expanded as well, other considerations come into effect depending on the nature of the change. Among the kinds of changes often introduced to systems going to FM7 are the following:

• revising existing functionality to take advantage of FM7 features (for example, eliminating the workarounds hitherto needed to view a subset of related data – this can be done much simpler now)
• consolidating elements (multiple tables in one file – this makes maintenance much easier, including allowing accounts/privileges to be handled in a single file)
• expanding functionality (enhancing/adding features, adding new reports, creating custom functions that reflect a specific business model, etc.)
• creating “hub and spokes” or “separation model” architectures (these allow the easy development of whole new views of the data, and make the system future compatible with transitions to enterprise systems such as Oracle)
• and many others...

Fortunately, FM7 is designed to allow a phased approach to migration. Not every system needs to be converted at once, and FileMaker 7 can be run without conflict on the same machine running an earlier version of FileMaker (though they can’t interoperate). Thus it is possible to migrate self-contained systems at different times.

One other main comment about migration. In general, basic conversion essentially re-creates the database architecture that was present before FM7. While this will provide several immediate benefits (performance, security, etc.), it may not be where the true benefits of conversion will be reaped. Typical pre-7 databases are filled with stopgap approaches to database issues that are simply no longer present in FM7. To take just one example, there are many approaches to generating reports that use an abundance of old tricks (many extra fields, relationship workarounds, etc.) that are simply not needed in FM7. This suggests that for some of the systems, the powerful gains will be found in revision, not just conversion.

Advantages of Converting to FM7

Depending on what is undertaken (conversion only, architectural revisions, expansion of functionality, etc.), there are many benefits to moving the pre-7 systems to FileMaker 7.

1. Conversion Advantages. As noted above, conversion itself will immediately provide security and performance enhancements. The data transmissions can be automatically encrypted, and the interaction with the databases will benefit from the much more efficient algorithms that FileMaker Server uses to send packets of data to users. (FM Server in fact now does a lot of the data processing itself, speeding up client interactions.)

In addition to these obvious advantages, converted FileMaker 7 files are also free of corruption (a common issue in FM6), and the client and server are much more stable products, so crashes are much less likely. FM7 files are also stored as Unicode, not ASCII, so it is now possible to back up these files using TSM without fear that the data is being sent in a vulnerable way (which was the case with FM6).

Furthermore, FileMaker 7 provides a much friendlier environment for development work: not only is it easier for developers to change features of the system (and the available palette of tools is bigger), but these changes can now be made without interrupting clients who are using the system. In other words, developers no longer need to kick users off the system to make changes. Likewise, server backups can be run on the live system without impacting users hardly at all. (No more “coffee cup” blocking users from getting work done.) This in itself helps avoid much wasted productivity, and allows more frequent backups if that is desired.

2. Revision Advantages. If revisions are undertaken to the systems, the possible advantages become even greater. Among these are the following (by no means a comprehensive list):

• Eliminate the need for archive files (and the extensive related work). To work around limitations in file size, performance, and the relational model in FM6, some solutions require that a new set of data tables be constructed regularly (e.g., each semester). Past files are retained for archival purposes, but the data is largely inaccessible. In FM7, these tables could be consolidated into persistent tables (FM7 files can hold much more data than FM6 files) and relationship-filter techniques could be used to view the current data. Longitudinal views of the data would also then become easily possible, allowing reports and analysis previously unthinkable.

• Significantly streamline the importation of data, and introduce a more standard database modeling architecture. In conjunction with the previous issue, solutions requiring new tables on some regular basis will inevitably present the problem of how to populate those fresh tables with needed initial data. And regardless of the way the data is populated, many FileMaker solutions are created without due consideration of an appropriate data architecture (one developed using “ER diagrams” for example). Revising the system could streamline the way data is imported into the database (e.g., using automated ODBC/SQL calls to a Warehouse) to create its initial data set, and could also put a better table organization in place that would eliminate typical duplications of data elements.

• Many more reporting options would become possible, and they would be much easier to realize. The current scheme for generating reports in the many systems requires extensive developer effort, and it’s very difficult to create a new report. What’s more, to give an end-user appropriate access to change layouts in pre-7 systems requires they be given basically full access to the file. With the techniques and tools available in FM7, the creation of reports could be much easier, could be dynamic (allow selecting criteria in real time), and could be extended to end-users without threatening the integrity of the core system (in other words, users could be given the ability to create their own reports without having to be given full access to the system). Furthermore, the options for presenting report data are greatly expanded in FM7 (for instance, you can put data in multiple columns with new portal options). Regardless, the effort required to create a new report would be greatly reduced.

• Navigation and usability of the systems could be greatly enhanced, especially through the new window options. The current systems work within FM6’s paradigm of “one window per data table”. FM7 allows more than one simultaneous view of a table’s data, opening up interface options not possible before. Take a simple example: suppose a user is looking at a list of students and wants to drill down to a particular student’s record. In FM6, the user can switch to another layout to see the record details, but in doing so the user loses the list view. In FM7, the user can open a new window with the detail view, while leaving the list view visible in the original window. In fact, the user can open any number of windows and could, for instance, compare two students’ records at the same time, a feat not possible in FM6. That’s just an example: the possibilities of streamlined interface – or expanded interface – are vast.

• Offer secure, reliable, and direct web interfaces to the data, eliminating much of the hardcopy efforts and HTML generation in the current system. Web publishing in previous versions of FileMaker was an iffy proposition, with a weakly featured web engine and a not-very-robust application (FileMaker Unlimited) behind the scenes. FM7’s web publishing engine is completely revised, and relies on a true web server (Apache or IIS, with SSL encryption if desired) processing sessions hosted by FM Server 7 rather than an FM Pro application. The implications of having a reliable web service are far-reaching for improving business processes. Furthermore, the required effort to create a useable interface is greatly reduced in FM7 – you can create a layout in FileMaker itself and, through Instant Web Publishing for example, it can be made accessible to a web user in that form with no extra design effort required.

One point perhaps to emphasize is that, unlike FM6, these improvements are not particularly difficult to implement in FM7. It is one of the important features of 7 that many of the tasks that used to be hard to do in FM6 are now rather straightforward in 7. Developers familiar with earlier version of FileMaker might look at the above suggestions and say “well, yes, we could get an effect like that in FM6 – with a lot of effort.” With FM7, much of that “effort” is gone.

These only suggest what is possible in FM7. Clearly, a common thread of the above list is that substantial efficiencies are possible by revising pre-7 systems – efficiencies that eliminate a lot of the now-unnecessary manual interventions embedded in the current systems. The other main thread is that sharing data in a controlled secure way is much more possible now. Beyond these two main ideas, there are many other improvements possible with the new features of FM7.

3. Advantages of Using FileMaker vs. Alternative Technologies (Oracle, Access, etc.). The previous sections concentrated on the advantages of using FileMaker 7 versus earlier versions of FileMaker, since the existing systems of interest here are currently implemented in FileMaker technology. It might be helpful to also consider the advantages of using FileMaker versus some of the alternative database technologies (Oracle, Access, MySQL, etc.). Experiences with older versions of FileMaker suggested (reasonably) that the product was a second-class citizen compared to these other technologies, but FileMaker 7 has changed all that. In fact, it is probably the case that FileMaker provides the best ROI of any of the major database options.

Objections to FileMaker as “not enterprise worthy” were valid for previous versions of FileMaker, but are no longer valid with FileMaker 7. FileMaker Inc. specifically developed this version to directly address concerns of appropriately skeptical IT practitioners who did not want to be responsible for managing enterprise data in an application that was sub-par. So FileMaker 7 is a complete change from earlier versions:

• FileMaker data can now be completely secure, both in transmission and in account/privilege management (which can even piggy-back on OS account management tools).
• The server technology is incredibly stable and is run as a system service not an application any more.
• The application program is also stable and not subject to crashes (and consequent corruption) the way previous versions were.
• The web technologies are industry standards like Apache and XML.
• The data architecture is now archetypally relational, to conform with standard database practices.

The list could easily be enlarged. The point is that limitations that previously made FileMaker a weak contender with other database heavyweights are now gone. It is a legitimate technology for medium-sized enterprise systems.

So it is a contender. But still, why choose FileMaker?

First of all, FileMaker is universally recognized for its developer- and user-friendly interface, compared with other systems. FileMaker embeds in its interface many business logic elements (e.g., field validation, search capabilities, record and account management, ready-to-use portals, a variety of display options, etc.) that would have to be created manually in other systems. And even end users can in many cases create layouts and reports in FileMaker – other systems require devoted developer time to provide such extensions.

Perhaps more significantly, FileMaker provides features not available in other database systems (such as calculated fields, easy-to-manage relationships, an extensive scripting and interface development environment, and now custom functions) that greatly speed up development time.

The net result is an eminently usable and easily modified system developed in a much shorter time than could be expected in other technologies. And quicker development time translates directly to lower costs overall.

(Why wouldn’t you use FileMaker? If you need to serve a very large community – more than 250 simultaneous users – or need a system with built-in transaction management – i.e., allowing an audit/backout trail going far back in time – then FileMaker is not the appropriate tool. Also, if you cannot use Macs or PCs, FileMaker isn’t available. Finally, if there is no FileMaker expertise available but there is expertise around some other technology, then FileMaker is obviously a vulnerable choice. Apart from these considerations, FileMaker 7 is now a perfectly worthy choice for organizational solutions.)

Departments at MIT regularly consider migrating FileMaker-based data systems to other technologies – presuming (from outdated information) that FileMaker is not worthy of an enterprise solution. (For instance, there is a presumption that Oracle is the de facto appropriate system for business workflow.) However, FileMaker 7 is eminently capable of handling the needs of such systems. Indeed, it would be quite less expensive to implement such systems in FileMaker 7 than in almost any other technology.

In any case, it’s time to reconsider FileMaker, for FileMaker 7 is now an entirely acceptable – and in many ways advantageous – development environment.

Decision Points

In spite of these points, the decision to move to FileMaker 7 is not a simple yes or no call. Some systems could be migrated to FM7 while others might remain at FM6. Some could be only converted, while others could be converted then significantly revised in a variety of ways.

The following list of questions is intended to make explicit certain questions that will need to be asked and some of the consequences of the answers. The questions are listed broadly in chronological order – e.g., the answer to earlier questions might very well preclude even asking later questions. These are not all the decision points, but suggest the broad set of issues that arise in relation to migrating a typical system to FileMaker 7.