Building Multi-User FileMaker Applications

FileMaker is a great platform for building multi-user applications. It’s easy to get started, but powerful enough to build applications that can run an entire business. One of the reasons it is so easy and powerful is that it does a very good job of handling the complicated issues that arise from allowing many users access to the same data over a network.  However it can’t do it all by itself. It’s up to the applications developer to use best practices to make sure the application remains fast, and is protected from the harsh realities of the real world.

…you will discover that as your database grows, your reports will grind to a halt.

To be clear, these are not bugs or things that FileMaker is doing wrong. These are issues that plague any application that has to account for multiple users, accessing shared data over networks. FileMaker, like any good relational database, has the means to deal with these issues, but it is up to you, the developer, to implement them. We cover a few of the major issues in this article.

Slow Reports and Views

FileMaker has lots of great ways to summarize data either using Summary fields or any of the aggregate functions. But if you are trying to summarize large numbers of un-stored calculations, especially through relationships, you will discover that as your database grows, your reports will grind to a halt. Although I am picking on reports, it’s not just limited to view only, traditional report style layouts. Any FileMaker Layouts that depend on summary calculations that reference un-stored calculations through relationships will suffer the same fate.

One solution is to de-normalize the data. When your data changes, you push that data out into stored fields then you build your reports so that they reference the stored data instead of the un-stored calculations. Another option is to pre-summarize the data. The idea here is to create special reporting tables that are populated on the fly with indexed, pre-summarized values. This concept is is sometimes called data warehousing.

Another user may have locked your record!

The downside to these methods is that now you have the same data stored in more than one place. As the developer, you have to make sure that the data stays in sync. This has to be done with some care. As I point out a little later, updating many records with a script has its perils that must be dealt with as well.

There are many other ways to optimize the performance of slow reports and other layouts.  Most of them boil down to avoiding using large numbers of un-stored calculations, especially ones using relationships.  If you develop with FileMaker long enough you will likely need to learn a quite a few of them.

Records Aren’t Always Editable

Why? There are many reasons. Another user may be editing the record. The user running the script may not have sufficient access privileges to edit the record. Field validations may prevent the field from being edited. Maybe you can avoid using field validations and record level access, but you can’t avoid record locking. It will happen. You need to make sure that you are accounting for these eventualities.

Learn to use Get(LastError). This FileMaker function can tell you when things go wrong with your scripts, and then you can take the appropriate action if an error is encountered. If the error code is 301, Record Locked, maybe you can try again after a second or two. At least you might want to warn your user that something went wrong.

Sometimes the easiest thing to do is to Revert all the changes your script tried to make and exit the script, leaving the database unchanged.  This is a clean known state. There is no question of what got saved and what did not.

There is no guarantee that your script will complete.

This is possible, and not even that difficult. Database transactions are the industry standard approach to “reverting”, although usually it is called “rollback” not “revert”. Filemaker does support database transactions. You can learn how to craft your scripts so that they are transaction safe.

Scripts Don’t Always Complete

This is really true.  I am not making it up. Nothing anywhere in the FileMaker engine can ensure that a script will complete 100% of the time. There are the obvious things like the computer crashing or FileMaker crashing that can get in the way.  Maybe you can convince yourself that doesn’t happen enough to be a problem.  But what about the network going down?  What if your users are accessing the data over a public wifi network?  What if they close the lid on their laptop?

If you are using FileMaker Go connected to a served file it’s even worse. The user can press the home button or answer the phone, and forget to comeback to FM. At some point the operating system will force FileMaker Go to quit. FileMaker will try to reconnect and keep going, but it can’t always. And when it can’t, the script just aborts. The user is left on what ever layout the script stopped, possibly with records open.

Refreshing a WebDirect window is the rough equivalent of force quit!

Then there is WebDirect.  If you have script running and the user closes the browser window or refreshes the browser, guess what happens to your script? It just stops, dead where ever it happens to be.

I know it’s harsh. But the truth is scripts may die partially along the way. It’s not avoidable. Maybe this isn’t a really big deal in your solution.  Maybe you don’t have any scripts that are long enough or complex enough for this turn into a problem. But if you do, you are going to want to deal with this issue.

Again, the answer is database transactions. If you write transactions safe scripts, you don’t have to worry about this. The script will still abort, but the user is booted from the file without the commit. If the commit doesn’t occur, then NO changes will be made at all. Everything is automatically reverted.

There are other problems, but I think these are some of the biggest. The slow interfaces become very obvious very quickly. But the other issues can lie hidden for a long time as they only affect the solution under certain conditions. These types of problems are the source of many of those weird data problems that you can’t quite ever figure out. You might find yourself asking something like, “Why doesn’t that field have the right data? All the rest do. The script works when I test it. I don’t get it.” It may be that the script just couldn’t do what you told it to do, or something caused the script to quit.

I want to re-iterate that these are not FileMaker bugs or problems. FileMaker does a great job of handling many of these issues. But occasionally it needs your help. It needs you to give it the proper instructions so it can keep your multi-user FileMaker applications humming along.