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) .

ABOUT THE DEMO

This is a stripped down simplification of a wine shop solution, architected like so…

…with Invoices dated from Jan 1 through Apr 30, 2021…

…and mini-reports summarizing sales by US state, accessible via the Scripts menu:

A NOTE REGARDING SQL JOINS

Depending on a variety of factors, including system architecture, # of records, speed of network connection, etc., SQL joins in FileMaker can sometimes take an unacceptably long time to resolve. I recommend testing carefully under real-world conditions before deploying any of the following. To be clear, I do make use of SQL joins in some of my solutions, but only after verifying they are a good fit.

Script 1: Inner Join

Common to all the scripts is an invitation (requirement, actually) to enter a date range… if you use M/D/Y style dates, the dialog will look like this…

…otherwise the dialog will look like this (D/M/Y):

(Because this demo was created in the U.S., screen shots in this article are in M/D/Y format.)

Since we’re aggregating data from two tables, we will use a SQL inner join, which is the SQL equivalent of a FileMaker equijoin. Note: because inner joins are the most common type of SQL join, there is no need to specify the “inner”.

And here is the result.

In the interest of readability, table occurrence names are spelled out in today’s code example screen shots, but each script also includes a commented out “terse” version of the code as well, where aliases are designated to stand in for table occurrence names, e.g.,

Script 2: Join Logic in Where Clause

This is identical to script 1, except instead of using the JOIN operator, we have moved that logic into the WHERE clause. I find this to be more readable than the code in script 1, but have not done any performance benchmarking to determine whether there is a down side.

Script 3: Formatted Currency

As mentioned at the outset, FileMaker’s SQL implementation does not support the standard method of formatting numbers via the CAST operator, and in scripts 1 and 2 we made no attempt to format the numbers as U.S. currency, i.e., with precision set to two decimal places…

…or commas as thousands separators. In script 3, however these shortcomings have been addressed…

…with help from the While function.

27 Feb 2021: see Paul Jansen’s comment below for a more elegant way to accomplish this.

Note: the above currency formatting trick is utilized in all subsequent scripts, but, to avoid redundancy, will be omitted from (most of) the remaining code examples.

Script 4: Invoice Promo box is Checked

Did you notice the “Promo” check box at the bottom left of the invoice layout? In the demo this box has been checked for an arbitrary number of invoices.

The underlying field lives in a “shadow” table, which is related to invoices via a 1-to-1 (or zero-to-1) relationship.

When the box is checked, records are created in the shadow table, and inv_shadow::flag_promo is set to 1. (If, on a given invoice, the box is subsequently unchecked, the shadow record will still exist, but flag_promo will now be empty.)

Once again, the logic is all loaded into the WHERE clause…

…and records meeting the criteria are aggregated into the result.

(At the risk of stating the obvious, the number of entries will increase as you widen the date range.)

Script 5: Invoice Promo box is not Checked

This one is trickier, because there are two ways the box might not be checked… a) for a given invoice the related shadow record does not exist, i.e., the “Promo” box was never checked, or b) the related shadow record exists, but flag_promo is empty, because at some point the “Promo” box was checked on the parent invoice, but then was subsequently unchecked. Here, to ensure that scenarios “a” and “b” are both accommodated, we use a LEFT JOIN and specify that flag_promo IS NULL (as opposed to “flag_promo <> 1”, which will not yield correct results).

Script 6: All States, with or without Sales

In this example we want to list all states regardless of whether they had any sales during the specified date range.

Once again, we will employ a LEFT JOIN, but this time there is no WHERE clause and all the criteria are specified in the JOIN clause. Doing so achieves the desired effect, but with a noticeable performance hit.

Script 7: All States, with 0.00 in place of Null

Here we want to see zeros rather than nulls when there are no sales…

…and this is accomplished by making a tiny change to script 6.

Script 8: Like #7, but much faster

Here the logic has been re-worked to eliminate the LEFT JOIN, resulting in a massive performance boost.

Explanation:

1. Build a list of state abbreviations and sort them alphabetically.
2. Walk the list of states and calculate the corresponding sales total on a per state basis.

CLOSING THOUGHTS

The While function is a great way to extend the power and functionality of ExecuteSQL. If you are looking for a general introduction to the While function or some ideas re: what you might be able to do with it, see…

• New in FM 18: SetRecursion and While
• New in FM 18: While, part 2

Finally, I’d like to briefly touch on a difference between SQL joins and FileMaker relationships. FileMaker is perfectly happy to allow you to create relationships between fields of disparate type, for example, from a text field to a number field. SQL is not so forgiving, for example, in an early version of today’s demo, I had defined inv_shadow::id_invoice as a text field, whereas the corresponding parent field, invoice::id, was defined as a number field. When I attempted to join the two fields via SQL, I got this error message:

For simplicity’s sake, I changed the field type of inv_shadow::id_invoice from text to number, but instead I could have used the CAST function to bring the two field types into alignment like so:

And I think that’s enough for today.