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”

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

JSONQuery, part 2

Continuation of interview with Steve Senft-Herrera

[Editor’s note: the demo file and custom function have been significantly updated since part 1.]

Demo file:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip


KF: Welcome back Steve for part 2 of our JSONQuery conversation.

SSH: Thank you, Kevin.

KF: One thing we didn’t mention last time, because they were late-breaking additions, were the inequality operators.

Continue reading “JSONQuery, part 2”

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

JSONQuery, part 1

Interview with Steve Senft-Herrera

[30 Nov 2021: Custom function and demo file have been updated. Some of the screen shots and example numbers referenced here in part 1 will deviate slightly from what you will find in the updated demo.]

Demo file:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

KF: Good afternoon, Steve. You’ve been developing JSONQuery over the last few years, and today I have the honor of presenting and discussing it here with you on FileMaker Hacks. I was wondering if you could start out with a brief description of what JSONQuery is?

SSH: Sure. JSONQuery is a custom function, and it operates on JSON. Typically you’re going to be feeding it a large JSON array you’ve received back from the FileMaker Data API, or somebody else’s API, where each record is a JSON object within a larger parent JSON array, and the purpose of this function is to be able to find child elements in that parent array that match certain criteria and return just those elements to you. 

Above and beyond that it has a lot of bells and whistles, some of which I’m sure we’ll cover, but the main impetus for writing it was giving you an easy and fast way to essentially query a JSON array.

For example, let’s say you have an array filled with a lot of orders, but you only need to get the order items that are being shipped to a certain city, or to a certain state, then this function would allow you to easily obtain those elements in an efficient manner. Continue reading “JSONQuery, part 1”

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

Virtual List Reporting, part 4

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 19 or later

FileMaker 19 + JSONata

Introduction

Recently I had the pleasure of speaking with Jeremy Brown on The Context Podcast about an open-source project called JSONata that enables you to query and transform JSON.

The JSONata site is packed with useful information, including this short video…

…as well as…

  • Extensive documentation
  • A link to the Stack Overflow forum, where you can ask questions (which may or may not get answered), and
  • The JSONata Exerciser, where you can experiment with pre-existing examples, or with your own pasted in JSON

Okay, a JSON query and transformation language sounds intriguing, at least in theory, but wouldn’t it be great if we could harness that power from within FileMaker? Well, now we can, utilizing techniques in this demo, built by Jeremy and shared here with his permission.

Demo file: JSONata.zip (requires FM 19+ on Mac, 19.3+ on Windows)

As you’ve probably surmised, the demo makes use of the Perform JavaScript in Web Viewer script step. If you’d like to learn more re: what’s going on under the hood, Jeremy goes into specifics here: Towards a Better JS Development Environment.

Note: the demo requires an online connection to access the JSONata library. Continue reading “FileMaker 19 + JSONata”

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

Render Found Set as JSON Object

Demo file: found-set-to-json-object.zip (40MB; requires FM 18 or later)

[Author’s note: the demo file is a work in progress. Modify it as you see fit.]

Today we’re going to compare various methods one might consider employing to render an arbitrary found set as a JSON object. The demo consists of a table of US customers with 250K records, and the JSON object will be structured like so…

{ 
   State Name: 
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   State Name:  
      { County : [ [ customer array ] , [ customer array ] ... ] , 
        County : [ [ customer array ] , [ customer array ] ... ] , 
        ...
      } , 
   ...
}

…i.e., grouped by state name and county, with customer data represented as a two-dimensional array, for example: Continue reading “Render Found Set as JSON Object”

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

JSON Currency Exchange Rates

Update 11 Apr 2021: as per comments below, demo has been updated to use a new service: openrates.io.

Welcome back for another exciting round of JSON exploration. Last time we discussed a JSON-related bug fix in FM 19, as well as a free package tracking service that returns results in JSON format. Today we’re going to look at a free currency exchange rate service.

Demo file: json-exchange-rates-via-openrates.zip

2020-08-24_09-12-59 Continue reading “JSON Currency Exchange Rates”

JSON, Level: Intermediate

A JSON Miscellany

Today we’re going to look at a bug fix in FM 19, and a free web service that returns useful information in JSON format.

Fixed in 19: JSON validation bug

In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect and correct. I wrote about this in detail last year, but, briefly, a JSON bug that was introduced in FM 18 has been corrected in FM 19.

Steps to replicate:

Create some JSON, and then transform it, but intentionally botch the transformation, for example, attempt to update an array using object notation:

2020-07-05_125525 Continue reading “A JSON Miscellany”

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

Ordering Elements within JSON Objects

Introduction

If you’ve generated JSON objects via FileMaker, you’re likely aware of the disparity between the order in which you specify the elements, and the order in which they subsequently appear (i.e., alphabetized by key name). As I wrote two years ago (in Thinking About JSON, Part 2)…

Both JSONSetElement and JSONFormatElements will automatically alphabetize key/value pairs within JSON objects. This can be disconcerting if you aren’t expecting it, but eventually you work through the stages of grief and come to accept that it’s just the way things are. The JSON Data Interchange Standard definition at json.org specifies that “An object is an unordered [emphasis mine] set of name/value pairs”, whereas an array is an “ordered collection of values”. In other words, by definition the order of the key/value pairs within JSON objects simply does not, and should not, matter.

Well… okay… you know that, and I know that, and FileMaker knows that, as do the JSON Jedi… but what if you are demonstrating a proof of concept to a client, and to avoid cognitive dissonance and unnecessary explanation you’d like to “doctor the evidence” (so to speak) and order the elements meaningfully?

For example, you’d like them to see this…

2020-04-29_165223 Continue reading “Ordering Elements within JSON Objects”