Category Archives: Level: Intermediate

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

Fast Summaries Re-revisited

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

Continue reading

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

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

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

Virtual List on Steroids

[Note 1: 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.]

[Note 2: the following assumes the reader is familiar with basic concepts presented in Virtual List Reporting, part 1.]

This is a follow up article to document new material from my recent FM-DiSC presentation: Virtual List on Steroids. The presentation was intended for developers who are (at least) somewhat familiar with virtual list, but for those not actively using virtual list, I decided to begin with a demonstration of “the problem” (or one of the problems at any rate) that virtual list was invented to solve.

Demo file #1: Step One – Admitting There’s A Problem

2018-01-22_112738

Continue reading

Excel Exports using XML and XSLT

Editor’s note: Once again it is an honor and a privilege to present an in-depth guest article written by Beverly Voth.

This is a follow-up to the User-Friendly Excel Exports series by Kevin Frank, but using an XML export along with various XSLT to give you .csv or .xls (for Excel). The first demo is a quick export (to CSV – comma-separated values) using an XSLT to help explain the relationship with FileMaker Pro XML export. How an XSLT style sheet reads that data to transform it into another text format (.csv or .xls) is also explained throughout the article and in comments within the XSLT. The demos will get increasingly more complex, but ultimately more “user-friendly”.

Demo file: Excel-Exports-using-XML-and-XSLT.zip

Continue reading

Reciprocal Linkage, part 3

Note: Leaner does not always mean faster, especially if record counts will be large… in which case I would recommend going with the approach discussed in part 2.

[3 Jan 2018: demo updated to version 3.2 as per Jens Rasmussen’s comment below.]

[2 Nov 2017: demo updated to version 3.1 as per my exchange with Jonathan Mickelson in the comments section below.]

I thought after part 2 that this technique had been thoroughly discussed and dissected, but Geoff Gerhard of Creative Solutions writes to point out that, if the goal is to have as few moving parts as possible, the table occurrence (TO) count can be further reduced, from four to three…

…and he has provided a demo file which I am sharing with his permission.

Demo file: reciprocal-linkage-GGv3.2.zip

Continue reading