How a wonderful piece of software can get ruined.
I love FileMaker. It is one of the coolest most powerful software applications available. I discovered it more than 25 years ago when I was looking for a simple way to track my books. What was a simple database program back then has grown up into a powerful application development tool for automating business systems.
But many businesses which are using it, however, begin to discover that their system, which was once so fast and snappy, has begun to slow down. Every week it seems to move slower. There are a number of factors which cause a FileMaker slowdown, but in this post I just want to address one cause.
Before I do, you may want to know my experience. I have been working with FileMaker for more than 20 years, I have developed many systems from scratch, and I have worked with systems built by others. I have learned the most by examining systems built by others, in most cases, sad to say, the lesson was ‘don’t do it this way’. Because many times, the systems were built by people who had little knowledge of the best way to design and architect a FileMaker database. Principle among the errors, and one of the primary reasons for many FileMaker slowdowns is an over-dependency on unstored calcs. And that is what I want to focus on here.
If your FileMaker system is slow, it is most likely because it is using too many unstored calcs. What is an “unstored calc” in FileMaker? FileMaker has a few different types of fields for tracking data. One of the most useful types is a “calculation” field type. Calculation fields can be ‘stored’ or ‘unstored’. A stored calculation performs its calculation once, and then retains the result of that calculation (mostly; for the sake of this post). The unstored calculation is constantly evaluating all the fields which comprise the calculation, and constantly updating. Because it constantly updates, it is a very useful tool.
Because it is so useful, people tend to overuse it. For example, suppose you have a subtotal field, which builds its calculation based upon the sum of all the line items. Every time a new line item is added, the subtotal updates. This is great, right? Yes, and no. Now imagine that you find many uses for unstored calcs all over your database. So every table has dozens of unstored calcs serving various functions. And every table has many records. As the system grows, more and more records are added to your database. After a while your FileMaker database has thousands of records, tens of thousands, and more. And every time you open a record, those unstored calcs, have to recalculate. Every single time; because they are unstored.
And this is how your system slows down. So how do you fix it?
The best thing you can do is to “flatten” the file, by changing those unstored calcs into static fields which retain their data in an unchanging fashion. There are a number of supporting steps you need to take to make that happen, but that is the essence of it. Change your unstoreds into stored, and find a way to update your data without depending upon constantly recalculating everthing. The end result is that you will have a fast, beautiful, and useful database system.