Category Archives: ExecuteSQL

Custom Field-Based Value Lists



Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them? 


The “problem”: field-based value lists sort alphanumerically

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”


From demo #4: field-based value lists mirroring the order of their underlying source fields

(Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.)

Continue reading

Virtual List Reporting + JSON

Today we’re going to look at an alternative approach to the multi-window VLR technique we examined last month in Virtual List Reporting part 3, utilizing and extending techniques introduced in last month’s JSON + Virtual List. To avoid needless repetition, today’s article will assume the reader is familiar with that material.


Demo files: vlr-plus-json-v1 and vlr-plus-json-v2

As you may recall from VLR Part 3, we have a system that allows us to spawn as many separate reports as we wish (each in its own browse mode window) and sort each report independently via clickable column headings. Continue reading

User-Friendly Excel Exports, part 7

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading

FM/SQL Portal Filter + Dynamic Sort

Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus.

Demo file: (75 Mb compressed, 230 Mb uncompressed)

browseaksortcitycomp Continue reading

Fast Summaries Revisited

If you do complex data analysis, then from time to time you probably need to group, summarize, and parse data into variables and/or fields. There are various ways to accomplish this, including the Fast Summary technique, which regular readers of this blog may be familiar with, as it has made a number of appearances here over the years.

Well today we’re going to look at a couple alternatives to Fast Summaries, with the help of some demo files, which you can use to do your own benchmarking.


Or, if you’d rather not do your own benchmarking, scroll down to see results from my tests. Bottom line: each of these three techniques can be fastest, depending on circumstances.

(Also, if you’ve been putting off exploring Perform Script On Server, a.k.a. PSOS, host the demo files on FileMaker Server 13 or later. The reporting routine optionally uses PSOS, so you can dive in painlessly and see what you’ve been missing.) Continue reading

Tips ’n’ Tricks, part 1

Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.

We start off with a couple FM 14-specific tips.

Tip #1: Tool tips in layout mode

In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.


Tip #2: Shift-Return in the Script Workspace

Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.


Before… line 1 is active, and the user is about to press Shift-Return

After... a new top line has been added

After… a new top line has been added, and existing entries have moved down to accommodate

Continue reading

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