1. Pages:
  3. 1
  4. 2
  5. 3
  6. 4
  7. 5
  8. 6
  9. 7
  10. 8
  11. 9
  12. ...
  13. 32
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.

Note: where appropriate, some of today’s custom functions utilize bracket notation to avoid unexpected results when and if key names contain wonky characters such as dots, brackets or braces. You can read more about bracket notation in Thinking About JSON, part 4.

And with that said, let’s move on to… 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

JSON.GetValueType

Kevin Frank

Demo Files

Make sure to download the correct version for your locale:

Disclaimer

This is a work-in-progress that I am sharing freely with the developer community. It has been under development for the last month, and I will likely revise it in the future. It may contain bugs or not behave the way you think it should. Use at your own risk, and with a healthy dose of skepticism and common sense. And, needless to say, treat it as a starting point and modify it as you see fit. Continue reading “JSON.GetValueType” →

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

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

Level: Intermediate

Button Bar Segment Fun, part 2

Kevin Frank

This is a quick follow up to Tuesday’s article, and to avoid unnecessary repetition I will assume the reader is familiar with that material.

Demo Files

There were some things in v3 I wasn’t completely happy about, and I realized this morning that they would be easy to fix, and that the act of fixing them would help demonstrate the power of using this approach in the first place. Continue reading “Button Bar Segment Fun, part 2” →

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

ExecuteSQL, Level: Advanced, SQL

SQL Multi-Table Query Optimization

Kevin Frank

Demo file:  sql-multi-table-query-optimization-v2.zip

Recently a colleague requested help w/ a SQL query that was performing slowly. I wrote back:

  1. Make sure there are no records open locally, i.e., on your machine, in the tables you are querying (for more information see this article by Wim Decorte: ExecuteSQL – The Good, The Bad & The Ugly)
  2. Only query stored values — if you’re querying a single table it should be fast
  3. If you’re querying multiple tables, make sure to construct the query to run optimally

Well today we’re going to take a look at #3 and see what we can learn re: optimizing multi-table queries. But first, I should point out that what follows reflects my current understanding of how things work. I hope this can be the start of a conversation where others will chime in and share their knowledge. Continue reading “SQL Multi-Table Query Optimization” →

JSON, Version: FM 18 or later

JSONQuery at FM-DiSC

Kevin Frank

Update 16 Sep 2024: JSONQuery 2.0 is now available.

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:

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

More information re: JSON + FileMaker: