Found Set Awareness Revisited

Recently a colleague contacted me with some questions about making ExecuteSQL found-set aware, and I realized that while I had addressed aspects of the problem in various blog postings over a multiyear period (for example, here and here), I had not assembled my thoughts into one cohesive, up-to-date article.

Well, that shortcoming has now been addressed, and today we’re going to look at three demo files, all of which rely on the summary list field type (introduced in FM 13).

9-20-2015 8-52-01 AM Continue reading

FileMaker UI Tricks: Dual-Purpose Layout

Editor’s note: Today it’s my honor and privilege to present an article by guest author Beverly Voth, whose contributions to the FileMaker community are numerous and much appreciated. It’s great to have her back here on FileMaker Hacks.

FileMaker 13 & 14 have some features that can be leveraged to allow one layout to toggle between Form View & Table View and look differently! We will be using the Starter Solution, “Contacts”. Feel free to download the demo file ( or you may start with a fresh copy of the database file or another Starter Solution and follow the steps below.

NEW to Starter Solutions? Select “New From Starter Solution” under the File menu. Click the “Contacts” icon and click on the “Choose” button. A dialog will come up with the “Save As” field filled in with the name of the database file you chose. Navigate to where you wish to save the file and click the “Save” button. This will open the file to the default layout and be ready to change.

The default layout for Contacts is “Contact Details”, is in Form View and shows two “tab-like” buttons called: Contact List & Contact Details. These are buttons that Switch you to different layouts. Notice how the placement is the same and there is a smooth transition between the two layouts. You may also notice that the view is “locked” to just allow View as List or View as Form (respectively). You cannot select other views on these two layouts.



Continue reading

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

De-Duplicate Within A Found Set Of Records

15 May 2015: Demo and article have been updated to make the technique 100% portable.

This is a quick follow up to the Ralph Learmont technique I posted the other day, in response to a comment seeking a generic technique to de-duplicate from within a found set.

Clearly there are various ways to skin this particular cat, and I have a suspicion my initial attempt can be further improved upon, but using Ralph’s demo as a starting point here’s what I’ve come up with:

5-11-2015 10-47-47 AM

Bear in mind that the goal here is for the script to be 100% portable, i.e., you should be able to copy and paste this script into your solution and run it without any modification at all.

Continue reading

Successfully Find Duplicate Values Within A Set Of Records

The need to isolate duplicates within an existing found set has often been a source of frustration for developers. A couple weeks ago we examined a technique by Ralph Learmont demonstrating that, contrary to popular belief and experience, the ! (find duplicates) operator can, under certain conditions, be coerced into isolating duplicate values within a found set.

4-24-2015 3-24-21 PM

Here’s Ralph’s original statement of the problem:

There’s a quirk in the way Filemaker deals with duplicate records. This makes it difficult to find duplicate records WITHIN a found set of records. If you try to constrain the Find to the current set of records, you might discover extra spurious records appearing. These records have “partner-duplicates” outside of the found set. These unwanted spurious records will appear as single occurrences when you inspect a sorted column. Technically they are duplicates. It’s just that their partners lie “outside”, hidden in the omitted slab of records.

And why it’s a cause for concern:

I think it’s important to explain why finding duplicates in a found set is something that often appears to work… and it won’t be apparent there actually IS AN ISSUE unless one goes to the trouble of actually sorting results and checking for those “single-occurrences”.

While the technique was blazingly fast, it seemed to me there was something a bit voodoo-like about Ralph’s previous demo, as well as my attempt at simplification. Apparently Ralph wasn’t satisfied with it either, because he has provided a new demo (Find Duplicates in Found Set) showcasing a more straight-forward technique, and kindly agreed to share some of his thoughts about it as well. Continue reading

FM 13: Anti-deduping, part 3

Today we have two great examples of using demos from this site as a starting point and making significant improvements: one from Malcolm Fitzgerald in response to FM 13: Anti-deduping, part 1, and one from Ralph Learmont in response to FM 13: Anti-deduping, part 2.

As you may recall the challenge was to retain duplicates and omit unique entries from within an existing found set, as opposed to starting from all records… otherwise we could have just searched on ! (find all duplicates), but since the ! operator does not play nicely with constrain, it was apparently not an option for this particular challenge.

Or so I thought. These gentlemen convincingly prove otherwise — and like all great techniques, what you’re about to see has the potential to be useful in a variety of situations, not just the narrow confines of this particular challenge.

Malcolm Fitzgerald – Example #1

4-25-2015 5-06-32 PM

Continue reading

FM 13: Pseudo Indexing

If you’ve spent much time working with FileMaker Pro, you are probably familiar with the ability to view a field’s index.

To view a field index, put your cursor inside any indexed field and press Cmd-i (on the Mac) or Ctrl-i (on the PC) to see a list of unique (non-duplicated) entries for that field.

It’s a great feature, but it has some limitations:

  1. Field indexes are not found set aware — you get unique entries for all records
  2. The field must be on the layout (and enterable)
  3. The field must be indexed
  4. When viewing a field index, it’s possible to accidentally insert data into the underlying field
  5. You can’t copy what you see — it’s view only
  6. If you’re doing a screen share, and zoom the database up to a higher magnification, the index window does not correspondingly zoom

Well today we’re going to look at a technique to circumvent these limitations, and I invite you to follow along in today’s demo file, FM 13 Pseudo Indexing, if you are so inclined. Continue reading

Interview with Ray Cologon

Today it’s my distinct pleasure to present an interview with Dr. Ray Cologon, whose contributions to the FileMaker community over the last decade and more have been invaluable, and whose DevCon sessions are always packed. To add your name to a priority list for future or alternate Master Classes, complete the online expression of interest form at the NightWing Enterprises site.

You’re currently offering FileMaker Master Classes in a number of locations, including two within the US. How did that come about?

The classes being offered presently are in a sense a sequel to two classes that were offered in 2014 in Berlin and London. The original impetus came from Egbert Friedrich of FileMaker Mentoring in Berlin, who invited me to consider presenting developer-oriented material for a Berlin class. It really grew out of that.

I was interested in the idea because my own experience has been that there has been very little available to take people beyond the basics and the levels of ability required (for example) for certification. There are a few DevCon sessions each year that are listed as advanced, and there are some online resources (of which filemakerhacks is one, in fact) that delve into deeper issues. But there has been no coherent framework to address the concepts and challenges that advanced developers face.

What I think essentially sets these events apart is that the content is broad-ranging, and the topics are inter-related. It’s not taking a specific area such as user interaction or interface design in isolation. Those things are definitely under discussion, but as part of a much wider agenda.

So the Master Classes are intended for advanced developers. What does that mean, and who would you consider to be at an appropriate level to take the class?

Yes – this is three days straight of advanced content, with a pretty densely packed list of topics. It really assumes people don’t just know all the basics already, but that they’ve built and deployed some sizeable systems and have a sense of the complexities and challenges that go with that. Continue reading