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

JSONQuery, part 1

Interview with Steve Senft-Herrera

[Editor’s note: the demo file has evolved a bit since this interview took place, and, as a consequence, screen shots shown here may deviate slightly from what you will find in the demo.]

Demo file: CF_JSONQuery_20211029_1200_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”

Level: Advanced, Version: FM 16 or later

Set Variable By Name Re-Revisited

Demo files: set-var-by-name-v4 and set-var-by-name-md5

Background

This is a quick follow up to last December’s Set Variable By Name Revisited, and to avoid repetition will assume the reader is familiar with the material that was presented in that article. But to briefly recap:

1. FileMaker does not provide an obvious way to programatically name a variable.

Continue reading “Set Variable By Name Re-Revisited”

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”

Level: Advanced

FRAP-Map: A Flexible, Robust, Accessible and Portable Data Mapping Technique

Editor’s note: today we have a guest article by long-time FileMaker developer, Peter Doern, who has come up with an elegant, outside-the-box methodology for managing imports. This is one of the coolest techniques I’ve seen in a long time. Enjoy.

FRAP-Map Overview
Flat data goes in; related data goes out.

FileMaker includes a powerful and fast method for importing data from a source table to a target table. FileMaker 18 introduced an overhauled file import dialog which allows us to easily specify source data character set and delimiter, target fields, and auto-enter options on import.

But what if you want to pull a source field into multiple target fields, manipulate data during import, or split a flat source table into multiple related records? What if you want to import data from multiple different sources with different structures into a single, consistent, file? What if the structure of the source file changes frequently?

Using the traditional FileMaker import method, any of these scenarios will require multiple imports using hard-coded import script steps, or a solid understanding of XML and XSLT transformations. This Flexible, Robust, Accessible and Portal Mapping technique, aka FRAP-Map, provides a powerful alternative.

Continue reading “FRAP-Map: A Flexible, Robust, Accessible and Portable Data Mapping Technique”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

Fun with SQL Joins and Currency Formatting

Demo file: sql-join-fun-etc.zip requires FM 18 or later.

Recently I was asked to create a mini-report combining data from a pair of related tables via ExecuteSQL.

This provided an opportunity to think about SQL joins, and also to come up with a way to apply currency formatting to dollar amounts in the SQL query result, given that FileMaker’s SQL implementation does not support the standard SQL way of accomplishing this (e.g., CAST AS NUMERIC or CAST AS DECIMAL) . Continue reading “Fun with SQL Joins and Currency Formatting”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

File IDs, Table IDs and Field-Level Schema

21 Jan 2021: demo has been updated to fix a couple bugs; article has been updated to reflect these changes.

Demo file:  file ids, table ids and field schema, v4.zip (requires FM 18 or later)

INTRODUCTION

Recently I was asked to help document a FileMaker solution by creating and populating a “schema” table as per the above screen shot.

One of the requirements was to programmatically determine internal ID numbers for the file and for the tables (as opposed to table occurrences) within that file, and this was accomplished by leveraging some script steps that were introduced in FileMaker 18.

(Thank you Rob Russell for pointing me in the right direction, i.e., to Save A Copy As XML.)

RUNNING THE DEMO

Clicking this button…

Continue reading “File IDs, Table IDs and Field-Level Schema”

Level: Advanced, Version: FM 16 or later

Set Variable By Name Revisited

Update 19 Aug 2021: demo file has been superseded by the one available in Set Variable By Name Re-Revisited

INTRODUCTION

You’ve probably heard the old joke that goes…

Patient: Doc, it hurts when I do this.

Doctor: Don’t do that.

Perhaps you’re also familiar with the FileMaker equivalent?

Developer: It hurts when I try to assign certain variable names.

FileMaker: Don’t do that.

Continue reading “Set Variable By Name Revisited”

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”