Category Archives: Version: FM 14 or later

FM 14: Separation Model Data Mining

Note: Interface file #1 requires FM 14 or later; interface file #2 works with FM 12 or later.

What do you get when you combine the Separation Model + FM 14 placeholder text + ExecuteSQL + a million-record table + a variable array + Get(CalculationRepetitionNumber) + the Mod and Ceiling functions + a couple custom functions, with blazingly fast (local, LAN and WAN) performance thrown in for good measure?

This article w/ accompanying demo (, that’s what.

7-7-2015 12-33-33 PM

The Challenge

Provide a data-mining interface to query a million-record table (cc_transactions) containing 20 years’ worth of credit card transactions. The client wants to be able to pick a date via a calendar widget, and see transaction info for that date, summarized by card type, transaction type and region.

7-6-2015 8-54-58 AM

This is a separated solution, with cc_transactions living in a file called z_data.fmp12, and a specific requirement for this project is to not make any schema changes to the data file. The data-mining will take place in a separate interface file… or in this case, two interface files, since we’re going to look at two methods.  Continue reading

FM 14: Separation Aggregation Aggravation revisited

Four years ago I posted the original version of this article, exploring a technique that worked fine in FM 11, but that broke and required extensive work arounds in FM 12 and 13. What you’re about to read is a revision of the original article, showcasing a FM 14 method made possible by a new feature: placeholder text.


FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

7-3-2013 2-57-02 PMToday’s demo file is FM 14 Aggregates and Separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). Here we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three calc fields below the portal are tracking the changes in real time. Continue reading

PDF Thumbnails in FileMaker 14

churchill-3“It is a riddle wrapped in a mystery inside an enigma but perhaps there is a key. The key is how you get the PDF into the container field, and which platform and FM version you use to accomplish this.” 

Winston Churchill, BBC radio address, 1939
(Just kidding about the second sentence.)

If you work with digital images, you’ve probably noticed the trend towards using PDFs as an image file format. I doubt many of use could have foreseen this happening when the PDF format was introduced in 1993, but in many organizations PDF has become the standard file format for images.

The good news is that the GetThumbnail function now works with PDFs in container fields in FileMaker 14, and in this article I’m going to suggest a couple reasons why you might find this useful, and explore some nuances between how PDFs in container fields behave on the Mac vs. on the PC.

pdf thumbnails demo

Today’s demo file, pdf thumbnails, is a 45Mb download, consisting of 32 PDF images (average size 160K, average dimensions 1160×880) as well as six stored PNG thumbnails of varying dimensions for each PDF. Continue reading