Dogged by a Slow FileMaker Database?

Cache in on this Quick Tip to Improve Layout Load Times & Report Generation

By Joe Cellino, FileMaker Developer

Have you ever wondered, “What is taking this report so long to generate?” or “Why does this layout take so long to load?” It may be that your FileMaker fields are set up inefficiently. If you have unstored calculation or summary fields on your layout, this will most certainly be the issue. Just as the names indicate, “unstored” calculation fields and “summary” fields don’t store any data. They must calculate the data each time the field is displayed. It may be easier to use unstored calculations in order to get your database functionality working, but this will inhibit your system from scaling with your business in the long run. The good news is — you can improve slow FileMaker performance with this data caching trick.

Let’s look at an eXample.

Let’s say we have a database with an Invoice and Invoice Lines table. The Invoice Lines table has a field called Totals — this is an unstored calculation with the formula Quantity * Price. Likewise, on the invoice table we have a field called Subtotal — this is also an unstored calculation field that sums the related Invoice Lines Totals. Each time you enter the Invoice layout, FileMaker must take time to generate the results of all of the Invoice Line Totals and then sum those results.

Now, this isn’t too much of a problem with one record, but imagine trying to calculate 10,000, 100,000 or even 1 million invoices for a report! You could take a heck of a coffee break while you wait. As you can see, this has the potential to be a very laborious process (i.e. time-consuming), even though it’s a simple calculation. Now imagine what your FileMaker system must go through for some of the complicated calculations we developers create.

You’re probably thinking, Well, I need my totals to calculate. What else can I do? Cache your data! The definition of cache is “to store away in hiding or for future use.” You want to store your data so that you can run reports in the future.

Let’s make the necessary changes in our eXample above to improve FileMaker performance.

Change the Invoice Lines Totals field to a number field and use the “Auto-Enter Calculated value” option for the formula. Ensure the “Do not replace existing value of field (if any)” option is unchecked so that the field will recalculate whenever the Quantity or Price changes. This solves half of the issue, but changing the Invoice Subtotal is a little more tricky.

If you were to make the same changes as we did for the invoice line and make the Subtotal a “Number” field with an “Auto-Enter Calculated value,” you would notice that it does not re-calculate whenever the Invoice Line values change. The “Auto-Enter Calculated value” will not re-calculate if the field referenced is in another table. To get around this issue, we will have to use some scripting. We can add a script to the OnObjectSave script trigger of the Quantity and Price fields that will calculate our Invoice Subtotal. You should also ensure you update the Invoice Subtotal when deleting an invoice line record.

Congratulations, you have cached your data and improved the performance of your FileMaker database! See how much faster your reports generate now. As a developer, I consider data caching a matter of good practice. My job is to not just create the desired features requested by clients but to ensure they are set up as efficiently as possible — for the present and into the future.

Stay tuned for some more advanced caching in my next article: speeding up dashboards.

Are you a citizen developer? FileMaker is a great do-it-yourself tool, but it is hard to know everything and easy to overlook how data structures impact functionality (as in the above eXample). If you enjoyed this tip, check out our FileMaker Pro Coaching & Consulting Services. We can hook you up with a developer like Joe who can teach you tips like this caching trick to take your FileMaker development to the next level.

Want to meet some real in-house developers and business owners who utilize eXcelisys’ coaching/consulting services to improve their FileMaker craft? Check out: FileMaker Pro Coaching / Consulting … Because 2 Heads Are Better Than 1

**This article is provided for free and as-is. Use, enjoy, learn and eXperiment at your own risk – but have fun! eXcelisys does not offer any free support or free assistance with any of the contents of this blog post. If you would like help or assistance, please consider retaining eXcelisys’ FileMaker Pro consulting & development services.