Category: Level: Intermediate

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

Traversing Arrays with Mod() and Floor()

Kevin Frank

Demo file: traverse-arrays-with-mod-and-floor.zip

Introduction

Today I want to talk about a technique that can come in handy if you need to walk a block of contiguous cells within a 2-dimensional JSON array for either reading and/or writing purposes.

[ 
   [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ],
   [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ],
   [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ],
   [ n, n, n, n, n, n, n, n, n, n, n, n, n, n, n ]
]

The standard approach would be to employ a While within a While, the outer to walk the columns and the inner to walk the rows (or vice-versa). And that’s a very valid way to go about it, but I was wondering whether a 2-dimensional array of arbitrary height and width could be traversed via a single While call. Well it turns out that yes, it is possible, and we’re going to look at an approach that can accomplish this with help from the Mod() and Floor() functions.

Let’s say we have a 4 row × 15 column array, for a total of 60 cells that we want to iterate through. Since JSON uses zero based indexing, we can consider the rows to be numbered 0 – 3 and the columns to be numbered 0 – 14 like so.

Assume that our task is to fill the array with the letters A through Z, one letter per cell, starting with column [0] and populating all the rows in each column before moving on to the next column. Since there are only 26 letters in the English alphabet, after Z has been assigned start with A again, and keep iterating through the sequence until all 60 cells have been populated (and while we’re at it, let’s go with lower-case letters to make it easier on the eyes).

Conceptually, it looks like this…

…and as JSON, like this.


Continue reading “Traversing Arrays with Mod() and Floor()” →

Level: Intermediate, Version: FM 16 or later

FM 22: Pseudo Indexing

Kevin Frank

Demo file: FM-22-Pseudo-Indexing.zip (works w/ FM 16 or later)

When I wrote about Sort Values and Unique Values back in 2017, I made passing reference to an article I’d written a couple years previously on the topic of “pseudo indexing“, i.e., displaying a list of unique values for a specific field for a found set, rather than for all records.

Actually, I wish things were that simple. Full disclosure: there was the original FM 13 demo/article. Then when FM 16 was released, I did a second demo/article, “FM 16 Pseudo Indexing”. Recently I discovered a flaw in the approach I used in the FM 16 article, so I am pulling that demo and article off line in favor of this one.

Today we’re going to dust off the old pseudo indexing demo, and update it to take advantage of various FileMaker functions and innovations that were not available back in 2015. Why bother? Here’s why: the new implementation…

  1. is simpler (has fewer moving parts)
  2. scales better
  3. is less brittle (prone to breakage if schema is renamed)
  4. enables you to bring new fields on board faster
  5. can sort the pseudo index in ascending or descending order

Continue reading “FM 22: Pseudo Indexing” →

Level: Intermediate, Macintosh

Another Tip for MBS “Check Variable Names”

Kevin Frank

This is a quick follow up to A Tip for MBS “Check Variable Names” from earlier this year. The other day I noticed variable name checking wasn’t working in one of my scripts. The culprit turned out to be MBS’s default 500 step limit for variable name checking. According to the documentation, the limit exists for performance reasons, but it turns out it can be adjusted like so.

MBS ( "SyntaxColoring.CheckVariableDeclaration.SetLimits" ; 2000 )

As you can see, I’ve quadrupled the number of script steps that MBS will check. I don’t plan to ever write, or have to work on, a script this long, but you never know. At any rate, problem solved, and so far I have not noticed any performance degradation in the script workspace.

You can read all about this function here on the MBS web site — https://www.mbsplugins.eu/SyntaxColoringCheckVariableDeclarationSetLimits.shtml

Level: Intermediate, Version: FM 12 or later

Boxed Summary Groups for Reports

Kevin Frank

Demo file:  boxed-summary-groups.zip

Recently I needed to produce a summary report with variable height rows and each group enclosed in a rectangular box. To make the challenge more interesting, there were multiple fields in the body part, and it was not known in advance for any given record which would be tallest. At any rate, the approach I decided to go with took a bit of trial and error to get working and I figured I’d share the process here in case anyone else finds it useful.

Continue reading “Boxed Summary Groups for Reports” →

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” →

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, Level: Intermediate, Version: FM 16 or later

FastRange Custom Function

Kevin Frank

Recently I needed to produce a large range of consecutive numbers. FileMaker doesn’t provide a built-in function for this but it’s easy enough to accomplish, for example, using the While function or a recursive custom function. In this case, I decided to go a different route based on a tip I’d seen someone post years ago (I’ve forgotten whom so cannot give proper credit) that you can leverage a couple of FileMaker’s JSON functions to facilitate this task, with the advantages being blazingly fast performance and an opportunity to think outside the box.

Demo file:  fastrange-cf-v1.04.zip
Continue reading “FastRange Custom Function” →

Level: Intermediate

Replace + GetNthRecord to Fill Down

Kevin Frank

This afternoon I pulled this data into FileMaker from a spreadsheet, and needed to “fill down” the region names in f2.

Of course I could have filled the region down in Excel prior to importing, but here I was already in FM. At first I was going to use a looping script, but then it occurred to me that it would be nice if Replace could do the trick. Continue reading “Replace + GetNthRecord to Fill Down” →