Author: Kevin Frank

JSON, Level: Intermediate

Generating Spreadsheets with LibXL

Kevin Frank

Introduction

From time to time FileMaker developers are tasked with generating Excel spreadsheet output. There are a variety of ways to accomplish this, in some cases via native FileMaker commands (Export, Save/Send Records), and in other cases using various workaround methods — some of which have appeared on this site in years past.

Today we’re going to look at producing spreadsheet output via LibXL, with help from a plug-in. Why bother? Several benefits immediately come to mind, including… power and flexibility, ease of implementation, and not having to add helper fields, tables, etc., to your database schema. With a plug-in you will typically do all the work at the script level, and generate feature-rich spreadsheets that otherwise would be difficult or impossible to produce from within FileMaker.

Demo file: Generating-Spreadsheets-with-LibXL.zip
(requires FM 19.5 or later)

Goya FMXL plug-in: https://goya.com.au/excel-plugin-for-filemaker

This plug-in is required for the demo to function. If unregistered it will add a trial notice banner at the top of the spreadsheet.

Note: if you are a Monkeybread user, MBS also offers an add-on plug-in for LibXL. The function calls are similar in both, and if you’re so inclined today’s demo file can be adapted to work with the MBS version. See documentation here, which can be helpful when using the Goya plug-in as well.

Continue reading “Generating Spreadsheets with LibXL” →

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

JSON Custom Functions for FM, part 3

Kevin Frank

This article is part of a series. See also…
•  JSON Custom Functions for FM 19.5, part 1
•  JSON Custom Functions for FM 19.5, part 2

Demo file: json-custom-functions-part-3.zip

Note: some of these CFs make use of JSONGetElementType so require FM 19.5 or later.

Disclaimer: use at your own risk, these CFs may contain bugs, these CFs may not be performant when processing large amounts of data, always test carefully before deployment, etc., etc., etc.

Continue reading “JSON Custom Functions for FM, part 3” →

Level: Intermediate, Version: FM 13 or later

A Fresh Approach to Deduplication

Kevin Frank

Editor’s Note: Today I’m pleased to present a guest article by Jon Rosen featuring a creative and performant approach to removing duplicate records.

Recently, I had a situation where I had a found set of more than 500,000 records, but over ⅔ of them were duplicates. To remove the dupes, I initially went with the time-honored method I’ve been using for the last twenty years. I’ve been using it so long, that the original version used global fields because script variables hadn’t been created, yet. But now it seemed to run glacially slow on this large file.

After giving it some thought, I came up with a new method of deleting duplicates that turned out to be simpler and many times faster than the older method. So, let’s start with a review of the original method. There’s a good chance you may be using it yourself.

Eliminate Duplicates (Old Method)

Continue reading “A Fresh Approach to Deduplication” →

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

JSON – Force Standard Notation

Kevin Frank

Introduction

This is a follow up to a behavior I mentioned last month where FileMaker’s JSON functions can transform a number into scientific notation when you might prefer to have that number remain in standard notation.

Demo file: json-force-standard-notation.zip

Note: demo file uses JSONGetElementType so requires FM 19.5 or later.

The overall functionality of the demo was covered last time. This time we’re going to focus on working around the behavior, followed by some related observations… but first let’s review. Continue reading “JSON – Force Standard Notation” →

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

JSON Currency Exchange Rates revisited

Kevin Frank

Demo file: json-rates-via-api-exchangerate-host.zip

Today we’re going to take a fresh look at pulling currency exchange rates into FileMaker, and this article is directly based on its predecessor. Why the re-visitation? Two reasons actually:

  1. The “free” endpoints I relied on back in 2020 (and in 2021 when I revised the original demo) have been monetized, and apart from any cost considerations, I want this demo to work out of the box without requiring an API token.
  2.  The check box set I used for symbol selection last time was designed to accommodate a fixed and relatively small number of entries. That was a short-sighted decision that could not possibly scale gracefully. This time around I’m using an approach that will automatically accommodate any number of symbols.

Continue reading “JSON Currency Exchange Rates revisited” →

Level: Intermediate, Version: FM 19.5 or later, Virtual List

A Summary Field Bug and Workaround

Kevin Frank

Introduction

Recently a client asked me to implement a virtual list reporting framework similar to the one I wrote about a few years ago in Virtual List Simplified. I added the framework to the client’s hosted file, and things went smoothly until I ran a report similar to the one shown below… and observed that the repeating summary field at the bottom wasn’t rendering. The same report worked flawlessly a) offline, and b) when hosted on FMS 19.4 and earlier, but when hosted via FMS 19.5 or 19.6, the summary repeater was malfunctioning.

Continue reading “A Summary Field Bug and Workaround” →

JSON, Level: Advanced, Version: FM 19.5 or later

JSON Custom Functions for FM 19.5, part 2

Kevin Frank

This article is part of a series. See also…
• JSON Custom Functions for FM 19.5, part 1
• JSON Custom Functions for FM, part 3

Demo Files

Note: some of the CFs have been revised and/or renamed since part 1, so if you plan to use these CFs, make sure to download today’s “part 2” file. Continue reading “JSON Custom Functions for FM 19.5, part 2” →

JSON, Level: Advanced, Version: FM 19.5 or later

JSON Custom Functions for FM 19.5, part 1

Kevin Frank

This article is part of a series. See also…
• JSON Custom Functions for FM 19.5, part 2
• JSON Custom Functions for FM, part 3

Demo File

Introduction

Today we have some custom functions (CFs) that can help you accomplish various JSON-related tasks in FileMaker. Back in 2018 I had this to say about JSON custom functions…

My inclination is to really understand something before I use a custom function to simplify things, but that’s a matter of personal choice… and one which can vary depending on the situation.

And four years later I find myself using JSON custom functions on a daily basis, to save time and to boost productivity — for example, to merge two objects into a single object, or to deduplicate an array. Continue reading “JSON Custom Functions for FM 19.5, part 1” →

Level: Intermediate

Sometimes Less Is More Reliable

Kevin Frank

Update: Make sure to read Tony White’s comment below. It contains important additional information re: the behavior described in this article.

Recently I noticed some code that had worked flawlessly for years was suddenly returning “?” instead of valid values. What it came down to was that I had renamed my file from “JSON Custom Functions” to “JSON Custom Functions for FM 19.5”

Well you know those functions like ValueListItems…

ValueListItems ( fileName ; valueListName )

…that have a fileName argument? The culprit turned out to be that I was using Get(FileName) for that argument, and I needed to instead use "".

All 21 of the design functions are vulnerable to this, ahem, behavior. From the online help —

  • If you specify a filename that contains a period, include the filename extension in the parameter. Otherwise, functions may interpret the period in the filename as the beginning of the filename extension, which can lead to unexpected results.
  • If you specify no filename (""), functions return results for the current file.

Source:  https://help.claris.com/en/pro-help/content/design-functions.html

Bottom line:  When using a design function and referencing the current file, use "" instead of Get(FileName). There’s no down side, and your code won’t break if you decide to add a period to the file name.

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

Thinking About JSON, part 4

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 3

Demo Files

Make sure to download the correct version for your locale:

Introduction

Welcome back to the fourth installment in this ongoing series. It’s hard to believe it’s been four years since the last segment, but in the meantime some exciting developments have occurred at the intersection of FileMaker and JSON, including… Continue reading “Thinking About JSON, part 4” →