Category: SQL

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

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

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

GetFieldName Revisited

Kevin Frank

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

Kevin Frank

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

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

2-Column Magic Value Lists

Kevin Frank

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

Acknowledgements: today’s article would not have been written without the inspiration provided by Bruce Robertson, John Ahn (FM 12 ExecuteSQL Unconference Session), Andries Heylen (Magic Value Lists), Marcelo Piñeyro (How to Extend FileMaker Value List Sort Capabilities) and Jonathan Fletcher (It’s Sorta A Value List Thing).

Introduction

Last year, in Custom Field-Based Value Lists, we looked at a technique to enable single-column field-based value lists to display their contents in entry order, as opposed to standard index-based alphanumeric order. For example using the contents of this field (from a single-record table) as the basis for a value list…

2019-10-13_135535.png

…we came up with a way to sort our value list (a.k.a. “VL”) items like so…

2019-10-13_135628

…as opposed to what you would get with a standard field-based VL, i.e., this:

2019-10-13_135646.png

I ended the article by saying…

Today’s article looked at value lists based on return-separated values in a single field. In an upcoming article we’ll explore implementing a similar technique for sorted two-column value lists based on entries in a dedicated value lists table where each list item lives in its own record.

…and now, nineteen months later, am ready to pick up where we left off last time, and to avoid repetition, will assume the reader is at least somewhat familiar with the issues and techniques covered in that article (Custom Field-Based Value Lists).

Note: if you aren’t clear on why a 2-column value list might be preferable to its single-column counterpart (or to a custom value list for that matter), check out Thinking About Value Lists, part 1.

Demo Files

Continue reading “2-Column Magic Value Lists” →

ExecuteSQL, Level: Intermediate, SQL, Virtual List

Bill of Materials

Kevin Frank

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

ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

Custom Field-Based Value Lists

Kevin Frank

Introduction

Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them? 

2018-03-09_14-44-10
The “problem”: field-based value lists sort alphanumerically

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”

2018-03-23_120634
From demo #4: field-based value lists mirroring the order of their underlying source fields

Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.

Continue reading “Custom Field-Based Value Lists” →