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

Button Bar Segment Fun

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

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

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

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

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

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

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

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

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”

Level: Intermediate, Version: FM 18 or later

A Value List Experiment

Demo file: a-value-list-experiment.zip

Note 1: today’s demo is recycled from the one that accompanied Virtual Portal, part 2. All we care about this time around is how we assign the parent company; the remainder can be ignored.

Note 2: if you’re unclear on value list basics, check out Thinking About Value Lists, part 1.

“I want it to work like it does in Quickbooks.” Based on a client request I recently implemented a Quickbooks-like picker… shown here enabling the user to easily assign a parent company via auto-complete as the user types the first few letters of the company name.

To be clear, we aren’t actually storing the parent company name, merely using it as a selection mechanism. When a name is chosen via this interface, the corresponding foreign key is immediately auto-entered into “id_parent”… Continue reading “A Value List Experiment”