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”

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

Creating and Using Invisible Window IDs

Editor’s note: I first became aware of Paul Jansen when I licensed his FMTools in the late 1990s, and I finally had the pleasure of meeting him last June at dotFMP after 20 years of online and voice communication. It’s an honor and a privilege to welcome him to FileMaker Hacks as a guest author.

FileMaker is pretty flexible. As developers we are given options as to how to reference things; fields, scripts and layouts can be referenced by name or number/id. Whilst we know that referencing things by name is more fragile, it is still a very useful capability to have, but it is definitely more robust to reference by ID…

Windows are a bit of an odd one out; we can only reference them by name.

As windows are probably the most likely things to have their names changed during normal day to day usage of a FileMaker Database it would be really useful if we could keep track of them by an ID independent of the displayed window title. Another benefit of having access to an ID to identify a window is that we then have to option to very easily store and access window specific variables. Kevin has made use of window specific variables based upon the window name in several articles and I suggest that access to a numeric ID that was independent of the window title would be an improvement. Continue reading “Creating and Using Invisible Window IDs”

Level: Intermediate, Version: FM 16 or later

Faux Subsummaries with Variable Height Rows

Recently we’ve looked at two methods to generate a “faux” subsummary to address a shortcoming of FileMaker native subsummaries… namely that in a multipage report you can have orphaned entries at the top of a given page with no indication of what parent entity they belong to.

2018-12-03_114620

The methods were documented here:

One limitation of both the above approaches is that they only worked with fixed-height report rows. Well today we have a nice, outside the box solution from Daniel Wood that does not suffer from this limitation.

Demo file: Faux-Subsummaries-with-Variable-Height-Rows.zip (requires FM 16 or later) Continue reading “Faux Subsummaries with Variable Height Rows”

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

Faux Subsummaries via JSON + Virtual List

Today we’re going to take another look at a challenge we discussed last time (in Conditional Summary Report Header)… namely how to cajole FileMaker into displaying a subsummary, or a reasonable facsimile thereof, at the top of a report page when items in the group begin on an earlier page.

2018-10-15_09-45-51

Demo file: Faux Subsummaries via JSON + Virtual List

2018-10-15_09-54-56.png Continue reading “Faux Subsummaries via JSON + Virtual List”

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

Thinking About JSON, part 2

This is a follow up to Thinking About JSON, part 1. Last time we were primarily concerned with learning about JSON paths and structures, and reading JSON. This time around we’re going to look at creating and manipulating JSON.

Demo file: winery-json.zip

(If the above screen shot looks familiar you have a good memory, because today’s demo is based on the one that accompanied this article: Summary List Fields in FM 13.)

To briefly recap, JSON is built on two structures…

  • Objects: surrounded by {} and consisting of comma-separated key:value pairs
    Simple example:  { “product” : “FileMaker Pro” , “version” : 17 }
  • Arrays: surrounded by [] and consisting of comma-separated values
    Simple example:  [ 2 , 4 , 6 ]

…and where things get interesting is that the “values” in either of the above structures can themselves be JSON (i.e., an object or an array). This defining feature of JSON, whereby a JSON structure can, and frequently does, contain embedded smaller JSON structures, was explored in detail in part 1, and we will see some examples of this today as well. Continue reading “Thinking About JSON, part 2”

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, you can find references to earlier articles here: Virtual List on Steroids, part 2. 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”

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

Virtual List on Steroids, part 2

[10 June 2018: includes updated material from my recent dotFMP presentation.]

[30 May 2018: check out my reassessment of the Fast Summary technique here.]

2018-05-09_19-04-17.png

Here are files that were demoed or referred to during my “Virtual List on Steroids” DIG-FM presentation on Thursday, May 10th, 2018 (VLR = “virtual list reporting). Continue reading “Virtual List on Steroids, part 2”

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

Thinking About JSON, part 1

I’ve been working on a couple large JSON projects over the last few months, and with the one year anniversary of FileMaker having built-in JSON capabilities just around the corner, this seems an opportune moment to share some reflections and opinions (some of which may contradict JSON-related opinions I have expressed previously).

Demo file: json-sandbox.zip

The following is intended to be a series of observations, rather than a structured introduction to JSON in FileMaker. If you’re looking for the latter, I recommend these resources in particular.

At any rate, if you’re not yet completely comfortable with JSON perhaps some of the following will be helpful, or failing that, amusing (intentionally or otherwise). Continue reading “Thinking About JSON, part 1”

Level: Intermediate, Version: FM 16 or later

Exposed by Tableau

Editor’s note: today we have a guest article written by John Weinshel, who has been quietly contributing to the FileMaker community for 20+ years. John’s thoughtful postings have helped developers at all levels of expertise, and I’m pleased to present his thoughts on Tableau here on FileMaker Hacks.

Demo files: exposed-by-tableau.zip (6Mb, contains two .twbx files)
Tableau trial: https://www.tableau.com/products/trial

The Tableau connector widget for the FMS 16 data API has made developers aware of the incredible interactive charts and graphs in Tableau. What may be less obvious is that Tableau can also tease out data that is hard to see natively—independently of the cool graphs.

FileMaker data is about a thing, an entity—the customer, stock, auto part, invoice item, reservation. With some additional work, we can see patterns in groups of data, but the default is to deal with it at the row level.

Visualization tools like Tableau, on the other hand, start out with groups of data. With some additional work, Tableau can elicit information about individual records, but the default is to work at the aggregate level. One of its strengths is, in fact, revealing patterns about groups of groups.

This post looks at how Tableau can—apart from its dazzling graphics—reveal hidden information within FileMaker data. It does not get into how to build calcs and views, nor how to connect Tableau to FileMaker data; some suggestions for learning about Tableau appear at the end of this article. Continue reading “Exposed by Tableau”