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: 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”

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

Virtual Portal, part 2

[Note: several hours after posting this article I realized the “Hide Object” calc could be streamlined. Screen shot and demo have been updated to reflect this.]

Demo file: virtual-portal-v2b.zip (requires FM 18 or later)

This is a quick follow up to the Virtual Portal article I posted the other day. As you may recall, the objective was to use virtual list to display disparate entities in a portal…

2019-12-26_154835

…via an array like this…

2020-02-16_18-29-04 Continue reading “Virtual Portal, part 2”

ExecuteSQL, JSON, Level: Advanced, Version: FM 16 or later, Virtual List

Virtual Portal

Demo file: virtual-portal-v1b.zip

This is a follow-up to last month’s article on virtual list, and this time we’re going to explore a way to use virtual list in a portal.

Imagine you have built a system where a Company can be a parent of a Mill, Refinery, Estate, or another Company.

2020-02-18_044932

Each of these entities exists as a separate table in your database…

2020-02-16_18-26-43

…and from the perspective of a Company record, you’d like to be able to see all immediate children. Continue reading “Virtual Portal”

Level: Advanced, Version: FM 16 or later, Virtual List

Virtual List Simplified

Demo file: virtual-list-simplified.zip

Note 1: The example in today’s article/demo is intentionally very basic.
Note 2: The demo is self-populating to keep the data current, so the values you see in the screen shots will not exactly match those you encounter in the demo.

Recently I had the pleasure of discussing virtual list with Paul Jansen and Jeremy Brown on The Context podcast. One consequence of having written so much on the subject over a period of many years, is that information has been spread across many articles. Another consequence is that my thinking re: certain implementation specifics has changed over time.

At the risk of stating the obvious, there are many, many ways to skin the virtual list cat, and the purpose of today’s article is not to say “this is the best way”, or imply that other approaches are flawed, but simply to propose one particular approach you might take — especially if you are either: a) new to virtual list, or b) already using virtual list, but aren’t completely happy with your current implementation.

At any rate, my aim today is to gather some useful insights from earlier articles into a single document (with an occasional new idea thrown in as well), and some of what follows has been recycled from those earlier articles. Continue reading “Virtual List Simplified”

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

2-Column Magic Value Lists

Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgements: today’s article would not have been written without the inspiration provided by Bruce Robertson, John Ahn (FM 12 ExecuteSQL Unconference Session), Andries Heylen (Magic Value Lists), Marcelo Piñeyro (How to Extend FileMaker Value List Sort Capabilities) and Jonathan Fletcher (It’s Sorta A Value List Thing).

Introduction

Last year, in Custom Field-Based Value Lists, we looked at a technique to enable single-column field-based value lists to display their contents in entry order, as opposed to standard index-based alphanumeric order. For example using the contents of this field (from a single-record table) as the basis for a value list…

2019-10-13_135535.png

…we came up with a way to sort our value list (a.k.a. “VL”) items like so…

2019-10-13_135628

…as opposed to what you would get with a standard field-based VL, i.e., this:

2019-10-13_135646.png

I ended the article by saying…

Today’s article looked at value lists based on return-separated values in a single field. In an upcoming article we’ll explore implementing a similar technique for sorted two-column value lists based on entries in a dedicated value lists table where each list item lives in its own record.

…and now, nineteen months later, am ready to pick up where we left off last time, and to avoid repetition, will assume the reader is at least somewhat familiar with the issues and techniques covered in that article (Custom Field-Based Value Lists).

Note: if you aren’t clear on why a 2-column value list might be preferable to its single-column counterpart (or to a custom value list for that matter), check out Thinking About Value Lists, part 1.

Demo Files

Continue reading “2-Column Magic Value Lists”

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

FM18: JSON.InsertArrayElement

Disclaimer: the technique shown in this article is provided on an as-is basis. Use with appropriate caution and at your own risk.

Demo file: JSON-Array-Custom-Functions.zip (requires FM 18 or later)

201-08-23-animated

Have you ever noticed that FileMaker does not provide a JSON function to insert a new element into an existing array? For example, given this array (and bearing in mind that JSON uses a zero-based index)…

[ “A” , “B” , “C” , “D” ]

…JSONSetElement ( array ; 2 ; “XXX” ) will transform it to…

[ “A” , “B” , “XXX” , “D” ]

… but we have no native JSON function to “push” a new element into position 2 so that existing elements slide one position rightward to accommodate, like so:

[ “A” , “B” , “XXX” , “C” , “D” ]

Continue reading “FM18: JSON.InsertArrayElement”

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

FM18: JSON.UpdateArrayElement

Disclaimer: the technique shown in this article is an attempt to work around an annoyance that can crop up when using FileMaker’s native JSONSetElement function, and is provided on an as-is basis. Use with appropriate caution and at your own risk.

Acknowledgement: I owe a debt of gratitude to Paul Jansen for many illuminating JSON-related discussions over the last year or so. Thank you Paul.

Bug fixes: Demo file has been updated to v3.

Introduction

Today we’re going to look at a custom function, JSON.UpdateArrayElement, written to circumvent a problem you may encounter when using JSONSetElement. (Note: see Thinking About JSON, part 1 and part 2 if you need a basic FM/JSON overview or refresher.)

Demo file: JSON-UpdateArrayElement-v3.zip (requires FM 18 or later)

2019-08-13_16-02-58.png

Why did I write a custom function to do what JSONSetElement apparently already does? Because under certain circumstances JSONSetElement does not work the way I believe it should. We’ll get to the custom function in a minute, but first there are a couple issues we need to examine. Continue reading “FM18: JSON.UpdateArrayElement”

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

Magic Portals

Today we’re going to look at a design pattern I’ve recently been using to accommodate a client request. The request is to be able to view and edit a parent, all related children, and all related grandchildren via a single “flattened” interface.

2019-04-24_16-22-61

Demo file: magic-portals.zip (requires FM 16 or later) Continue reading “Magic Portals”

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

Virtual List Reporting with JSON Arrays

Acknowledgment: As always a huge thank you to Bruce Robertson, for inventing virtual list, and for many other contributions to the FM community over the years.

Introduction

As a follow up to my recent “Virtual List on Steroids” presentations at DIG-FM and dotFMP, today I want to take a fresh look at using JSON arrays in conjunction with Virtual List Reporting.

JSON Array

JSON arrays + Virtual List are a natural fit, but, as we shall see, small changes in methodology can make a huge difference in terms of performance, and the approaches we’re going to explore today are the result of a lot of trial and error, and incorporate feedback and suggestions from Dave Graham, Paul Jansen and Perren Smith.

What follows will assume the reader is somewhat familiar with the basic ideas behind Virtual List. If you aren’t familiar, or need a refresher, check out Virtual List Simplified. We’ll get to the demos in just a minute, but first, a review of some of the benefits of using virtual list.

  • Flexible framework accommodates complex reporting challenges
  • Fast performance
  • No need to tamper with schema in your data tables or on the relationships graph
  • Unlike traditional FM reports, you can easily combine data from unrelated tables
  • Under certain circumstances, virtual list reports (VLRs) can be much faster to develop than traditional FM reports
  • 100% multi-user safe and friendly

Overview

Here’s the main idea in a nutshell:

2018-06-24_182509.png Continue reading “Virtual List Reporting with JSON Arrays”