Category: Version: FM 18 or later

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

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

Button Bar Segment Fun

Kevin Frank

Demo Files

Today we’re going to look at some ways single-segment button bars (SSBBs) can help produce dynamic column headings for list views and/or reports, with a goal of concentrating logic into the segment calculation and reducing schema dependencies elsewhere. This is a work in progress, rather than a finished, battle-hardened methodology. The aim is to explore possibilities and stimulate discussion.

Note: the demo files are built on top of an “empty” virtual list table. The point is not to (once again) dive into virtual list or clickable/sortable column headings, but to provide a list view we can pretend contains valid entries, while we focus on what’s going on in the layout header part.

Disclaimer: these techniques are in the proof-of-concept stage. As with all techniques on this (and any other) site, use with a healthy dose of common sense and at your own risk.

Continue reading “Button Bar Segment Fun” →

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

Exploring Wordlespace with SQL and While

Kevin Frank

Recently we’ve discussed optimizing SQL queries in FileMaker, and had some fun with various SQL experiments. Today we’re going to explore some ways FileMaker can use ExecuteSQL and the While function to perform letter frequency and text pattern analysis on candidate words for the popular Wordle game.

The list of words comes from https://github.com/tabatkins/wordle-list and purports to include the actual answer words, as well as all allowable guess words. I don’t know how valid this list of words actually is, or, assuming it is currently valid, whether it is carved in stone or will change at some point in the future.

Demo file: SQL-Multi-Table-Experimentation-Wordle.zip

Some Notes

  • Today’s file is functionally identical to the one from last time; if you already have it, there’s no need to download this one.
  • No attempt is made to differentiate between daily Wordle words that have already appeared vs. those that have yet to appear.
  • SQL is case-sensitive in the WHERE clause; all our examples today use lower case letters so we may safely ignore the issue for the duration of this article.
  • For a general-purpose introduction to SQL in FileMaker, see Beverly Voth’s Missing FM 12 ExecuteSQL Reference.

Continue reading “Exploring Wordlespace with SQL and While” →

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

SQL Multi-Table & Miscellaneous Experimentation

Kevin Frank

INTRO

Today we’re going to pick up where we left off last month, and today’s article will assume the reader is familiar with the material we covered last time (in SQL Multi-Table Query Optimization).

This time we’re going to dig a little deeper into multi-table SQL queries, conduct some SQL experiments, and look at a way the While function can help speed up and/or extend the capabilities of an ExecuteSQL query.

Demo file: SQL-Multi-Table-Experimentation.zip

Continue reading “SQL Multi-Table & Miscellaneous Experimentation” →

JSON, Version: FM 18 or later

JSONQuery at FM-DiSC

Kevin Frank

When / Who / Where

On Friday, January 14, Steve Senft-Herrera and I presented JSONQuery at FM-DiSC (FileMaker Developers in Southern California).

Useful Links

Recording of the presentation: https://www.youtube.com/watch?v=dztdZrHdrUQ

Current version of JSONQuery:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

Our recent two part in-depth interview series:

Coming soon: Steve Senft-Herrera’s demo file from the presentation

Kevin Frank’s demo file from the presentation: jsonquery-sandbox.zip

More information re: JSON + FileMaker:

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

JSONQuery, part 2

Kevin Frank

Continuation of interview with Steve Senft-Herrera

[Editor’s note: the demo file and custom function have been significantly updated since part 1.]

Demo file:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

KF: Welcome back Steve for part 2 of our JSONQuery conversation.

SSH: Thank you, Kevin.

KF: One thing we didn’t mention last time, because they were late-breaking additions, were the inequality operators.

Continue reading “JSONQuery, part 2” →

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

JSONQuery, part 1

Kevin Frank

Interview with Steve Senft-Herrera

[30 Nov 2021: Custom function and demo file have been updated. Some of the screen shots and example numbers referenced here in part 1 will deviate slightly from what you will find in the updated demo.]

Demo file:  CF_JSONQuery_20211130_0120_PUBLIC.fmp12.zip

KF: Good afternoon, Steve. You’ve been developing JSONQuery over the last few years, and today I have the honor of presenting and discussing it here with you on FileMaker Hacks. I was wondering if you could start out with a brief description of what JSONQuery is?

SSH: Sure. JSONQuery is a custom function, and it operates on JSON. Typically you’re going to be feeding it a large JSON array you’ve received back from the FileMaker Data API, or somebody else’s API, where each record is a JSON object within a larger parent JSON array, and the purpose of this function is to be able to find child elements in that parent array that match certain criteria and return just those elements to you. 

Above and beyond that it has a lot of bells and whistles, some of which I’m sure we’ll cover, but the main impetus for writing it was giving you an easy and fast way to essentially query a JSON array.

For example, let’s say you have an array filled with a lot of orders, but you only need to get the order items that are being shipped to a certain city, or to a certain state, then this function would allow you to easily obtain those elements in an efficient manner. Continue reading “JSONQuery, part 1” →

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

Virtual List Reporting, part 4

Kevin Frank

Introduction

Back in 2017 I wrote about a technique to enable users to a) produce multiple on-screen reports, and b) interact with those reports in browse mode. The article was called Virtual List Reporting, part 3, and while the approach it advocated works well enough under most circumstances, today I’d like to share some fresh ideas.

Note: as you might guess from the title of this article, implementing this technique in your solution, and/or understanding what’s going on under the hood, requires some knowledge of virtual list. If you are not familiar with virtual list, or need a refresher, you may find this article helpful: Virtual List Simplified.

Demo Files (require FM 18 or later)

Continue reading “Virtual List Reporting, part 4” →

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

Fun with SQL Joins and Currency Formatting

Kevin Frank

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

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

File IDs, Table IDs and Field-Level Schema

Kevin Frank

21 Jan 2021: demo has been updated to fix a couple bugs; article has been updated to reflect these changes.

Demo file:  file ids, table ids and field schema, v4.zip (requires FM 18 or later)

INTRODUCTION

Recently I was asked to help document a FileMaker solution by creating and populating a “schema” table as per the above screen shot.

One of the requirements was to programmatically determine internal ID numbers for the file and for the tables (as opposed to table occurrences) within that file, and this was accomplished by leveraging some script steps that were introduced in FileMaker 18.

(Thank you Rob Russell for pointing me in the right direction, i.e., to Save A Copy As XML.)

RUNNING THE DEMO

Clicking this button…

Continue reading “File IDs, Table IDs and Field-Level Schema” →