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

JSON Custom Functions for FM 19.5, part 1

Demo File

Introduction

Today we have some custom functions (CFs) that can help you accomplish various JSON-related tasks in FileMaker. Back in 2018 I had this to say about JSON custom functions…

My inclination is to really understand something before I use a custom function to simplify things, but that’s a matter of personal choice… and one which can vary depending on the situation.

And four years later I find myself using JSON custom functions on a daily basis, typically not for “simplification”, but to boost productivity — for example, to merge two objects into a single object. Continue reading “JSON Custom Functions for FM 19.5, part 1”

Level: Intermediate

Sometimes Less Is More Reliable

Update: Make sure to read Tony White’s comment below. It contains important additional information re: the behavior described in this article.

Recently I noticed some code that had worked flawlessly for years was suddenly returning “?” instead of valid values. What it came down to was that I had renamed my file from “JSON Custom Functions” to “JSON Custom Functions for FM 19.5”

Well you know those functions like ValueListItems…

ValueListItems ( fileName ; valueListName )

…that have a fileName argument? The culprit turned out to be that I was using Get(FileName) for that argument, and I needed to instead use "".

All 21 of the design functions are vulnerable to this, ahem, behavior. From the online help —

  • If you specify a filename that contains a period, include the filename extension in the parameter. Otherwise, functions may interpret the period in the filename as the beginning of the filename extension, which can lead to unexpected results.
  • If you specify no filename (""), functions return results for the current file.

Source:  https://help.claris.com/en/pro-help/content/design-functions.html

Bottom line:  When using a design function and referencing the current file, use "" instead of Get(FileName). There’s no down side, and your code won’t break if you decide to add a period to the file name.

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

Thinking About JSON, part 4

This article is part of a series. See also…
•  Thinking About JSON, part 1
•  Thinking About JSON, part 2
•  Thinking About JSON, part 3

Demo Files

Make sure to download the correct version for your locale:

Introduction

Welcome back to the fourth installment in this ongoing series. It’s hard to believe it’s been four years since the last segment, but in the meantime some exciting developments have occurred at the intersection of FileMaker and JSON, including… Continue reading “Thinking About JSON, part 4”

Level: Intermediate

Button Bar Segment Fun, part 2

This is a quick follow up to Tuesday’s article, and to avoid unnecessary repetition I will assume the reader is familiar with that material.

Demo Files

There were some things in v3 I wasn’t completely happy about, and I realized this morning that they would be easy to fix, and that the act of fixing them would help demonstrate the power of using this approach in the first place. Continue reading “Button Bar Segment Fun, part 2”

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

Button Bar Segment Fun

Demo Files

Today we’re going to look at some ways single-segment button bars (SSBBs) can help produce dynamic column headings for list views and/or reports, with a goal of concentrating logic into the segment calculation and reducing schema dependencies elsewhere. This is a work in progress, rather than a finished, battle-hardened methodology. The aim is to explore possibilities and stimulate discussion.

Note: the demo files are built on top of an “empty” virtual list table. The point is not to (once again) dive into virtual list or clickable/sortable column headings, but to provide a list view we can pretend contains valid entries, while we focus on what’s going on in the layout header part.

Disclaimer: these techniques are in the proof-of-concept stage. As with all techniques on this (and any other) site, use with a healthy dose of common sense and at your own risk.

Continue reading “Button Bar Segment Fun”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

Exploring Wordlespace with SQL and While

Recently we’ve discussed optimizing SQL queries in FileMaker, and had some fun with various SQL experiments. Today we’re going to explore some ways FileMaker can use ExecuteSQL and the While function to perform letter frequency and text pattern analysis on candidate words for the popular Wordle game.

The list of words comes from https://github.com/tabatkins/wordle-list and purports to include the actual answer words, as well as all allowable guess words. I don’t know how valid this list of words actually is, or, assuming it is currently valid, whether it is carved in stone or will change at some point in the future.

Demo file: SQL-Multi-Table-Experimentation-Wordle.zip

Some Notes

  • Today’s file is functionally identical to the one from last time; if you already have it, there’s no need to download this one.
  • No attempt is made to differentiate between daily Wordle words that have already appeared vs. those that have yet to appear.
  • SQL is case-sensitive in the WHERE clause; all our examples today use lower case letters so we may safely ignore the issue for the duration of this article.
  • For a general-purpose introduction to SQL in FileMaker, see Beverly Voth’s Missing FM 12 ExecuteSQL Reference.

Continue reading “Exploring Wordlespace with SQL and While”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

SQL Multi-Table & Miscellaneous Experimentation

INTRO

Today we’re going to pick up where we left off last month, and today’s article will assume the reader is familiar with the material we covered last time (in SQL Multi-Table Query Optimization).

This time we’re going to dig a little deeper into multi-table SQL queries, conduct some SQL experiments, and look at a way the While function can help speed up and/or extend the capabilities of an ExecuteSQL query.

Demo file: SQL-Multi-Table-Experimentation.zip

Continue reading “SQL Multi-Table & Miscellaneous Experimentation”

ExecuteSQL, Level: Advanced, SQL

SQL Multi-Table Query Optimization

Demo file:  sql-multi-table-query-optimization-v2.zip

Recently a colleague requested help w/ a SQL query that was performing slowly. I wrote back:

  1. Make sure there are no records open locally, i.e., on your machine, in the tables you are querying (for more information see this article by Wim Decorte: ExecuteSQL – The Good, The Bad & The Ugly)
  2. Only query stored values — if you’re querying a single table it should be fast
  3. If you’re querying multiple tables, make sure to construct the query to run optimally

Well today we’re going to take a look at #3 and see what we can learn re: optimizing multi-table queries. But first, I should point out that what follows reflects my current understanding of how things work. I hope this can be the start of a conversation where others will chime in and share their knowledge. Continue reading “SQL Multi-Table Query Optimization”

JSON, Version: FM 18 or later

JSONQuery at FM-DiSC

When / Who / Where

On Friday, January 14, Steve Senft-Herrera and I presented JSONQuery at FM-DiSC (FileMaker Developers in Southern California).

Useful Links

Recording of the presentation: https://www.youtube.com/watch?v=dztdZrHdrUQ

Current version of JSONQuery:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

Our recent two part in-depth interview series:

Coming soon: Steve Senft-Herrera’s demo file from the presentation

Kevin Frank’s demo file from the presentation: jsonquery-sandbox.zip

More information re: JSON + FileMaker:

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

Connecting Portals to JSON Arrays

Have you ever wished you could connect a portal to a JSON array? Portals and JSON arrays seem like they should be a natural fit, but FileMaker doesn’t offer us an obvious way to connect one to the other.

(Why would you want to do this? One use case would be to provide dynamic scrollable selection criteria for a report.)

At any rate, today we’re going to take a look at a little proof-of-concept I threw together to enable portals to display and edit data in JSON arrays. In a real-world implementation, the JSON would likely be sitting in a $$variable, which, among other things, would help make the technique multi-user friendly. Here, in the interest of simplicity, I’ve opted to store the JSON in a regular text field. A couple benefits of doing so:

  • You will see changes made in the portal immediately reflected in the JSON, and vice-versa.
  • As you navigate from record to record within the demo, the portals will reconfigure themselves to accommodate the corresponding JSON.

(Yes, it’s possible to accomplish the preceding with variables as well, but the aim here is to keep things simple).

Demo file: connecting-portals-to-json-arrays.zip

Continue reading “Connecting Portals to JSON Arrays”