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.
