Category Archives: 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: (6Mb, contains two .twbx files)
Tableau 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

JSON Array + Virtual List

10 Sep 2017: Dave Graham has made a couple significant improvements to the original demo file that accompanied this article. I recommend reading his comments at the end of this article, and using the techniques featured in his version of the demo file —

Over the last few postings we’ve been looking at using JSON with Virtual List… and prior to today, our focus has been on JSON structures using name/value pairs. Well today we’re going to switch our attention to basic JSON array structures.


Demo file: (requires FileMaker 16.02 or later)

Continue reading

Virtual List Reporting + JSON

Today we’re going to look at an alternative approach to the multi-window VLR technique we examined last month in Virtual List Reporting part 3, utilizing and extending techniques introduced in last month’s JSON + Virtual List. To avoid needless repetition, today’s article will assume the reader is familiar with that material.


Demo files: vlr-plus-json-v1 and vlr-plus-json-v2

As you may recall from VLR Part 3, we have a system that allows us to spawn as many separate reports as we wish (each in its own browse mode window) and sort each report independently via clickable column headings. Continue reading

JSON + Virtual List

31 July 2017: As per my exchange with Beverly Voth in the comments section of part 2, the date encoding technique used here is strictly intended for situations where FileMaker will both produce AND consume the JSON.

29 July 2017: demo has been updated to require a minimum version of FileMaker 16.02 as per my exchange with John Renfrew in the comments section.

One nice thing about FileMaker being on a yearly release cycle is that there is always something new to learn and write about… and, having recently attended a pair of highly informative sessions on the topic of JSON (JavaScript Object Notation) at Devcon 2017 — thank you Todd Geist and Anders Monsen — it is clear to me that JSON + virtual list is a compelling combination.


Demo file:

My original plan was to integrate JSON with virtual list reporting, but in the interest of clarity, I decided to save that for a future article. Today we have a straightforward demo that a) generates a small amount of JSON from a standard FileMaker table, and then b) renders it in a virtual list table. Continue reading

FM 16: SortValues and UniqueValues

[Update 7/1/17, 12 noon (PDT): demo file has been updated to make the found set tracking more robust; article text has been revised to reflect this.]

Today we’re going to look at two functions introduced in FileMaker 16: SortValues and UniqueValues, and you may want to download and follow along in this demo file: FM 16 – SortValues & UniqueValues (requires FM 16 or later).

Each of these functions operates on a return-delimited list of values, and the names are self explanatory: SortValues allows you to intelligently sort the list in ascending or descending order, and UniqueValues removes duplicate entries. Continue reading

Export Field Contents (Update for FM 16)

Editor’s note: I am thrilled to feature a guest article by Beverly Voth.

In the article “An In-Depth Look at “Export Field Contents” (here), the study of how FileMaker exports text as single field contents is compared to the standard FileMaker Exports. Some of these present a problem when the field (exported) gets converted to something which a receiving system may reject as invalid. Several methods and alternative “fixes” are presented, including using XML & XSLT.

Update! NEW IN FMP 16 – we have a FIX!

There is a new function in FileMaker Pro (and Advanced) 16 that “fixes” a couple problems with Export Field Contents (namely the encoding and end-of-line). Instead of the Base64 nested function trick use the new Function:

TextEncode( text ; encoding ; lineEndings )

Reference: FM16 Help: TextEncode

Using TextEncode() & Export Field Contents

Set Field [ container ; TextEncode ( text ; encoding ; lineEndings ) ]

Continue reading