FileMaker Pro is a database manager from FileMaker Inc, a wholly owned subsidiary of Apple. It is a capable produce that has been around for over 20 years and is the dominant Mac-based database manager, though there is also a Windows version. FileMaker has evolved relatively slowly, with more focus on usability than on features. In comparison to Microsoft Access, FileMaker wins on usability and scalability, but Access has a more traditional approach based on SQL and programming with Visual Basic for Applications. FileMaker has a drag-and-drop script editor and support for AppleScript on the Mac.
Although the script editor is frustrating for someone used to writing code, it does work. As well as manipulating the data, you can set and retrieve local and global variables, perform loops and display custom dialogs; it is not as limited as it may seem at first.
A FileMaker database can be huge, with 8 terabytes specified as the theoretical limit. External databases are accessible through ODBC on both Windows and Mac.
The number of users supported by FileMaker is limited. The desktop product supports up to 5 concurrent users, and FileMaker Server up to 250 users. FileMaker has its own built-in security system, though FileMaker server can also authenticate against an external directory. Security is fine-grained, and you can even specify permissions for an individual record.
I have not looked at FileMaker for a few years, but renewed my interest when the company came out with FileMaker Go, a runtime client for Apple iOS. Given that FileMaker runs scripts you might have thought this would be restricted, bearing in mind this provision in the App Store guidelines:
2.7 Apps that download code in any way or form will be rejected
This is normally taken to prohibit runtimes like Java or Adobe Flash/AIR. Well, either someone decided that FileMaker scripts are not code; or there are special rules for an Apple subsidiary, which is reasonable enough. Anyway, FileMaker Go is in the App Store and does run scripts.
What this means is that you can create apps in FileMaker Pro and deploy them to iOS without going via the App Store. There are two models. FileMaker Go can open a file hosted by FileMaker desktop or server, in which case it behaves like a Mac or Windows client, or alternatively you can transfer a file to FileMaker Go to run locally. Transferring a file is easy using iOS launch service; essentially, if you can access the file via the internet or an email attachment, you can just tap it on the device and it will open in FileMaker Go. The advantage of running locally is offline use, whereas the advantage of the client-server model is that all users have the most up-to-date version of the data, and the database can be much larger. FileMaker is a real server application; this is not just file sharing. This also means that FileMaker must be running with the database open if you want to to use the client-server approach.
I tried FileMaker Go with a simple example and it works well. In essence it is delightful; you just open your database either locally or over the network, and it works. Here is a sample app on the iPhone 4:
That said, there are things that do not work, spell checking for example. It is also stripped of anything other than client features, so you cannot modify database structure, create new databases, or publish from the device to other clients. You also have to be careful with layout size. Most layouts designed for the desktop will need modification to work well.
There are a couple of issues. One is performance. It is just about bearable, but has that lethargic feel that you get with interpreted code on a relatively slow processor.
Another issue is synchronisation. If you want to work offline, how do you update your main database with any changes? The issue is little different with FileMaker Go than it is with a laptop, and it is discussed here. You have several choices:
1. Don’t synchronize, use client-server.
2. Treat your local database as read-only.
3. Use import and export. Existing records will simply be overwritten by imported ones.
4. Use a third-party tool. However the tool mentioned here, SyncDek, probably does not work with FileMaker Go since it needs to run a Java process on the client.
5. Roll your own. “FileMaker Pro has all the tools needed to create a robust synchronization system” says the guide; but it is non-trivial to implement this.
It is worth mentioning that FileMaker Pro also has an Instant Web Publishing feature that gives another route to mobile access and may perform better. There are pros and cons. The big one is offline, only available with FileMaker Go. Another is scripts. Some scripts work in Instant Web Publishing, but FileMaker Go is more compatible in this area.
I think this is significant for businesses where iOS devices are turning up. Many business apps do resolve down to forms over data, and this is is an easy way to deliver this kind of application to iOS users.
How is FileMaker pro as a programming tool? Just for fun, and because I have done it for other mobile development tools, I built a calculator in FileMaker. I do not recommend FileMaker for general-purpose programming; but it has the essentials, a form designer and scripting. Here is the result on an iPhone 4:
Oddly the biggest struggle I had was finding an easy way to display the input and result. In the end I added a field to the database just for this purpose. If a FileMaker expert could let me know a better way to update a text label on a layout via script, I would be interested to know.
The calculator is slow too, not for the calculation of course, but the operation of the user interface. Still, it does demonstrate that FileMaker Go is indeed able to download code and run it.
Update: I replaced the display field with a merge variable, which works better as it avoids focus issues and the keyboard popping up. Thanks to the commenter for the suggestion.
While it is true that the number of users supported by FileMaker Pro is limited up to 5 concurrent users and FileMaker Server supports up to 250 users there are no limits to the number of connected users with FileMaker Server Advanced. Thanks.
Yes, performance can be an issue if one takes a legacy database created for FMP desktop and attempts to run it unmodified on FM Go. But anyone who is serious about FMP development and planning to deploy a solution via FM Go knows (or learns quickly), that the solution must be optimized for iOS performance and UI constraints. A good summery of this topic is the FileMaker Go Technical Brief here:
http://www.filemaker.com/products/filemaker-go/docs/techbrief_filemaker_go_121_en.pdf
More FileMaker Go development resources can be had here:
http://www.filemaker.com/products/filemaker-go/resources.html
You may also want to look for the product GoZynch for a turn-key sync’ing solution for FileMaker Go deployment.
Regarding your calculator example: it’s not really a fair example of a FileMaker solution. FileMaker is first and foremost a database system, not an application programming environment. It is best suited to applications, or solutions as they’re more commonly called, that store large amounts of data for quick retrieval and analysis. A better “calculator” solution for FileMaker might be one that emulates the old style calculator that used a paper roll to print calculations and results. In that case, each record in the database would be a new row on the printout.
However, to get better performance from your existing calculator example, there are some things you can try. First, try changing the field you’re using to display the result to a global field (Field Definitions, double-click the field name, and then Storage Options). Global fields store a single value that is available across all tables and records in the database, and is not indexed by FileMaker, so the result should display immediately in this type of field. Another option is to use a global variable to hold the calculator result (i.e., a variable name prefixed with $$ instead of $). You can then display the $$global in the UI via the Insert menu > Merge Variable command. Note that merge variables don’t always refresh immediately in the UI, so you might need to follow it up with a Refresh Window script step, which in turn could potentially reintroduce performance issues. Another alternative would be to create un unstored calculated field that simply returns the value of the global variable. Just make sure to check “Do not store calculation results” under Storage Options in the field definition so that FileMaker does not attempt to store and index the result as it was built to do – it is a database after all.
Thanks for the suggestions; and I did read the technical brief you mention.
Yes, I realise this is not what FileMaker is for but it was an interesting exercise. I tried using a calculated non-stored field but got “not modifiable” errors. I didn’t think of merge variables though – will have a look.
Tim
> I tried using a calculated non-stored field but got “not modifiable” errors.
Right, the value of a calculated field cannot be set, it simply displays the value of its calculation. So if you create a calc field whose formula is “2 + 5,” it will display 7. In your case, you would create a calc field whose formula is simply “$$the_result”, or whatever your global variable is called. The field would simply display the value of the global. When you create the calc field, you data type it so it returns text, number, date, and so on, so in the script or whatever sets your global var, you would need to be cognizant of data type you put into the global; i.e., if the calc field is set to display numbers, you wouldn’t want to put long text error messages into the global. Still think a global field might be the simplest most straightforward solution to this particular problem though.
Thanks. The other problem I had with the calculated field is getting the darn thing to refresh. Actually the merge variable works best. The reason is that in FM Go, when the focus shifts to a field the keyboard pops up. If your app is entirely button-driven that is unfortunate. I fixed by immediately using Goto Object to shift the focus. But the merge variable is a better solution.
Tim
I am currently researching a solution that would allow auditors to work offline on an iPad and sync the results of the audit with a central database. It sounds as though this would be a viable solution. Thoughts?