How to Make FileMaker Really Slow!

Here are the top three mistakes people make when trying to build with FileMaker Pro by Claris

FileMaker applications can be really fast and zippy, if they are built correctly. FileMaker is easy to use, which is great! But it is also easy to use wrongly, which is not so great!

So, with a tongue-in-cheek approach to life, I would like to share with you:

Three Ways to Make FileMaker Run Really, Really Slowly

Mistake #1. Use many, many Unstored Calculations

What is an “unstored calc”? It is a calculation field that is set to be unstored, or ‘calculate when needed’. It is a fantastic tool in FileMaker, when used correctly! It provides instant updates to the status of key data in your FileMaker database. So if an unstored calc field references some other field, in a completely different part of the database, it will reveal the results to you.

For example, maybe you want to always know what every Customer has paid you ever, that is, the “customer lifetime value” (CLV). You can just create a FileMaker unstored calculation that does a “SUM” function on all customer invoices. [ But I don’t recommend you to do this – remember, we are talking about the big three Mistakes made with FileMaker. ]. But you can see it is super-powerful. And because of that, it can be over-used. [ There are other ways to get the CLV of a customer, which do not require unstored calc fields.]

I once did an audit for a client to find out why their FileMaker business application was running so slowly. Didn’t take long. I opened up the Manage Database menu and looked at their Customer table. First problem, it had 600 fields! Second problem, 400 of them were unstored calcs!! That is called, ‘over-doing it!’

2. Use many summary fields on giant listviews with thousands of records

The second way to make FileMaker run really, really slow is to open all of your layouts into listview, with a ‘Show All Records’ command, and place at the bottom/top of each of those columns a summary field which will calculate the thousands of records loaded into that spreadsheet-like layout display. That way the summary fields cannot calculate until every single one of the thousands of records has first been downloaded from the server. Then you can tediously calculate each column header/footer for all the records.

What is a Summary Field? In FileMaker, a Summary Field is what is called an aggregate function. It’s value comes from grouping the results of many records together. Others are SUM, MAX, MINIMUM, AVERAGE, etc. These fields function because they need to gather the data from many records together to show their results.

Like unstored calcs, summary fields can be great and powerful tools. But with great power, comes great responsibility! Use them correctly. I once opened up an application from a new client, and was pleasantly surprised to see that there were NO summary fields in the entire application. It was very fast, and served his purposes very well, even without summary fields.

3. Best option to turn your FileMaker into molasses: Use unstored calculation fields that depend on summary fields!

This is where it gets really rich! Using an unstored calculation on a listview that summarizes all of those individual unstored calculations will slam your solution into a wall and stop it from doing anything for a long, long time.

There are better ways to serve your purposes, which do not require the simultaneous calculations of so many fields at once.

SOLUTIONS AND ALTERNATIVES TO THESE MISTAKES

Allow me to share just one sample to give you an idea of what can be done: Many times clients want to get sales totals for their business summarized by month, and then for the year. That is great. One way to approach this would be to schedule a script to run on the very last day of each month. After hours, the script can run a Find command for the month, and then run calculations and/or scripts to make all the necessary totals for the month. Then, and here is the key idea, you STORE that info into a separate History table. At the end of the year, you have a table with just 12 records in it. Then when you want to calculate your totals for the year, it is a simple matter to add up 12 records.

So that is just one sample idea of how to run a really zippy database application and get all the totals you need, without resorting to molasses-inducing options like unstored calcs or summary fields.

Feel free to contact us at HighPower Data & Design for any other great FileMaker (or web) solutions, you may need.

~William Miller