FileMaker and DuckDB – what’s special about this liaison?

15. Juni 2024

FileMaker + DuckDB

After discovering DuckDB by chance and giving it a try it was time to get a real project done.
I did some experiments and found a way to integrate DuckDB with FileMaker, what was the topic of my presentation for the dotfmp conference in Berlin two weeks ago. After the resonance was quite exciting and feedback by other FileMaker developers was encouraging, I gave it a try to adapt an existing solution to the possibilities of DuckDB.

Get ready for a deep dive

In my first experiments with DuckDB I just touched the surface. Now it was time for a real deep dive…
A few months ago I implemented a project with FileMaker to solve a strategic question in a business case that was unanswered yet.

It was a really complex scenario for analyzing a huge dataset. The approach includes

  • unpacking nested groups of labels
  • normalizing multiline keys
  • aggregating on missing relational data
  • generating data on if-then-else logic
  • calculation of statistical weights upon aggregated results of the whole dataset
  • normalizing the weights upon subsets of the data in several dimensions
  • and finally extrapolation of the whole dataset by a cross product of labels, weights and several measures

The original analysis I created in FileMaker required a rather complicated logic that included

  • generating meta data by scripts
  • multiple loops upon all dimensions and millions of records
  • calculation of intermediate results
  • writing back status flags to the data for being able to filter aggregations

When it finally worked, it took more than 10 hours of processing time. The final results then had to be manually transferred into a huge Excel document to display multiple pivot tables with the results in different dimensions.
The impact of the results were really worth it. But now the decision makers wanted to have this report on a regular basis.

This was an ideal candidate to find out if DuckDB could fulfill the promises I had read about. It took me some days to understand the logic of the approach to this task and to discover more and more of the rich toolset DuckDB offers. Finally the new project got traction and I learned some really neat tricks that were only possible by the powerful and flexible SQL language that is unique to DuckDB and extends the classic SQL by some awesome features like logic for structures, conditionals, nesting, pivoting, regular expressions, string functions, date functions and much more.

Unleashing the power

To make a long story short: Not only was it possible to rework the entire project within a short period of time, but the end result was far more advanced than I could ever have dreamed of.

FileMaker DuckDB
12 Tables, 25 TOs
86 Helper Fields
10 Custom Functions
56 Scripts
1524 Script-Steps
1 Query
27 SELECT Statements

Using DuckDB, it was possible to turn all the data processing into one giant SQL statement consisting of a series of SELECT commands to import all the existing data from CSV files, generate the calculated data and output the results in exactly the form that was needed. Thanks to its in-memory capabilities, the processing time has been reduced from 10 hours to less than 4 seconds(!!).
Really amazing!

FileMaker DuckDB
10 hours 4 seconds

With this power under the hood, I finally went one step further and had DuckDB generate a new dataset consisting of a cross product of the original data with the sliced subdivisions of labels and weights that were calculated as a temp table during the aggregation process. This new dataset consists of about 30 million rows of data, and it only took less than a second to create it, and less than 3 seconds to write 2.8 gigabytes of data to a new CSV file. That said, I did not use a super computer or some scaled out cloud service, but all this happened on my local machine, a MacBook Pro with an M1 CPU.
Awesome!

Go one better…

Now, with the new result set, there is a foundation to build another project on top of this processing pipeline to bring further enhancements to our existing reporting. I had built an OLAP tool in our FileMaker solution, to make sense of transactional data by being able to filter and aggregate by more than 20 dimensions. But since we collected about 25 years of transactions, which means tens of millions of FileMaker records, the processing time in FileMaker got slower and slower over the years. To compensate we migrated the server hardware every few years to the fastest machines that were available at that time.

A typical report needs some minutes to select a subset of the data by find-request and then doing the calculation of the results with summary fields and sorted subsummary-layouts. Which is indeed a nice and flexible way of doing this in FileMaker. But as processing time grew over the years, these powerful features are used less and less by the users who are in need for the results to make business decisions on the data. We ended up creating some scripted reports that are processed overnight once a month and automatically sent to our controlling team as a PDF file.

Now we see some opportunities to advance our OLAP tool for “real time” analytics with the help of DuckDB. It could then produce final results instantly by the push of a button in the FileMaker user interface. That would be a huge advancement in delivering an interactive tool to find answers on strategical questions on the fly.

It comes down to the simple fact, that big CSV files with shadow tables of all data can serve as a database by its own, as DuckDB is able to process the files in blazing speed. It would be nice to bring the integration of DuckDB and FileMaker some steps further for more elegant interactions but the discovered possibilities by REST API are fair enough to unfold the magic of analytical power and build new ways of interaction with the business data that already exists inside the FileMaker solution.

Conclusion

FileMaker is a great tool that enables us to build critical business solutions by storing and managing data. This is why we often call it a “Database”. In fact, we know it is much more than that, but it has its roots in this category of database software. If you think of DuckDB as just another database, you will miss the point. Yes, it’s true that DuckDB also is in the category of database software, but it has a whole different approach on dealing with data.

While FileMaker is optimized for reading and writing rows of data. DuckDB is optimized for processing and aggregating columns of data, resulting in its unbelievable speed. While FileMaker is optimized for transactions and access by multiple users on a server, DuckDB is optimized for single processes on a single machine, without any features for record locking or network access from multiple users or devices. It just reads, transforms and writes data in very flexible and powerful ways. So each of this two databases has its very own feature set that are ideal in combination because they complement each other nicely.

FileMaker and DuckDB is a winning team! There are many more use cases to be discovered, the journey has only just begun.


Further reading:

Integrating DuckDB

08. Juni 2024

Integrating DuckDB with Node.js as a REST API for FileMaker

…with Node.js as a REST API for FileMaker

Please find attached Slides and Demo-Files from my presentation at dot.fmp conference in Berlin.

DuckDB is a rising newcomer on the block. It has a small footprint, is blazingly fast and runs on all platforms. With its extended SQL commands, it goes beyond other database engines and offers an interesting feature set. Optimized for storage in columns, it offers a comprehensive toolset for fast data analysis. With its ability to read data from multiple sources – even directly from cloud storage – it can be used as an ETL tool.

We have a quick look at DuckDB and examine how to integrate it’s power into FileMaker solutions. As an additional benefit you learn how to use node.js to create a simple REST API that is instantly accessible with just a few lines of FileMaker script.

The presentation slides are available for download here:
dotfmp Presentation – Integrating DuckDB (PDF 2,1 MB)

Demo files (FileMaker and node.js) are available for download here:
DuckDB_Demofiles.zip (PDF 29,8 MB)

Further reading:
FileMaker and DuckDB – what’s special about this liaison?

Claris Platform Update – The Bottom Line

14. Oktober 2023

FMDiSC Claris Platform Update

The latest news update from Claris has made some noise. Now that the dust has settled a little, it is time to look at the various aspects of a major transition that has now been going on for more than 4 years.

Let’s take a look back at the major adjustments in the Claris strategy and why they were necessary. We provide answers to the latest developments and put them in a broader perspective.

How Claris has managed to clarify its strategy for the entire platform. We examine which issues have been resolved and where action is still needed.

At the invitation of the colleagues from FMDiSC, I was able to give another presentation, picking up where last year’s presentation ended.

Claris Platform Update – The Bottom Line
FMDiSC October Meeting 2023
https://youtu.be/tc2f2MNylcQ?t=5466

The Future of FileMaker – Position determination
FMDiSC October Meeting 2022
https://youtu.be/2FQ1iE1aDAI?t=137

Claris Product Strategy Update 2023

30. August 2023

Claris platfrom update

There has been some speculation about the future of FileMaker over the past 2 years. In the meantime, after numerous announcements on the part of Claris, there were more and more concrete results that gradually became visible. Some things looked promising, in other places there was still a lack of clarity, and some things were less than optimal in implementation and especially in communication.

What was positive, however, was that Claris, after initial hesitation, actively went into dialogue with partners and developers. Gradually, results were presented and feedback was obtained again and again. In the meantime, one can definitely say that Claris has listened and taken the suggestions and concerns of its long-term partners seriously!
Of course, it is not always easy, especially with such a reorientation, to get all the right goals under one hat at the first attempt. It is all the more important that during a major transformation there is room for corrections and a re-sharpening of the strategy.

Now Claris has made some changes: Many suggestions for improvement have been taken into account and the bottom line is that the waiting time and the intensive exchange with the community have been worth it. Although not all goals have been achieved in the implementation yet, it is now clearer than before that the direction is right! Moreover, Claris has already delivered results in some places. The most recently released versions make a solid impression.

And most important: while the new products “Studio” and “Connect” are given a meaningful place in the platform strategy, existing solutions can benefit from the innovations without the need for an elaborate technical migration. The best of the new and old worlds simply grow together seamlessly.

In addition, Claris now gives all existing customers and developers free access to the new tools so that they can prove themselves in practical tests. We can “kick the tires” without any additional licensing hurdles or technical barriers. So things can finally move forward!

Refer to Claris latest announcements from August 2023:
Blog: Claris Product Roadmap
YouTube: Claris Product Strategy Update 2023


Let’s look back at the most important points from last year’s presentation “The Future of FileMaker”. There, we had summarised some issues and wishes from the customers’ and developers’ point of view. We now look at where Claris provides us with answers with the current announcements.

To put it briefly: a lot has been taken into account. Thank you Claris!

Refer to PDF for download (12.6 MB):
The Future of FileMaker – Review and Update 2023.

Blog: The Future of FileMaker – FMDiSC Meeting Oct 2022
Recording of the presentation on YouTube: The Future of FileMaker

FileMaker Magazin: Eine Einordnung – Was sich bei der Claris Produkt-Strategie ändert (in German language)

FM JSON Path

21. August 2023

FM JSON Path

Sometimes it would be useful to have a flexible way to access the elements within a JSON object. Something like XPATH for XML objects.

FileMaker itself offers only very limited JSON functions. With JSONGetElement it is possible to query a known path within the JSON object, but no wildcards or even search filters are possible.

This is exactly what the library “JSONPath” offers. It enables an XPATH-like query syntax for JSON objects. A detailed description with examples can be found on Stefan Gössner’s website:
https://goessner.net/articles/JsonPath/



This can be used to realise the following queries, for example:

  • $.store.book[*].author -> the authors of all books in the store
  • $..book[-1:] -> the last book in order
  • $..book[?(@.isbn)] -> filter all books with isbn number
  • $..book[?(@.price<10)] -> filter all books cheaper than 10

See more examples and full sample JSON on the project website.



There is a JavaScript implementation of this library which can be downloaded from the Google Code Archive:
https://code.google.com/archive/p/jsonpath/downloads

With the help of the JavaScript functions of the MBS plugin, the JSON Path library can be easily integrated into FileMaker and then called via script command:
https://www.mbsplugins.eu/component_JavaScript.shtml

Note the difference in the functions “JS.CallFunction” and “JS.CallFunctionValues”. While the first function expects the input of parameters in JSON format, the second function works with parameters in text form. If this difference is not observed, the values arrive incorrectly in the JavaScript function and cannot be processed. Instead, one receives an error of the type “invalid json (at offset 1)”.

Once these hurdles have been overcome, the content of a JSON object from FileMaker can be queried with a simple script via MBS using a JSON path expression. You get the result in text form or you can simply convert it back into JSON.



Attached is a FileMaker file with the examples from the JSON Path website.
The JavaScript library is already included in the FileMaker demo file.
The MBS Plugin version 11.3 or newer is required.

Download: FM JSON Path.fmp12 (319 KB)



Update 2023-10-02:
JSON Path has been implemented in the latest version (13.5) of MBS plugin. Please refer to MBS documentation and corresponding blog post:
MBS JSON.Query
JSON Query in FileMaker

Claris in dialogue

22. Dezember 2022

Claris Dialogue

There is something going on in the communication of Claris. In the meantime, new version updates for FileMaker have been shown and further innovations have been announced. While the past months were characterized by hopes and fears, confidence is now returning with regard to the future of the Claris platform.

Highlights of the past weeks were:

  • The Claris Product Team Webinar at the beginning of November
  • The Claris sessions at the engageU conference in Malmö
  • The Claris Partner exclusive webinar in early December
  • The release of FileMaker version 19.6
  • New announcements about Claris Connect (Custom Connectors!)
  • The announcement of the freemium version for the new Claris platform
  • The technical tutorials in the “Claris Engineering Blog” (refer link)
  • The blog post by Robert Holsey in the Claris Forum (refer link)
  • Q&A with Andrew LeCates at FMDiSC December Meeting (refer link)
  • New in 2023: Claris Update Webinar – Jan 25 (refer link)

The message from Claris is now clear: “We’re on it! We are building the future! Be patient!”. It was also reiterated once again that FileMaker will continue to evolve as a product and nothing will be broken. On the contrary, all improvements should benefit the entire platform. And wherever there are still gaps in “Claris Pro” in terms of functionality or technical features compared to FileMaker, which stand in the way of a migration, these should be fixed as soon as possible.

OK, Claris – we heard you!
Thank you for your efforts!

So we can be curious about the novelties in the upcoming year…




Links:

Images: Claris, @JorisAarts, @dotfmp

FMDiSC Claris Platform Survey

18. Oktober 2022

FMDiSC Claris Platform Survey
The FileMaker Developers in Southern California (FMDiSC) are bringing us a brilliant occasion to take place in a global Survey about the Claris Platform.

This could be Our response to Claris to give feedback about their latest announcements and precise our perception about the new platform strategy and express what we as developers would expect from them to grow the platform further.

The survey is very well thought out and designed to cover all critical aspects.
Thanks to FMDiSC and especially David Knight from Angel City Data for putting in a lot of work during the preparation.

You can get some background about the idea of the survey in his presentation during the latest FMDiSC Meetup on Youtube:
https://youtu.be/284ZEhjM7GQ?t=4262

So please take some minutes to fill in your answers to the survey.
Don’t miss this chance to raise our voice as a global FileMaker community.

Just follow this link to start:
https://www.surveymonkey.com/r/FS2MN6N

The Future of FileMaker – FMDiSC Meeting Oct 2022

18. Oktober 2022

FMDiSC The Future of FileMaker

On invitation of the American developer colleagues of FMDiSC (FileMaker Developers in Southern California) the opportunity arose to present the lecture “The Future of FileMaker” once again.

The presentation covers the development of the FileMaker platform over the past 30 years, classifies the respective stages with regard to the general requirements in the IT world and breaks down how the new Claris platform currently presents itself technically in detail. It also provides answers to open questions regarding the many new marketing buzzwords and the basic concept behind the new platform strategy.

It also looks at our situation as FileMaker developers with an assessment of what challenges we are facing in the coming years.

The presentation was already part of the German-speaking FileMaker conference in Hamburg in June this year. After Claris officially announced the new products in September and also further technical details of the new Claris platform are available in the meantime, the talk could be updated in some points. In addition, I have added a section to evaluate Claris’ announcements from a developer’s point of view and explain my assessment in this regard.

Last but not least, there is a preview of a wishful future with a few ideas with which the potential of the Claris platform could be further exploited.

Thanks to the colleagues from FMDiSC for the opportunity to shed some more light on the topic!

The new version of the talk is in English, the recording is available on Youtube at the following link:
https://youtu.be/2FQ1iE1aDAI

+++

Following the talk, there was an FMDiSC discussion panel on the new Claris platform, with further assessments of the current situation and an outlook on what we can expect in the coming years.

Chris Moyer, Richard Carlton, Todd Geist, Beverly Voth and Vince Menanno shared their views and brought further momentum to the discussion.

The second part of the meeting is also available as a recording on Youtube:
https://youtu.be/284ZEhjM7GQ

+++

The meeting was completed by the presentation of a survey by David Knight. This is aimed at us developers and is basically structured in such a way that all essential and also critical points regarding the future of the platform are queried in order to get an overall picture of our assessment as developers. All in all, this makes a very well thought-out and positive impression! So to speak, a constructively built feedback from us developers to Claris.

The explanations of the survey can also be seen in the second part of the Youtube video, starting at minute 71 – just follow this link:
https://youtu.be/284ZEhjM7GQ?t=4262

The link to the survey will be published soon and an active participation of all FileMaker developers is explicitly desired!

Rückblick FMK 2022

26. Juni 2022

FMK 2022 FileMaker Konferenz in Hamburg

Nach zwei Jahren Pandemie-bedingter Pause konnte endlich wieder das alljährliche Treffen der deutschsprachigen FileMaker Community als “echte” Konferenz statt finden.

135 Teilnehmer aus Deutschland, Österreich und der Schweiz nutzten die Möglichkeit, um sich nach den zahlreichen Online-Events nun wieder einmal in größerer Runde in Präsenz zu treffen.

Die FileMaker Konferenz FMK2022, welche diesmal erneut in der Hamburger Hafencity ausgerichtet wurde, bot ein buntes Programm an Vorträgen, Diskussionen, Workshops und vor allem reichlich Möglichkeiten für den persönlichen Austausch.

Eine neue Struktur mit kürzeren Sessions, etwas mehr Pausen und gemeinsamen Auftakt- und Abschluss-Veranstaltungen an jedem einzelnen Tag lockerte die 4 Konferenztage deutlich auf und bot damit auch ausreichend Möglichkeiten, um außerhalb der Tagungsräume etwas sommerliche Atmosphäre in der nahen Umgebung zu schnuppern.

Zum Abschluss des offiziellen Teils schalteten sich am Freitag Nachmittag die leitenden Claris-Entwickler und Community-Manager per Video-Konferenz dazu, um technische Details der neuen FileMaker-Version zu erläutern und Fragen zu beantworten.

Danke für die vielen Vorträge und Gespräche. Und vor allem großen Dank an das Konferenz-Team, welches uns dieses Event möglich gemacht hat!

Das vollständige Konferenzprogramm sowie Vortragsfolien zum Download sind auf der Veranstaltungs-Website zu finden:
https://filemaker-konferenz.com

#Claris #FileMaker #FMK2022

Presentation: The future of FileMaker

24. Juni 2022

FileMaker Conference Presentation - The future of FileMaker

FileMaker has grown a special niche among database development systems for three decades. There is hardly a comparable product that has continued to develop over such a long period of time and is still relevant for current application scenarios.

A dedicated community of developers has remained loyal to the product for what feels like an eternity and has matured along with the system and its possibilities.

In the meantime, the manufacturer Claris has set a new course and is trying to readjust in order to catch up with new technical possibilities and in addition to bring new users and developers on board also.

New tools and new target groups are to pave the way for the future in the global change of rapidly developing technology platforms and new competitors.

Will Claris succeed in this balancing act? What are the specific challenges?
What consequences will the new strategy possibly have for us as developers?

We will start with a short overview and then switch to an open dialog about the opportunities and risks of the upcoming changes. Join the discussion and let’s capture the current mood on the future of FileMaker together.

FMK FileMaker Conference 2022
Wednesday, June, 22nd at 4:00 P.M.

https://filemaker-konferenz.com/konferenzplan/
#FMK2022 #Claris #FileMaker

The presentation slides are available for download here:
FMK2022 Presentation – The future of FileMaker (PDF 5,6 MB)

A german version is available here:
FMK2022 Vortrag – Die Zukunft von FileMaker