Version: FM 18 or later – FileMakerHacks

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" )

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

October 31, 2024December 25, 2024 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

Oct 2021 – FileMaker Hacks interview w/ Steve Senft-Herrera, part 1
Nov 2021 – FileMaker Hacks interview w/ Steve Senft-Herrera, part 2
Jan 2022 – FMDiSC YouTube presentation (Steve & Kevin)
Feb 2022 – FMDiSC YouTube presentation (Steve goes under the hood)
Jul 2024 – DIGFM YouTube presentation (Kevin w/ Steve)
Sep 2024 – FMDiSC YouTube presentation (Kevin w/ Steve)

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

September 16, 2024October 31, 2024 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” →

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

JSON Currency Exchange Rates, v3

September 30, 2023September 19, 2024 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

February 27, 2023August 2, 2025 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:

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

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

Button Bar Segment Fun

May 31, 2022June 2, 2022 Kevin Frank

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

April 4, 2022April 13, 2022 Kevin Frank

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

March 27, 2022April 4, 2022 Kevin Frank

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.