With FileMaker 16, Claris introduced the FileMaker Data API into the FileMaker development environment, giving us a powerful new tool for pushing and pulling data in and out of the FileMaker database engine. 

As a standards based REST API, the FileMaker Data API can be utilized as the primary means of interacting with the FileMaker database engine from widely used web application development tools like Javascript, Git, and Google Cloud.

A very natural use case for the FileMaker Data API came up a little over a year ago when a new client, RLO Training, came to us with an old FileMaker solution. The old solution was based in FileMaker 7 and its job was to accept a data import of financial data and output a complex 20 page report summarizing the financial data across time and allowing comparison between approximately 20 organizations.

The goal was to eliminate the printed report and replace it with an interactive web application that would allow users to input their monthly financial data and then directly interact with it online to replace the functionality previously provided by the old printed report. With a web app, the data would be accessible in real time and with the promise of future enhancements only possible with an online web tool.

We had a bit of a rocky start getting the application written, as the old FileMaker 7 tool was so dated that no one knew why some of the calculations were written the way they were and time had to be spent on all sides gaining that understanding. The departure of a key stakeholder at our client in the middle of development also caused significant issues. Perseverance pays off though, and the web application has been in successful production use for some time now. 

The FileMaker Data API is based on layouts and access to the schema is based on the layout you are referencing when your API method is called. It is important in a complex application to think carefully about the types of queries you will need to do from your web application in order to gain maximum performance. It has been our experience that while the FM Data API is a powerful tool, it does hit performance limitations as the number of concurrent users and queries goes up.

As we were completing the data entry portion of the application and moving into the reporting side, we found that the need to do 3 to 5 queries to build the report led to performance issues when the number of concurrent users started to grow. We considered several alternative solutions for this and, after testing, narrowed the options down to two. The first was to refactor the database a bit to flatten out the schema and reduce the number of queries and the second was to cache the data needed for the reporting side in a set of mirrored MySQL tables. 

We ended up going with the latter solution and the resulting performance has been excellent. The completed solution uses FileMaker Pro for the back end client support and management functions, including user management and monitoring. The web application writes customer entered data directly to the FileMaker database engine via the FileMaker Data API. A server side script in FileMaker pushes a subset of data into MySQL for the web application to access for the higher performance required of the financial reporting.

We used node.js for our backend with Angular framework for the front end on this project. Our source code control is done using Github. We’re using Kubernetes at Google Cloud for hosting the web application and an Amazon EC2 VM for hosting the FileMaker Server.

~John Newhoff