Level: Any, Version: FM 16 or later

FilterValues, part 2

Sometimes it happens that the real value of a blog posting emerges in the comments section. Such was the case last time, when Paul Jansen posted an elegant little workaround to remedy a performance issue with the FilterValues function. Paul’s workaround is deserving of its own demo, and that’s what we’re going to look at today.

Demo file: filtering-values-v2.zip

2020-06-02_230953

Continue reading “FilterValues, part 2”

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”

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: Intermediate, SQL, Version: FM 16 or later, Virtual List

2-Column Magic Value Lists, part 2

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

Introduction

This is a quick follow-up to last month’s article on 2-Column Magic Value Lists, and, to avoid repetition, will assume the reader is familiar with that material. A colleague pointed out that it might be helpful to offer some real-world applications of this somewhat esoteric technique, and today we have two demos based on last month’s demo #2, but this time actually doing something useful.

2019-11-11_065703.png

Demo Files

Continue reading “2-Column Magic Value Lists, part 2”

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”