As we have been advancing our Web Application footprint at Portage Bay, we have been finding ourselves with a bottleneck at the FileMaker Data API interaction level. This bottleneck is causing latency between calls and doesn’t scale well, especially when several users are interacting with a data intensive site at the same time. We needed a solution, and the requirements were straight forward. We need to serve the data more quickly, allow for scaling to multiple concurrent calls, and provide less latency then the FileMaker Data API. Basically we needed a way to stage our FileMaker data in a cache that could serve the data really fast. 

After considering relational vs non-relational databases, and trying a few caching services such as Redis, we decided to go with MySQL and use a relational database. While Redis or some other caching service would have been the easiest to implement, we couldn’t use Redis cache for this project as the data changed too frequently and had multiple query parameters. Neither of which reap the benefits of standard caches. Favored by our existing familiarity, the decision for MySQL was an easy one. It is widely supported in most programming languages, works great for Web Apps, and we already have several tools to work with MySQL data tables.

One of the tools we like to use for testing SQL syntax, is RazorSQL, it makes it easy to interact with your MySQL data and generate query structure on the fly, allowing you to easily build tables and column structure. On the FileMaker side, FileMaker can connect to MySQL via an ODBC connection. Since we are hosting the FileMaker solution on one of our servers, we needed to add the ODBC driver to the server that it was being hosted on in order for FileMaker to connect to the MySQL source using External SQL Data Source or ESS for short.

In this instance we setup the MySQL server hosted in a google kubernetes cluster. We added the database for this project, and all that was needed now was to mimic FIleMaker and add the tables with all of the fields to our MySQL database. In MySQL they are referred to as Tables and Columns. This is where we found the next challenge. Creating the 10-20  tables in MySQL with hundreds of fields (columns) per table is no easy task. The field names also needed to match what had already been created in FileMaker, that way we could create a schema for either FileMaker Data API integration or MySQL query integration by the front end portion of the Web App.

This is where FileMaker came in handy and was helpful in solving the problem by generating the syntax needed for table creation. We have been able to write a script that can get all of the fields on a layout, the same layout you would use for the FileMaker Data API calls. We can iterate through that list of fields and quickly create the commands in MySQL syntax to generate a new table with columns (fields) that match. There are of course caveats. Before beginning we needed to make sure that both the field names, as well as the layout names to be used are SQL safe, otherwise they would need to be handled with special care. We chose to rework them to be safe rather than add any additional logic to the syntax creation. We needed to ensure each table has a primary key, as that is needed by FileMaker’s ESS to reference the record ( SQL Row ) properly. This will be set after we create the table in MySQL. Container fields won’t transfer and should be omitted.  Lastly, the layouts need to only reference the layouts’ table fields. ( otherwise you need to get fancy and do more complex stuff. While we did need to do that, it was a bit involved and I won’t bother you in this blog with those details. Maybe we will review it in another blog article. )

The main function in the FileMaker script is the “FieldNames(fileName;layoutName)” function, it provides a list of field names. We write it like this, “FieldNames ( Get ( FileName ) ; $layout )”. In this instance, we pass in the layout name that we want to generate the table from. After that we iterate through the list and create the syntax, with the last part being the type of field, so that we can properly assign it a relevant type in the MySQL Column.

We have included a demo file with the script. Feel free to download it and give it a look. This saved us a ton of time and was very helpful for this project. The only drawback is that there wasn’t an easy way to copy the SQL syntax from a script variable and put it into the clipboard. Once we have it in the clipboard, then we could paste it into RazorSQL to actually generate the tables. In order to copy it to the clipboard, we would have had to add a global field in our FileMaker solution to store it before it could be copied to the clipboard. In this instance we just copied it out of the Tools/Data Viewer.

Please send us an email with any comments or suggestions!

-Xandon Frogget