1. Pages:
  2. 1
  3. 2
  4. 3
  5. 4
  6. 5
  7. 6
  8. 7
  9. ...
  10. 28
JSON, Level: Advanced, Level: Intermediate, Version: FM 16 or later

FastRange Custom Function

Kevin Frank

Recently I needed to produce a large range of consecutive numbers. FileMaker doesn’t provide a built-in function for this but it’s easy enough to accomplish, for example, using the While function or a recursive custom function. In this case, I decided to go a different route based on a tip I’d seen someone post years ago (I’ve forgotten whom so cannot give proper credit) that you can leverage a couple of FileMaker’s JSON functions to facilitate this task, with the advantages being blazingly fast performance and an opportunity to think outside the box.

Demo file:  fastrange-cf-v1.04.zip
Continue reading “FastRange Custom Function” →

Level: Intermediate

Replace + GetNthRecord to Fill Down

Kevin Frank

This afternoon I pulled this data into FileMaker from a spreadsheet, and needed to “fill down” the region names in f2.

Of course I could have filled the region down in Excel prior to importing, but here I was already in FM. At first I was going to use a looping script, but then it occurred to me that it would be nice if Replace could do the trick. Continue reading “Replace + GetNthRecord to Fill Down” →

Level: Intermediate, Version: FM 20.3 or later

January 2024 FM-DiSC Presentation

Kevin Frank

Here are resources re: my January 12th FM-DiSC presentation.

Layout Calculations

Generating Spreadsheets with LibXL

YouTube recording of meeting

Level: Intermediate, Version: FM 20.3 or later

Layout Calculations in FileMaker 20.3

Kevin Frank

Update 12 Dec 2023: this article has been updated as per my exchanges with Fabrice Nordmann in the comments section below. Thank you Fabrice for the reality check.

This is a follow up to last month’s article, Layout Calculations in FileMaker 20.2, and will assume the reader is familiar with that material. Today we will be taking a look at some changes to Layout Calculation behavior in the newly-released FileMaker 20.3.

#1. Calculation Result Type Can Now Be Specified

We can now specify the result type for a layout calculation…

…whereas in FM 20.2, the result type could only be text.

Initially I was under the impression that this would yield benefits when applying, for example, date specific formatting options, but as Fabrice points out, the formatting behavior is identical regardless of what you do with the result type. Continue reading “Layout Calculations in FileMaker 20.3” →

Level: Intermediate, Version: FM 20.2 or later

Layout Calculations in FileMaker 20.2

Kevin Frank

The recent release of FileMaker 20.2 introduces a feature that developers have long been asking for: the ability to define calculations at the layout level without resorting to workarounds (such as those we saw in Button Bar Segment Fun, part 1 and part 2.)

You can read the official word(s) from Claris here…

…and today I just want to share a few observations. First, in case you haven’t yet gotten your feet wet with layout calculations:

  1. Make sure you’re using FileMaker Pro 20.2 or later
  2. Go into layout mode
  3. Choose “Layout Calculation…” from the Insert menu
  4. The Specify Calculation dialog will appear
  5. Enter the syntax for your calculation, e.g.,
    "Hello " & Get ( UserName )
  6. Click OK

You’ll see something like this in layout mode…

…and this in browse mode (if your user name happens to be Kevin).

Okay… let’s go back into layout mode for some experimentation. If you control-click on the layout calc block you can access the Specify Calculation dialog…

…but, if you prefer, you can also edit the layout calc directly on the layout.

Continue reading “Layout Calculations in FileMaker 20.2” →

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

JSON Currency Exchange Rates, v3

Kevin Frank

Demo file: json-currencyapi.com.v2.zip

This is a quick follow up to a pair of articles I published earlier this year, and will assume the reader is familiar with that material.

Having first blogged about this back in 2020, I had hoped to be finished with this topic, not because it isn’t interesting (it is) or because getting comfortable working with APIs isn’t fun, educational, and potentially profitable (ditto), but because I didn’t really have much left to say on the subject. Unfortunately, for the third or fourth time now, the API my demo was based on stopped working after the service provider was purchased by another company with the aim of monetizing the formerly-free service. Continue reading “JSON Currency Exchange Rates, v3” →

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

Update 30 September 2023: Free end point no longer available for this service. See JSON Currency Exchange Rates, v3 for a revised, functioning demo.

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