Category Archives: Level: Intermediate

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 — dg-json-array-plus-virtual-list.zip.

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.

2017-09-02_123654

Demo file: json-array-plus-virtual-list.zip (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.

2017-08-21_093643

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, part 2

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

Demo file: json-plus-virtual-list-part-2.zip

This is a short follow-up to part 1, to demonstrate an alternative idea re: JSON date encoding. As you may recall, JSON does not have a “date” type, and one of our goals is to encode and decode JSON dates in a region-agnostic manner (e.g., encode in a region where MM/DD/YYYY is the default, but then decode in a region where DD/MM/YYYY is the default).

To accomplish this goal, in part 1, we wrapped our FileMaker dates inside a GetAsNumber function when pushing them to JSON so they looked like this:

2017-07-29_180704

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.

2017-07-27_152145

Demo file: json-plus-virtual-list.zip

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

During our recent discussion of SortValues and UniqueValues, I made passing reference to an article I wrote a few years ago on the topic of “pseudo indexing“, i.e., displaying a list of unique values for a specific field for a found set, rather than for all records.

2017-07-04_17-25-35

Well today we’re going to dust the pseudo indexing demo off, and update it to take advantage of the above-mentioned new-in-16 functions. Why bother? Here’s why: the new implementation…

  1. is simpler (has fewer moving parts)
  2. scales better
  3. is less brittle (prone to breakage if schema is renamed)
  4. enables you to bring new fields on board faster
  5. can sort the pseudo index in ascending or descending order

Demo file: FM-16-Pseudo-Indexing.zip (requires FM 16) 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

User-Friendly Excel Exports, part 7

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading

User-Friendly Excel Exports, part 6

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.

Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.

Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.

Demo files:

Continue reading