1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. ...
  11. 31
Level: Intermediate, Version: FM 12 or later

Magic Numeric Value List

Kevin Frank

Demo Files

Demo #2 is a variation on demo #1, and uses the contents of a $$variable as the basis for the value list.

The Challenge

Recently I had a need to use a dynamically-generated ascending sequence of numbers as the basis for a value list, and in a multi-user-safe manner. By dynamically-generated, I mean that the list of numbers would be calculated at runtime and would not be known in advance. And by multi-user safe, I mean the methodology would need to allow multiple users to simultaneously and independently populate the value list with their own sets of numbers.

First Attempt At Solving

There are a number of ways a developer might go about solving this problem, and my initial plan was to…

a. Populate a global text field with the list of numbers

b. Base the value list on that global field using a technique I picked up from John Ahn at DevCon 2012 in Miami Beach (as described in Magic Value Lists) and which I subsequently explored in detail in Custom Field-Based Value Lists.

The basic idea is to set up a relationship where the “primary” predicate is unstored. In this case I went with a self-join from my Settings table to itself like so…

…though I could have instead used a global field on the left (primary) side… the important things being that the left-hand predicate is not stored and the right hand predicate is stored, and of course that the relationship is valid (in this case we have an equijoin operator and each field = 1).

[Note: a Cartesian join relationship would work here as well.]

Continue reading “Magic Numeric Value List” →

Level: Intermediate, Version: FM 21 or later

Closing Windows During Transactions

Kevin Frank

Update 2 Aug 2025: the issue documented in this article has been resolved in FM 22 (a.k.a. FM 2025).

This article documents behavior in the current shipping version of the product, i.e., FM 21 (a.k.a. FM 2024).

Demo file: transaction_test.zip (requires FM 21 or later)

This is a follow-up to last month’s article on Modal Transactional Card Windows to address a very specific question. Given that transactions are scoped to a window, does a transaction properly revert when you close the window where the transaction was initiated?

At first glance it would appear that the answer is yes, but appearances can be deceiving, and today we have a demo file from Jonathan Jeffrey that can help us understand what is actually going on, and which I am sharing with his kind permission. Continue reading “Closing Windows During Transactions” →

Claris Pro, Level: Intermediate, Version: FM 20.2 or later

Modal Transactional Card Window

Kevin Frank

20 April 2025: Demo and article updated to incorporate a couple tactfully-worded suggestions from Tony White to make the technique more robust. Thank you Tony.

Demo file: transactional-card-window-v3.zip (requires FM 20 or newer)

Resources

Introduction

Recently a colleague and I were discussing ways one might go about having a card window be both modal and transactional. By default card windows are modal to the “background” window (the window they were generated from), but are not modal to other windows in your solution.

In this case we wanted the card window to be modal to the entire solution, so that once it was displayed the user would not be able to do anything else in FileMaker Pro until they had dealt with that window.

Additionally we wanted to leverage the transactional model, so that edits would take place in standard fields, with changes made by the user committing if they clicked Save, and evaporating if they clicked Cancel. Reminder: transactionality is an all-or-nothing proposition. You either get all your changes or none of them. You never end up with half baked data.

And, finally, we wanted to map the Esc key to the Cancel button, and map the Return and Enter keys to the Save button. Continue reading “Modal Transactional Card Window” →

JSON, Level: Advanced, Version: FM 21 or later

JSON Optimization in FM 21

Kevin Frank

Introduction

If you work with large JSON structures in FileMaker, you may be aware that the current shipping version of the product (FileMaker Pro 21, a.k.a. FileMaker Pro 2024) has a single-element JSON cache, which is reset whenever JSONSetElement or JSONGetElement is called, and that you can boost performance by taking this into account.

Six months ago Josh Halpern was kind enough draw my attention to this in a comment on one of my articles, and added…

“If you’re interested, I wrote a couple more points about how the JSON cache behaves here: https://the.fmsoup.org/t/performance-core-principles-10-scripting/1867/25?u=jwilling. But the gist is… only touch one json at a time.”

[The above is required reading as far as I’m concerned, and has changed the way I work with JSON.]

More recently I was discussing JSON caching with Steve Senft-Herrera, and he shared a couple additional optimization ideas that had not occurred to me, and that I am definitely adding to my tool kit. (Thank you Steve.)

At any rate, today we’re going to take a look at applying all three of these ideas to speed up processing of large JSON structures within a While statement, and you can follow along in today’s demo file if you are so inclined.

Demo file: fm-21-json-optimization.zip  (requires FM 21 or later)
Continue reading “JSON Optimization in FM 21” →

Level: Intermediate, Macintosh

A Tip for MBS “Check Variable Names”

Kevin Frank

7 Aug 2025 – See also Another Tip for MBS “Check Variable Names”

If you’re on MacOS, and use the Monkeybread (a.k.a. MBS) plug-in with “check variable names” enabled, you quickly come to appreciate how useful this feature, also known as linting, can be.

For example I recently helped another developer track down a script problem that turned out to be a slight misspelling of a variable name, a misspelling subtle enough that it took us a long time to see it (of course for most of that time we didn’t realize the $var name was the culprit). If he’d had Monkeybread installed and this feature turned on, we’d have saved about 45 minutes. Actually I shouldn’t say “we”… my colleague would have immediately seen the problem and wouldn’t have bothered to contact me. Continue reading “A Tip for MBS “Check Variable Names”” →

JSON, Level: Advanced

January 2025 FM Training Livestream

Kevin Frank

Here are links to articles, custom functions, etc., related to my Wednesday, January 15th FM Training Livestream presentation on JSON custom functions.

YouTube link

Demo Files

JSON and JSON Custom Functions

Other JSON-Related Articles

JSONQuery

 

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

JSONQuery Odds and Ends

Kevin Frank

This is a quick “odds and ends” post to share the current iteration of my JSONQuery sandbox file. Some of the examples are new; some are from presentations I gave earlier this year. Most of them illustrate one or more things you can do with this amazing custom function (a few of the examples don’t deal with JQ directly, but play a supporting role). The aim is to convey a tiny measure of the depth and breadth of what you can accomplish with JSONQuery.

Resources:

The Sandbox file requires FM 19.6 or later (but a few of the examples use features introduced in FM 21).

A bit of info in case this is the first time you’ve heard of JSONQuery. It is a custom function which operates on JSON, created and maintained by Steve Senft-Herrera.

Typically you’re going to point it at an array of objects…

…or an array of arrays…

…and it will return child elements matching your query parameters. Continue reading “JSONQuery Odds and Ends” →

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

JSONQuery v2 Conversation with Steve Senft-Herrera, part 2

Kevin Frank

Note 1: This conversation primarily focuses on new features and improvements in JQ v2. For a more general introduction to JSONQuery see the “Resources” section at the beginning of part 1.

Note 2: JSONQuery v2 is available here — https://github.com/steve-ssh/FMP_JSONQuery.

Continuation of JQ v2 conversation with Steve Senft-Herrera

Kevin Frank: There are a couple little improvements in v2 we haven’t mentioned yet. Is this a good time?

Steve Senft-Herrera: Sure.

KF: Okay, thanks. One that I really like is that you can now use “=” in place of “EQUALS” — you don’t have to, but you can. In v2 either of these will work…

JSONQuery ( $input ; "region" ; "EQUALS" ; "Asia" ; "" ; "name" )

or

JSONQuery ( $input ; "region" ; "=" ; "Asia" ; "" ; "name" )

KF: …whereas in v1 you had to write out “EQUALS”.

SSH: Yeah, I like that one too.

KF: Another improvement is that when “MATCH_ALL” is your Operator, you can now simply leave that argument blank. As per ex. 197 in the example file, these two statements are equivalent.

and

SSH: It’s a small change, but I like it because there is less to distract the eye from what really matters in the calculation: your input JSON, and the desired key or path to harvest — in this case, “address”.

KF: Completely agree.

The MAP Component

KF: You know there is an absolute favorite item on my list that hasn’t been mentioned yet, and it has to do with the simplification of MAP syntax.

SSH: That is definitely right there at the top of my list of favorite features also. Continue reading “JSONQuery v2 Conversation with Steve Senft-Herrera, part 2” →

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

JSONQuery v2 Conversation with Steve Senft-Herrera, part 1

Kevin Frank

Background

JSONQuery 2.0 was released last month with a wealth of new features, along with some improvements to existing features as well. Steve and I spoke about it recently, and we’ll get to that in just a moment. But first, for those who may not be familiar with JQ, here’s a bit of background information.

What is JSONQuery?

JSONQuery is a FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).

Basic Things JSONQuery Does

1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.

2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.

3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.

Official Site

https://github.com/steve-ssh/FMP_JSONQuery

Other Resources

Introductory Remarks

Kevin Frank: Welcome back Steve. It’s been 3 years since we had our first JSONQuery conversation, and I’m so excited about JQ v2.

Steve Senft-Herrera: Thank you. I checked out the dates just before we met tonight, and saw that it is about 3 years.

KF: Hard to believe it’s been that long, and I want to congratulate you, because I know that it’s been a formidable effort on your part to get v2 out. And I speak for a lot of FM developers when I say we truly appreciate it.

SSH: Well, thanks. The formidable part, I think, was actually just managing my own life schedule to make time for it, more than the actual work that had to be done. There were long periods of time where the work was mostly finished, but just had to sit there patiently before I could get to it and take it to the next milestone. Now it’s officially out. Continue reading “JSONQuery v2 Conversation with Steve Senft-Herrera, part 1” →

JSON, Level: Any, Version: FM 18 or later

JSONQuery 2.0 is Here

Kevin Frank

This is a quick post to say that JSONQuery 2.0 is finally here with many new features, and some improvements to existing features as well.

What is JSONQuery?

A FileMaker custom function authored by Steve Senft-Herrera that enables you to query JSON (works with FM 18+).

Basic Things JSONQuery Does

1) Query – Allows for finding elements that match a supplied value, with a variety of options for operators.

2) Aggregate – Allows for returning an aggregate, e.g., SUM, AVG, LIST, etc., of values taken from either the matched elements, or all of the input.

3) Transform – Allows for customization of how the results are returned, with the option to pick-and-choose what attributes are included in the output, and how they are named.

Official Site

https://github.com/steve-ssh/FMP_JSONQuery

Continue reading “JSONQuery 2.0 is Here” →