JSON, Level: Intermediate

A JSON Miscellany

Today we’re going to look at a bug fix in FM 19, and a free web service that returns useful information in JSON format.

Fixed in 19: JSON validation bug

In FM 18 (but not in 16 or 17) JSONFormatElements and other JSON functions can mask mangled JSON errors, making them harder to detect and correct. I wrote about this in detail last year, but, briefly, a JSON bug that was introduced in FM 18 has been corrected in FM 19.

Steps to replicate:

Create some JSON, and then transform it, but intentionally botch the transformation, for example, attempt to update an array using object notation:

2020-07-05_125525 Continue reading “A JSON Miscellany”

ExecuteSQL, Level: Intermediate, SQL, Version: FM 10 or later

GetFieldName Revisited

Long time readers of this blog will be familiar with the following scenario —

An ExecuteSQL expression is given, e.g.,

ExecuteSQL ( "
   SELECT SUM ( net_amount )  
   FROM cc_transactions 
   WHERE batch_date = ? 
      AND batch_region = ? 
      AND card_type = ? 
" ; "" ; "" ; $theDate ; $theRegion ; $theCard )

…followed by a disclaimer along the lines of

For readability, static code has been used… in the real world I would employ robust coding practices to prevent accidental breakage due to field and/or TO renaming.

…with the link pointing to custom functions utilizing a combination of GetFieldName and Quote to ensure that using reserved words or potentially-problematical characters (such as a space or #) in table occurrence or field names, or renaming either of the preceding, will not break your SQL code. Continue reading “GetFieldName Revisited”

ExecuteSQL, Level: Intermediate, SQL, Version: FM 16 or later, Virtual List

2-Column Magic Value Lists, part 2

Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.

Introduction

This is a quick follow-up to last month’s article on 2-Column Magic Value Lists, and, to avoid repetition, will assume the reader is familiar with that material. A colleague pointed out that it might be helpful to offer some real-world applications of this somewhat esoteric technique, and today we have two demos based on last month’s demo #2, but this time actually doing something useful.

2019-11-11_065703.png

Demo Files

Continue reading “2-Column Magic Value Lists, part 2”

Level: Intermediate, Version: FM 18 or later

New in FM 18: While, part 2

Today we’re going to dig a little deeper into the new-in-18 While function, and to avoid repetition, will assume readers are familiar with last month’s article on SetRecursion and While. We’ll look at some new examples ranging from basic to advanced, investigate the circumstances under which While can use variables previously declared via Let, and check out some benchmark results comparing While vs. CustomList.

Demo Files

Continue reading “New in FM 18: While, part 2”

Level: Intermediate, Version: FM 18 or later

New in FM 18: SetRecursion and While

Author’s note: Inevitable comparisons will be drawn between the new While function and the venerable CustomList custom function, which I wrote about at length several years ago. Preliminary testing indicates that CustomList is faster under certain circumstances and While is faster under others. In my opinion, both of them belong in your developer tool kit.

Today we’re going to take a look at two new functions introduced in FileMaker 18: SetRecursion and While, and you can follow along in today’s demo files if you are so inclined.

maclaurin-series.png

Demo files: While-Sandbox.zip and Maclaurin-Series.zip

Continue reading “New in FM 18: SetRecursion and While”

ExecuteSQL, Level: Intermediate, SQL, Virtual List

Bill of Materials

29 Mar 2019: Demo file has been updated to v2. See comments for details.

Editor’s note: Geoff Gerhard of Creative Solutions is one of those “quiet” developers who has been helpfully sharing his knowledge in online FileMaker forums for more than 20 years. I personally have benefited enormously from his expertise, and am pleased to feature his first appearance as a guest author here on FileMaker Hacks.

Demo file: BillOfMaterialsDemo-v2.zip

Continue reading “Bill of Materials”

Level: Intermediate, Version: FM 12 or later

Disabling New Window

Today I want to talk about a potential problem lurking in a small percentage of FileMaker solutions, and discuss various options for mitigating it.

As you may know, there are three ways to display a new window…

  1. Via the New Window script step
  2. Via the Go To Related Record script step
  3. Manually via Window > New Window

…and it’s this last option that can, depending on circumstances, be problematical.

2019-02-23_13-06-51.png

I say “depending on circumstances” because typically it’s okay for a user to spawn a new window, which is to say that doing so doesn’t pose any challenges beyond those the developer already faces when building a multi-user system, which presumably the developer has considered and has intelligently dealt with.

Continue reading “Disabling New Window”

FM Development, JSON, Level: Intermediate, Version: FM 16 or later

Creating and Using Invisible Window IDs

Editor’s note: I first became aware of Paul Jansen when I licensed his FMTools in the late 1990s, and I finally had the pleasure of meeting him last June at dotFMP after 20 years of online and voice communication. It’s an honor and a privilege to welcome him to FileMaker Hacks as a guest author.

FileMaker is pretty flexible. As developers we are given options as to how to reference things; fields, scripts and layouts can be referenced by name or number/id. Whilst we know that referencing things by name is more fragile, it is still a very useful capability to have, but it is definitely more robust to reference by ID…

Windows are a bit of an odd one out; we can only reference them by name.

As windows are probably the most likely things to have their names changed during normal day to day usage of a FileMaker Database it would be really useful if we could keep track of them by an ID independent of the displayed window title. Another benefit of having access to an ID to identify a window is that we then have to option to very easily store and access window specific variables. Kevin has made use of window specific variables based upon the window name in several articles and I suggest that access to a numeric ID that was independent of the window title would be an improvement. Continue reading “Creating and Using Invisible Window IDs”