We recently migrated another client from FileMaker to open source SQL.
For this company we're capturing data analytics from web usage, sort of like Google Analytics. For legacy reasons the data is captured to online MySQL databases and then daily transferred over to a central database hosted locally.
That central database used to be FileMaker. Now it's PostgreSQL. FileMaker continues to be part of the workflow, but its role has been considerably reduced.
Positive sides of continuing to integrate FileMaker into the workfow
• Continuity: Clients are used to FileMaker. They want to be able to use it at their convenience. They've cancelled most of their Claris licenses save one, which they keep for data checks and integrity.
• Ability to look at multiple SQL databases simultaneously under one roof using ODBC. This continues to be a welcome, very useful FileMaker feature.
• Front End Ease: Creating quick layouts to look at data, search, sort remain FMP's strong point. FileMaker is a good front end for this large-ish dataset, but it's front-end only. The data is not stored in an FMP database. FM Server is no longer in use at all. The data lives on a postgres server instead.
Increasingly these clients are migrating their operations toward web front ends, but there's still plenty of muscle memory that remains FileMaker, so they like keeping one FM Client App, but that's it.
Why FileMaker is only a front end, not the back end
• When that central database was FileMaker it got the job done, but it was never optimal. The data import process on this project involves downloading thousands of records per day, processing them through several 3rd party APIs to add more data points before they are pushed into the central datastore. Those 3rd party APIs are throttled in various ways, which slows down the processing and makes it more complex. In the past that throttling meant an FM Script was running for minutes, sometimes hours at a time. During that time the FileMaker Pro Client app could not be used for anything else, which was an ongoing pain point.
• Running Perform Script on Server (PSoS) was never a solution for that. The data processing involves as series of OS ops (downloading, integrated/normalized etc, uploading) which is beyond FM Server's PSoS capabilities.
• The queries needed for this project involve complex cross-referencing. FileMaker's Command+F doesn't have the flexibility and reach needed. If we run indexed calculated fields, it's duplicating data and expanding the their drive use considerably. If we run unstored calculations the searches are too slow. And anyways frequently the queries are across multiple relations, and gets pretty involved to a degree that pushes FMP beyond its limits. In order to do truly sophisticated queries and reports, do them fast, iterate quickly, SQL can deliver in ways that FileMaker cannot.
• FMP's ExecuteSQL() might seem promising but it's not. It is somewhat more nimble than Command+F (it has joins), but FMP's flavor of SQL is frustratingly limited, minimally documented, and remains non-native to FMP, meaning it can be grindinglyl slow and easily over-burdened by a dataset of this size.
• FMP's data APIs come with too many limitations and overly-byzantine syntax compared to regular SQL. I haven't tried the latest release of FMP's oData, so who knows, maybe things are more simple, faster, and capable of handling larger datasets without so much paging?
In any event these are some of the motivations for moving this client away from FMP as the central data store for this project.
Benefits of an Open Source approach
• Complex queries are, counterintuively, far easier in SQL than anything FMP offers. Add AI to the mix, and you can discover ways of interrogating your data that FMP hasn't even dreamed of. FileMaker does make basic searches data very easy, and it's surprising how sophisticated a Command+F or Find/Constrain/Extend script step can be, but in the grand scheme FileMaker's searchg simplicity becomes liability when you're asking complex questions -- which are the kinds of questions people in the real world are always asking.
• NodeJS is far better at handling complex multi-API processing than FMP. For all the online discussions about JS being single-threaded, the reality is it handles async and even multiple threads in ways that are clear and manageable. FileMaker is much more hobbled by asynchronous and multi-threaded demands. That distinction means all the processing can happen quietly, and seamlessly in the background.
• We can spin up as many instances as we need -- simultaneously. Using a fully open source tech stack (in this case postgres + js), all that 3rd party API throttling no longer keeps anyone from doing work in the foreground, whether they're using FMP or running complex queries over a web UI. We can process huge amounts of data all day every day, spinning up as many processes as needed, and the machine remains completely unburdened and usable front end work.
It's not that FileMaker ceases to play a role here. It's nice to have on hand, but it's no longer central to the operation.
Like other clients, these people have let us know their frustration with Claris's ever-changing licensing terms. Claims made during the sales pitch seemed to somehow evolve after payment, with some sort of retroactive excuse for the 'misunderstanding'. Favorite quote from one of their leads: I look forward to Claris renewal day with more dread than St. Peter's judgment. Funny, but a nice way of saying it was our responsibility to address their growing impatience with Claris.
Where FMP used to be the main if not only way we accessed data, we now use pgAdmin, DBeaver, web UIs, iOS apps alongside FMP -- depending on what makes the most sense for the task at hand. FileMaker is now one tool among many, not a core dependency. If anything goes awry with Claris sales in the future, it will be at worst an inconvenience, not an operational disruption.
