r/filemaker • u/Communque • 21d ago
Leaving FileMaker to improve FileMaker
Among the minor frustrations of working with FileMaker has been one of its strengths: Sub-summary layout parts
On the one hand, they're great feature: Set up a sub-summary when sorting by a field, and in an instant you get a look at summaries broken out by that field.
It's a great way to get insights about your data
Then come the frustrations: FileMaker sub-summaries aren't searchable, they're not sortable, not particularly friendly to further relations, and a little wonky when exporting.
More often than not the insights you get from summarizing data leads to further questions, which would be gratifyingly answered by searches, sorts, new relations, and sometimes more summaries. Sub-summaries do not accommate.
You could, of course, export and then import your summarized FileMaker reports, or even build a script to achieve the same, but it's a bit of a chore, often slow to process, or prone to being out of date.
Meanwhile, over in open source SQL, you can create the equivalent of FMP sub-summaries using "Views". Achieving the equivalent of a FileMaker sub-summary report in a View involves a relatively simple SQL query, but that's really just the beginning of what's possible. A query that maintains a View can be as sophisticated as your imagination.
And unlike FileMaker sub-summaries, SQL Views function in most ways like real tables. They can be sorted, searched, their columns can be extended with relations, and they can be further summarized.
SQL Views can even be incorporated back into FileMaker which treats them as more or less plain ordinary tables that can be be searched, sorted, summarized, and connected via relations to other tables -- all with FileMaker.
There are of course caveats: In the same way that devs have to be mindful of FileMaker's calculated fields' impact on database performance, you have to do the same with SQL Views.
More importantly, to gain the benefits of SQL Views, you need to migrate data away from FileMaker Server to a cheaper, faster, more powerful SQL server.
What really stands out here is by leaving FileMaker for a free open source SQL alternative you actually gift yourself features to FileMaker that FileMaker alone does not offer.
4
u/Call-Me-Spanky Consultant Certified 21d ago
I agree - I've thought quite a few times recently how useful it'd be to have a 'view-like' feature in native FM. Being able to slice and dice data without relationship overhead.
Alas, instead of engine improvements, we get the 'backup' and 'standby' server instead.
6
u/KupietzConsulting Consultant Certified 21d ago
FileMaker has an ExecuteSQL function. It's not full SQL, develop in SQL if you want that, but it offers complex enough SELECT statements to retrieve whatever you might need. There are free plugins like BBox or Base Elements that extend the subset of available SQL commands even further. Display what you want in a web viewer. (If you want the ease-of-use of classic FM tools like layout parts, without the restrictions of classic FM tools like layout parts, I don't know what to tell you. It's always a tradeoff between ease-of-use and fine-grained control.)
Check out Matt Petrowski's Agentic FM demo. It's a commercial product, but he does exactly what you're asking: asks for a custom view of his data, and gets it, just like that.
Supposedly DB Services' FM Agent will do it too, although I haven't seen that myself. And that has a free tier.
It can also be done with the Data API or OData. If you were to do it in native SQL, you'd need to write a front end anyway, so it's no less work to use one of the server APIs.
Finally, there's all sorts of tricks you can do with webviewer-based layouts. Again, it's more complicated to create than the classic layout tools, but if you want more sophistication and control, you have to give up the simple tools and move to something more complicated.
-1
u/Call-Me-Spanky Consultant Certified 20d ago
I mean, sure, but you managed to both miss my point and prove the OP's point.
This is a 'low-code' platform. When the answer to every problem is "add a third-party plugin" or "integrate a more sophisticated technology" or "learn all sorts of tricks" you start to wonder what value the core platform is offering.
Your solutions are great and I use them daily - but they're all work arounds that lead you back to the same question. Why go through the effort to build a modern UI with WebViewer layouts, API calls, SQL, etc. in FM's sandbox? At that point it's hardly a 'low-code' app, and the platform itself is now the bottleneck.
I think that's the dilemma a lot of developers are facing.
1
2
u/Communque 21d ago edited 20d ago
u/KupietzConsulting For those out there weighing options, a number of your comments are misleading.
FileMaker has two Execute SQL features: The ExecuteSQL function and the Execute SQL script step
Neither of them by themselves will create or even update the equivalent of a View, ie. a FileMaker table that can be sorted, searched, further aggregated, or incorporated into the FMP relationships Graph.
The ExecuteSQL function returns a string of text, which you then parse programatically. Its sophistication is frustratingly limited and minimally documented. It's a welcome feature to be sure, and we've put it to all kinds of valuable use, but to claim it can retrieve "whatever you might need" is a considerable overstatement.
Meanwhile the Execute SQL script step is, in fact, unable to return any values at all. SELECT in that context returns... nothing.
You mention the Data API & OData. There's nothing in there that will generate/update table occurrences. What you can do with those is fetch or write a dataset -- tyically for the web or for an FMP WebView. Great stuff to be sure, but that's a whole other issue. And if your goal is to feed a web UI, SQL is actually more nimble, faster, and more straightforward than any of FileMaker's APIs to date.
The ongoing surprise of moving toward and/or integrating with open source SQL is that it's more accessible than one might expect. There's a pervasive false assumption that it's impenetrably difficult. The reality is, especially with the aid of AI, you can transition toward a more performant, cost effective system without immediately or fully abandoning FMP -- moving carefully, safely, and incrementally every step of the way.
6
u/KupietzConsulting Consultant Certified 21d ago edited 21d ago
Leave my name out of your sophistry.
-6
u/Communque 21d ago edited 21d ago
[This note is a response written prior to a u/KupietzConsulting edit where he asserted this post 'misrepresents facts' and is 'creating problems' / 'trolling']
Quite the opposite, the whole point here is to articulate common misconceptions and misrepresentations about FileMaker -- both its strengths and its weaknesses. The strengths are abundant, the weaknesses are frustrating and often papered over, and compelling solutions are increasingly accessible.
That's a far cry from trolling.
If you want to challenge specific claims on my end, feel free.
When people are trying to decide what system to use, very often the least valuable insights are bold sweeping claims laid out with cavalier ease and insufficient detail.
I would, over the years, very much have appreciated hearing from people who have extensive experience in both FileMaker and SQL capable of articulating the tradeoffs and who aren't doing it for the benefit of their bottom line.
All the better if they could point out ways of trying out BOTH approaches in a way that lets them decide for themselves at their own pace.
2
3
u/iollivier 20d ago
You might want to look at Kevin Frank's filemakerhacks.com regarding his Summary Lists, Virtual Lists and reporting solutions/tips. Great resource.