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

JSON Custom Functions for FM 19.5, part 2

Demo Files

Note: some of the CFs have been revised and/or renamed since part 1, so if you plan to use these CFs, make sure to download today’s “part 2” file.

Introduction

Welcome back to JSON Custom Functions for FM 19.5, round two. In part 1 we explored these CFs…

…and today we’ll be taking a look at the ones we didn’t cover last time. As before, a key ingredient in some of these CFs is JSON.GetValueType, which auto-determines the type of an element that is being added to an object or an array.

Today’s Custom Functions

Disclaimer: use at your own risk, these CFs may contain bugs, these CFs may not be performant when processing large amounts of data, always test carefully before deployment, etc., etc., etc.

JSON.FromTextArray ( array_2d ; colDelimiter ; rowDelimiter ; optionalKeyList )

Purpose: generates either a 2-dimensional JSON array or an array of JSON objects from a delimited 2-dimensional input array (for example, the response from a SQL query). The absence or presence of optionalKeyList determines whether you get back this…

[ [ value, value, value... ], [ value, value, value... ], etc ]

…or this…

[ { key:value, key:value, key:value... }, { key:value, key:value, key:value... }, etc ]

For example, assume the following result of a SQL query is sitting in a $$sql.array variable…

9035|New Receivable|1|010.003.001.001
9036|Review & Post Receivables|1|010.003.001.002
9037|Unpost Receivables|1|010.003.001.003
9038|New Direct Receipt|1|010.003.001.004
9039|Apply Receipts|1|010.003.001.005
9040|Review & Post Receipts|1|010.003.001.006
9041|Apply Credits & Prepayments|1|010.003.001.007
9042|Unpost Bank Deposits|1|010.003.001.008

What we get back from the custom function depends on the final argument.

Ex. 1 — the final argument is blank so the CF returns an array of arrays.

Ex. 2 — the final argument contains a list of key names so the CF returns an array of objects.

(If you’re curious about JSON.Format2D, that was discussed in part 1.)

JSON.MapJsonFromTextArray ( array_2d ; map ; colDelimiter ; rowDelimiter )

This one comes from Cristos Lianides-Chin, and is used with his kind permission. It came about after I showed him the preceding CF and he replied, “You know what would be a logical next step? To implement a map.”

Purpose: Given a 2d text array, such as would be returned from ExecuteSQL, maps each array row into the schema of a JSON object and returns a JSON array of objects.

Example — using the same $$sql.array variable as above, and a $$map variable populated like so…

…this statement…

JSONFormatElements ( 
    JSON.MapJsonFromTextArray ( $$sql.array ; $$map ; "|" ; ¶ ) 
)

…produces this.

JSON.DedupeArray ( array )

Purpose: removes duplicate entries from a JSON array.

Example — given this input…

…here is the corresponding output.

JSON.ObjectFromRecord ( tableOccurrenceName ; fieldToExclude )

Purpose: creates a correctly-typed JSON object for the specified record.

Sometimes you want to create an object representation of a given record. You don’t want to be bothered with specifying fields, you just want it to work with a minimum of thought and effort on your part.

Note 1: if you use this CF in a calculated field do not use Get ( LayoutTableName ) as the first argument… reason: when you look at the field across a relationship, you will get the json object for the parent, not the child.

Note 2: if you use this CF in a calculated field, to avoid a circular reference make sure to pass that field name in as fieldToExclude, otherwise pass in "" for the second argument.

IMPORTANT: unfortunately GetFieldName ( Self ) cannot be hard-coded into a custom function but we can pass it in as a parameter.

Ex. 1 — a calculated field in the customers table defined like so…

…will return something like this.

Ex. 2 — this similarly-defined calculated field in the sales table…

…returns this.

In the above example, since the CF doesn’t pick and choose, the result includes the output from two other “as_json” fields. If we wanted to, we could modify the definition of as_json_record like so…

…to get those other two out of there.

JSON.OptionalObjectFromFieldList ( objectName ; fieldList ; customNameList )

Purpose: creates a correctly typed JSON object based on field list, ignoring empty fields.

Ex. 1 — without custom names (assuming affiliation and birthPlace are empty).

Ex. 2 — as above, but with custom names.

Note: as per the above example, customNameList can be sparsely populated to allow a combination of custom and native field names.

JSON.CountElements ( json )

Purpose: returns count of non-null elements in a JSON object or array.

Note: Counts each entry, excluding nulls. If you want to count *all* entries (including nulls) use JSON.CountKeys instead

This CF came about when I needed to count non-null entries in a JSON object and some of those entries included embedded returns. The following examples reference this $$json variable which includes a two-line “address” entry.

Ex. 1 — the typical “count values” approach will count one value for “company” and two values for “address”.

Ex. 2 — the custom function disregards \r and \n so each element is only counted once.

JSON.CountKeys ( json )

Purpose: as above, but counts keys instead of elements.

Note: although rarely encountered, it is possible for object keys to contain “vertical white space” characters such as hard returns, line feeds, "\n", "\r", etc.

Assuming a variable, $$wonkyKeys, populated like so…

…resulting in a key list that looks like this:

Ex. 1 — the typical approach will count each key twice.

Ex. 2 — the custom function gets it right.

JSON.ListAllValues ( json )

Purpose: behaves like JSONListValues but returns empty rows for nulls.

Note: works with both objects and arrays.

Ex. 1 — FileMaker’s JSONListValues function swallows the nulls.

Ex. 2 — the CF renders nulls as empty rows, thereby preserving the position of subsequent items in the list.

JSON.KeyExists ( json ; keyOrIndexOrPath )

Purpose: returns a 1 if specified key is present, otherwise returns 0.

Ex. 1 — does key exist within an object?

Ex. 2 — does key exist within an array?

JSON.Deflate ( json )

Purpose: unformats formatted JSON, with no harm done if the JSON isn’t formatted to begin with.

Example: given this formatted array…

…the custom function returns this:

Note: you can achieve the same result by doing this (which, in fact, is what the CF does under the hood):

JSONGetElement ( $$small.array ; "" )

JSON.IsValid ( input )

Purpose: returns a 1 when input is valid JSON, otherwise returns 0.

Ex. 1 — input is JSON.

Ex. 2 — input isn’t JSON.

Interlude

The remaining custom functions are featured in today’s second demo file, JSON Insert and Update for FM 19.5.

Back in 2019 I did a pair of articles regarding certain limitations of JSONSetElement, which you can read here if you are so inclined.

In a nutshell, the “update” CF was created to allow a multi-dimensional auto-generated array to overwrite a non-empty (i.e., existing) array address. And the “insert” CF was created to allow one to insert an element into an array, shifting all downstream elements one position rightward to accommodate.

So why write new CFs for FM 19.5? Because the demo files and accompanying CFs worked fine when FM 18 was the current version, but when FM 19 was released something broke, and rather than fix old code, I decided to update the CFs to take advantage of JSONGetElementType, and combine both of them into a single demo file.

JSON.UpdateElement ( source ; address ; element )

Purpose: works like JSONSetElement but doesn’t balk when overwriting an existing element with an autogenerated array.

Ex. 1 — given this source array…

…we can push an auto-generated array into address [1] (which currently contains null) using JSONSetElement.

However, if we instead target [2] (which currently contains an object) with JSONSetElement, chaos ensues.

Ex. 2 — JSON.UpdateElement to the rescue.

JSON.InsertArrayElement ( source ; address ; element )

Purpose: inserts an element into an array, with any existing array elements sliding one position rightward to accommodate.

Ex. 1 — FileMaker enables us to overwrite an existing value via JSONSetElement…

…but does not provide us with a corresponding function to insert a value into an array so that existing elements shift one position rightward.

Ex. 2 — JSON.InsertArrayElement remedies this shortcoming.

JSON.GetArrayElementPosition ( source ; address )

This is a helper CF used by JSON.InsertArrayElement.

Purpose: translates a JSON array address into the corresponding FileMaker position value.

Note: this CF disregards JSON formatting (if any) and always returns the position you would get if the source were unformatted.

Ex. 1 — given this source json…

…we can derive the corresponding FileMaker position for address [1] like so…

…or, if we want to save a few keystrokes, since this is a single-dimension address, like so:

Ex. 2 — this time with a more complex source and address.

JSON.ValidAddressSegment ( source ; address )

This is a helper CF used by JSON.UpdateElement and JSON.InsertArrayElement.

Purpose: identifies the portion of a target address that already exists in the source JSON.

Example — given this source and address…

…the “valid” address segment (the portion that already exists) can be determined like so.

Conclusion

I use some of these CFs once in a while, and others on a daily basis. If you’ve made it this far chances are you’ll find uses for one or more of them in your own work, and if you find any bugs, or have suggestions for improvement, I’d appreciate it if you would let me know by posting a comment here.

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

JSON Custom Functions for FM 19.5, part 1

9/13/2022: Demo file has been updated to fix a bug in JSON.MergeArrays.

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, to save time and to boost productivity — for example, to merge two objects into a single object, or to deduplicate an array. Continue reading “JSON Custom Functions for FM 19.5, part 1”

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”

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”

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”