Success Story of the ACF Plugin for FileMaker

FileMaker is a great tool for creating applications and databases, especially for individuals without extensive programming experience. It allows you to create beautiful user interfaces and interconnected database definitions that work seamlessly. However, when it comes to more advanced functionality, one often hits limitations or has to create it in a way that becomes overly complex. After working with large systems in FileMaker for years, I sat down and created an extension that provides the ability to write code in a more conventional manner. I named it ACF – Advanced Custom Functions for FileMaker. This became an entirely new programming language inspired by many different others I am familiar with and have used.

I had recently encountered a case where an invoice needed to be calculated. This was solved using what is known as “calculated fields” in FileMaker, a type of field that doesn’t store data values like normal fields but rather calculates the value based on other fields and other calculated fields. Such an invoice is typically saved as an Invoice Header, which represents the actual invoice, and another table with line items linked to this invoice. The line items have quantity, price, discount, line total, VAT, and line total with VAT. All of these are calculated using calculated fields, which means that every time an invoice is displayed, a multitude of calculations are performed in the database. The invoice is recalculated each time it is viewed.

This can be quite demanding, especially when you’re trying to create a report with, let’s say, 500 invoices, and each one needs to be recalculated one by one to generate the report. It becomes very time-consuming.

To address this, a set of parallel fields was created, which were regular data fields where the calculated values were copied in. This copying was performed when an invoice was approved.

Then, for layouts and images that needed to display field content, a set of calculated hybrid fields was created, which, based on the approved status of the invoice, would choose either the calculated fields or those with stored values.

You can see that the picture has become rather complex to achieve the necessary performance while remaining accurate. In another system, you wouldn’t work with “calculated fields” but rather data fields that get updated when changes are made. However, achieving this straightforwardly in FileMaker can be a bit complicated.

The problem with the existing routines that were in place to perform these calculations was their instability. They didn’t always do the job, and it was assessed that it likely took less time to create an ACF (Advanced Custom Functions) function to do this instead of troubleshooting the original function.

Here, I solved the problem using an ACF function I created. With this, I could create a procedure that calculated the entire invoice and updated all the necessary fields in a single function. It became a very clear and effective solution. You can read more about the function in the manual on this page.

You can find more information about this ACF-plugin in the manual. If you would like to explore it by yourself. Download a demo from the demo pages in the manual, or buy a license at our store. It’s not that expensive.