We’ve all been there as FileMaker developers: we get a new client who needs a relatively simple request. A small application for a specific use case. Then you get another request to extend the system. And another. Before you know it, that simple structure has grown to monstrous proportions — and what was once a smart, agile little database now feels slow and stodgy. At which point we say to ourselves: if only we’d known it was going to be this big, we would have built it differently.
Well, with that in mind, we put together some best practices — ways of developing from the start regardless of the size of the project.
#1 LIMIT UNSTORED CALCULATION FIELDS
Calculation fields are easy to throw together. They can help you deliver value to your client quickly and easily, but at a long-term cost. Un-indexable calculation fields are notoriously slow to display or search. Best practice is to review the workflow and find a point where the calculation can be calculated and STORED. Once this is established, you can create a script, button or trigger to calculate and store the information. It will take a bit more time to rework the workflow in the beginning, but it is worth it; a small added cost up front will end up paying off significantly in long-term performance and reliability.
#2 SIMPLIFY RELATIONSHIP GRAPH
Over the years, the FileMaker community has come up with many different ways to use the relationship graph. There are many innovative techniques to solve a variety of use cases. The problem with this: it can make your relationship graph overly complex. Every time the database is opened, the database engine has to read the entire graph. This also happens during POS calls, scheduled scripts and data API calls. If your graph is overly complicated, it can slow all of these things down. Plus, from a developer point of view, a convoluted relationship graph can be hard to understand - which can increase the likelihood of errors or repeated relationships. So before you create a new relationship in your graph, think about if there is a way you can script the process instead.
#3 MULTIPLE FILE SOLUTIONS
Quite some time ago, FileMaker developers gained the ability to have multiple tables in one file. This simplified solutions greatly – no longer were you required to have a file for each table in your application. However, as these things usually go, we have found that there are performance gains to be had in thoughtfully splitting your solution into separate files.
At a high-level, this is because all data and schema changes, when saved, involve writing to the actual physical file. This is handled by the OS. In a high transaction file, one in which you have many users committing records often, these “writes” will queue one after the other and eventually can slow things down. Additional benefits to splitting your solution up into multiple files include more flexible backup options, faster restores on smaller files, and faster backups now that FileMaker server can do parallel backups. This technique can also reduce record locking since that happens on a file by file basis.
So, as you start a new "small" solution, keep these best practices in mind. Avoid unstored calculation fields. Keep your relationship graph as simple as possible. And think about breaking your solutions up into multiple files. If you do this early, it will extend the life and scalability of your solution and help you avoid having to go back and change things once your solution inevitably grows.
If you're interested in learning more about The Support Group's FileMaker development services, please contact us.