FileMakerHacks

Recently I received an email wondering…

When printing a multipage report sorted by a sub-summary, is there a way to display the sub-summary heading on the following page when it breaks across two or more pages?

There are various approaches one might take, and I actually buried one of those approaches inside an article + demo file posted on this site back in 2013 (PDF Catalog with Table of Contents). At the time we didn’t have “Hide Object” so I used conditional formatting in conjunction with a special-purpose “helper field” to flexibly display the conditional heading.

Well today we’re going look at an approach that doesn’t require a helper field, and you are welcome to follow along in this demo file if you are so inclined: conditional-summary-report-header.zip

Continue reading “Conditional Summary Report Header” →

JSON, Level: Intermediate

Thinking About JSON, part 3

August 9, 2018July 31, 2022 Kevin Frank

This article is part of a series. See also…
• Thinking About JSON, part 1
• Thinking About JSON, part 2
• Thinking About JSON, part 4

This a quick followup to last month’s part 2, because today I want to to dig a little deeper into JSONSetElement and take a closer look at the first argument:

As I wrote last time…

Part of what makes JSONSetElement so powerful is that it can be used both to create new entries, and to update existing entries. Specify a valid address, and it will either create the entry if it doesn’t already exist, or update it if it does.

Let’s talk about the update aspect first, because it’s extremely straight forward. If you have existing JSON in a variable, for example $$simpleJSON, you can update it (i.e., add new elements or change existing elements) like so:

- JSONSetElement ( $$simpleJSON ; etc )

In other words, you use your existing JSON as the first argument.

But when it comes to creating new JSON, there are three possible first arguments to consider.

- JSONSetElement ( “{}” ; etc )
  …instructing FileMaker to create a JSON object
- JSONSetElement ( “[]” ; etc )
  …instructing FileMaker to create a JSON array
- JSONSetElement ( “” ; etc )
  …trusting FileMaker to figure out which structure to create

At the risk of stating the obvious, in the first two cases we are being explicit, but in the third case we are not. (Note: If you’re unclear on the distinction between objects and arrays, this was covered in great detail in part 1.)

Continue reading “Thinking About JSON, part 3” →

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

Thinking About JSON, part 2

July 30, 2018September 8, 2022 Kevin Frank

This article is part of a series. See also…
• Thinking About JSON, part 1
• Thinking About JSON, part 3
• Thinking About JSON, part 4

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

June 29, 2018June 2, 2021 Kevin Frank

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

Continue reading “Virtual List Reporting with JSON Arrays” →

Level: Intermediate, Version: FM 13 or later

Fast Summaries Re-revisited

May 30, 2018September 8, 2022 Kevin Frank

My recent “Virtual List on Steroids” presentation at DIG-FM, and the preparation of a revised version to give at dotFMP next week, has prompted me to reconsider certain assumptions re: optimizing performance both locally, and across a LAN and/or WAN.

2018-05-29_094509

Specifically, there are two things we’re going to look at today. First, a way to dramatically speed up sorting on related data. Then, having incorporated that trick into the Fast Summary approach, we will compare Fast Summaries vs. Multi-Finds under various scenarios.

Demo files (structurally identical):

Fast Summaries Re-revisited (80K records) [3 Mb compressed]
Fast Summaries Re-revisited (640K records) [20 Mb compressed]

Continue reading “Fast Summaries Re-revisited” →

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

Virtual List on Steroids, part 2

May 10, 2018September 8, 2022 Kevin Frank

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

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

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

April 23, 2018September 8, 2022 Kevin Frank

This article is part of a series. See also…
• Thinking About JSON, part 2
• Thinking About JSON, part 3
• Thinking About JSON, part 4

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

This article is intended to be a structured introduction to JSON in FileMaker. There are some other good FM/JSON resources out there, and I recommend these 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” →

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

Custom Field-Based Value Lists

March 12, 2018September 8, 2022 Kevin Frank

Introduction

Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them?

2018-03-09_14-44-10 — The “problem”: field-based value lists sort alphanumerically

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”

2018-03-23_120634 — From demo #4: field-based value lists mirroring the order of their underlying source fields

Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.

Continue reading “Custom Field-Based Value Lists” →

Level: Intermediate, Version: FM 16 or later

Exposed by Tableau

February 8, 2018September 8, 2022 Kevin Frank

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

Level: Intermediate