Category: Virtual List

ExecuteSQL, JSON, Level: Any, SQL, Virtual List

October 2025 Full Access Resource Page

Kevin Frank

Here are links to articles and resources related to presentations/discussions I will be participating in at the [Full Access] un-conference Oct 20-23, 2025.

JSON Demo Files

JSON Articles

JSONQuery

Virtual List Demo Files

Virtual List Articles

SQL Articles

JSON, Level: Advanced, Version: FM 19.6 or later, Virtual List

Virtual List Even More Simplified

Kevin Frank

Demo file: virtual-list-even-more-simplified.zip  [expects, but does not require, FM 22 or later]

Note 1: The example in today’s article/demo is intentionally very basic.
Note 2: The demo is self-populating to keep the data current, so the values you see in the screen shots may not exactly match those you encounter in the demo.

As long time readers may recall, between 2011 and 2019 this blog featured numerous articles touching on various aspects of the virtual list technique — or, more properly, series of techniques. The following year, in 2020, I decided to distill the core bits into a basic “beginner’s guide” with accompanying demo file, and I called it Virtual List Simplified. And if you’re still using FM 21 or earlier, and are looking for an introduction to virtual list, I highly recommend that article.

However, with the release a few months ago of FM 22 (a.k.a. FileMaker 2025), the landscape dramatically changed thanks to a re-written JSON engine that is significantly more powerful (i.e., faster) than before. Why should that matter? Because JSON is typically where virtual list gets its data from, and thanks to this awesome performance boost, it’s time to recycle and update the 2020 article and accompanying demo file to take advantage of it. And good news: it’s now less complicated and easier to understand.

At the risk of stating the obvious, there are many, many ways to skin the virtual list cat, and the purpose of today’s article is not to say “this is the best way”, or imply that other approaches are flawed, but simply to propose one particular approach you might take — especially if you are either: a) new to virtual list, or b) already using virtual list, but aren’t completely happy with your current implementation.

At any rate, my aim today is to gather useful insights from earlier articles into a single document (with a couple new ideas thrown in), and some of what follows has been recycled from those earlier articles.

Spoiler alert: two-dimensional JSON arrays are now viable and performant as back-ends for virtual list. Prior to FM 22 this was not the case… things would appear to be fine when the 2D array was relatively small, but would slow down dramatically with larger 2D arrays. For details see Virtual List with a 2D JSON Array in “FileMaker Pro 22: Initial Impressions”.

Continue reading “Virtual List Even More Simplified” →

ExecuteSQL, Level: Any, Virtual List

October 2025 FM Training Livestream

Kevin Frank

Here are links to articles and resources related to today’s FM Training Livestream presentation: Best of FileMaker Hacks Tips, Tricks and Traps, part 2

YouTube link

Demo Files

FMH Articles

Other Articles

JSON, Level: Any, Version: FM 22 or later, Virtual List

FileMaker Pro 22: Initial Impressions

Kevin Frank

Demo Files

Minimum version to open the file shown in parentheses.

Introduction

It’s been a couple weeks since the release of FileMaker Pro 22 (a.k.a. 2025), and now that the dust is beginning to settle and I’ve had some time to explore and build some demos, my initial reactions are…

  1. Wow, this is an amazing, game changing release.
  2. Ouch, they just “broke” a bunch of my blog articles — in the sense that behaviors and issues described therein have changed in FM 22.

What this article is not: a general overview. If you’re looking for a general overview Fabrice Nordmann (FileMaker 2025! Explore the New Features) and Wim Decorte (FileMaker 2025 Executive Summary) have written some good ones.

What this article is: a look at some of the features and fixes I’m interested in, including the new GetRecordIDsFromFoundSet (a.k.a. GRIFFS) function, and with particular focus on JSON performance improvements. Also I want to mention a potential problem if you’re on the Windows platform, and we might as well get that out of the way first. Continue reading “FileMaker Pro 22: Initial Impressions” →

JSON, Level: Intermediate, Version: FM 18 or later, Virtual List

JSON Currency Exchange Rates, v3

Kevin Frank

Demo file: json-currencyapi.com.v2.zip

This is a quick follow up to a pair of articles I published earlier this year, and will assume the reader is familiar with that material.

Having first blogged about this back in 2020, I had hoped to be finished with this topic, not because it isn’t interesting (it is) or because getting comfortable working with APIs isn’t fun, educational, and potentially profitable (ditto), but because I didn’t really have much left to say on the subject. Unfortunately, for the third or fourth time now, the API my demo was based on stopped working after the service provider was purchased by another company with the aim of monetizing the formerly-free service. Continue reading “JSON Currency Exchange Rates, v3” →

JSON, Level: Intermediate, Version: FM 18 or later, Virtual List

JSON Currency Exchange Rates revisited

Kevin Frank

Update 30 September 2023: Free end point no longer available for this service. See JSON Currency Exchange Rates, v3 for a revised, functioning demo.

Demo file: json-rates-via-api-exchangerate-host.zip

Today we’re going to take a fresh look at pulling currency exchange rates into FileMaker, and this article is directly based on its predecessor. Why the re-visitation? Two reasons actually:

  1. The “free” endpoints I relied on back in 2020 (and in 2021 when I revised the original demo) have been monetized, and apart from any cost considerations, I want this demo to work out of the box without requiring an API token.
  2.  The check box set I used for symbol selection last time was designed to accommodate a fixed and relatively small number of entries. That was a short-sighted decision that could not possibly scale gracefully. This time around I’m using an approach that will automatically accommodate any number of symbols.

Continue reading “JSON Currency Exchange Rates revisited” →

Level: Intermediate, Version: FM 19.5 or later, Virtual List

A Summary Field Bug and Workaround

Kevin Frank

Introduction

Recently a client asked me to implement a virtual list reporting framework similar to the one I wrote about a few years ago in Virtual List Simplified. I added the framework to the client’s hosted file, and things went smoothly until I ran a report similar to the one shown below… and observed that the repeating summary field at the bottom wasn’t rendering. The same report worked flawlessly a) offline, and b) when hosted on FMS 19.4 and earlier, but when hosted via FMS 19.5 or 19.6, the summary repeater was malfunctioning.

Continue reading “A Summary Field Bug and Workaround” →

JSON, Level: Intermediate, Version: FM 18 or later, Virtual List

Virtual List Reporting, part 4

Kevin Frank

Introduction

Back in 2017 I wrote about a technique to enable users to a) produce multiple on-screen reports, and b) interact with those reports in browse mode. The article was called Virtual List Reporting, part 3, and while the approach it advocated works well enough under most circumstances, today I’d like to share some fresh ideas.

Note: as you might guess from the title of this article, implementing this technique in your solution, and/or understanding what’s going on under the hood, requires some knowledge of virtual list. If you are not familiar with virtual list, or need a refresher, you may find this article helpful: Virtual List Simplified.

Demo Files (require FM 18 or later)

Continue reading “Virtual List Reporting, part 4” →

JSON, Level: Advanced, Version: FM 18 or later, Virtual List

Virtual Portal, part 2

Kevin Frank

[Note: several hours after posting this article I realized the “Hide Object” calc could be streamlined. Screen shot and demo have been updated to reflect this.]

Demo file: virtual-portal-v2b.zip (requires FM 18 or later)

This is a quick follow up to the Virtual Portal article I posted the other day. As you may recall, the objective was to use virtual list to display disparate entities in a portal…

2019-12-26_154835

…via an array like this…

2020-02-16_18-29-04 Continue reading “Virtual Portal, part 2” →

ExecuteSQL, JSON, Level: Advanced, Version: FM 16 or later, Virtual List

Virtual Portal

Kevin Frank

Demo file: virtual-portal-v1b.zip

This is a follow-up to last month’s article on virtual list, and this time we’re going to explore a way to use virtual list in a portal.

Imagine you have built a system where a Company can be a parent of a Mill, Refinery, Estate, or another Company.

2020-02-18_044932

Each of these entities exists as a separate table in your database…

2020-02-16_18-26-43

…and from the perspective of a Company record, you’d like to be able to see all immediate children. Continue reading “Virtual Portal” →