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

JSON – Force Standard Notation

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 (recycling some screen shots and text from last month’s article). Continue reading “JSON – Force Standard Notation”

ExecuteSQL, Level: Advanced, SQL, Version: FM 18 or later

Fun with SQL Joins and Currency Formatting

Demo file: sql-join-fun-etc-v2.zip requires FM 18 or later.

[26 Feb 2022: demo file has been updated to v2 to address the concern raised by Phil McGeehan in the comments section. Screen shots have not been updated.]

Recently I was asked to create a mini-report combining data from a pair of related tables via ExecuteSQL.

This provided an opportunity to think about SQL joins, and also to come up with a way to apply currency formatting to dollar amounts in the SQL query result, given that FileMaker’s SQL implementation does not support the standard SQL way of accomplishing this (e.g., CAST AS NUMERIC or CAST AS DECIMAL) . Continue reading “Fun with SQL Joins and Currency Formatting”

Level: Any, Version: FM 16 or later

FilterValues, part 2

Sometimes it happens that the real value of a blog posting emerges in the comments section. Such was the case last time, when Paul Jansen posted an elegant little workaround to remedy a performance issue with the FilterValues function. Paul’s workaround is deserving of its own demo, and that’s what we’re going to look at today.

Demo file: filtering-values-v2.zip

2020-06-02_230953

Continue reading “FilterValues, part 2”