Level: Beginner, Version: FM 8 or later

Streamlining Scripts, part 1

I’m always mildly astonished when I see a script like the following — and this is the entire script by the way. It’s not that there’s anything horribly wrong with with it, but the architecture annoys me.

The thing I don’t like about the above script is that it is indented more than is actually necessary, negatively impacting readability. Continue reading “Streamlining Scripts, part 1”

Level: Intermediate, Version: FM 10 or later

Avoiding Brittleness

Update 28 April 2014: Make sure to read the illuminating comments following the article, with various suggestions to make your code even less brittle.

The other day I was working with an OnRecordCommit script trigger — let’s call it “Trigger Script” — and, not surprisingly, I wanted this script to run whenever a record in a certain table was committed. Except… well… not exactly always… you see, there was this one other script— let’s call it “Other Script” — which had a Commit Record step right smack in the middle, and in that particular circumstance I definitely did not want Trigger Script to execute.

Luckily, we can include a parameter when a script is triggered, so I added the highlighted script parameter to the OnRecordCommit trigger as follows:

…which evaluates as 0 (zero) when the current script is “Other Script”; otherwise it evaluates as 1. Continue reading “Avoiding Brittleness”

Level: Any, Version: FM 8 or later

Cartesian Join Experiment

Disclaimer: This one falls into the category of just because you can do something doesn’t mean you should. I do not recommend implementing this technique in a real-world solution, in case it should break in some future version of FileMaker.

One thing that bothers me about cartesian join relationships is that they’re implemented at the field level, when from a logical perspective they are actually table-to-table joins (their purpose is to give each table access to all rows in the other table). It seems like we ought to be able to link a table to another table directly, without bothering with fields at all, and it turns out we can… sort of.

7-13-2013 8-09-31 PMTo create a “table-to-table” cartesian join like the one at the left, start by defining a new field in each table… the field type doesn’t matter, because it’s going to have a very short lifespan. Continue reading “Cartesian Join Experiment”

Level: Beginner, Version: FM 8 or later

What’s Right with this Picture?

Note: As of FileMaker 13 this work around is no longer necessary.

There’s something wrong with this picture. Actually, there’s something right that normally wouldn’t be — can you see what it is? (Hint: follow the dotted lines.)

Notice how everything lines up perfectly? Continue reading “What’s Right with this Picture?”

Level: Beginner, Version: FM 8 or later

Plural, Singular, Who Cares?

This morning I was doing some maintenance on an old solution when I ran into this ungrammatical dialog. “I’d like to give a piece of my mind to the lazy slob who wrote that”, I said to myself smugly, followed a few seconds later by, “Uh, whoops, the slob was me.” Normally, I’m pretty good about making sure my dialog text is correct with regard to plural or singular, but in this case I must’ve been feeling particularly lazy (or overly confident that users would never export only a single record).

Continue reading “Plural, Singular, Who Cares?”

Level: Any, Version: FM 8 or later

Portal Sorting, part 3

Today we’re going to look at a couple more approaches to dynamic sorting, from opposite ends of the complexity spectrum. The simple one, portal sorting, circa 2002, is something I built in the FM 5.5 era. It uses a “smoke and mirrors” approach to achieve its objective, and apart from converting to .fp7 format, and consolidating into a single file, I’ve left it as is.

Behind the scenes this file has eight relationships from the parent table to the child; all based on the same keys, but with different sort orders. Continue reading “Portal Sorting, part 3”

Level: Intermediate, Version: FM 10 or later

Portal Sorting, part 2

The other day we looked at static portal sorting, where the developer decides in advance how the portal will sort, and “hard codes” those settings into the portal. Sometimes, though, we want to provide users with an interface where they can dynamically sort a portal by clicking on column headings…

…and we’re going to look at a technique to accomplish this today. Continue reading “Portal Sorting, part 2”

Level: Beginner, Version: FM 8 or later

Portal Sorting, part 1

Experienced FileMaker developers will likely already be familiar with the following portal sorting trick, but I’ve worked with enough developers recently who aren’t (two, to be precise), that I figure it’s worth sharing here.

Problem: how do you sort a portal on a field that doesn’t live in the table the portal is based on? Take, for example, a simple sales database with this structure:

We want to view sales history for a particular product in a portal, like so: Continue reading “Portal Sorting, part 1”

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

Custom Functions for Internal SQL

Recently I’ve mentioned several custom functions that can make your life easier when working with FileMaker’s internal SQL engine (a.k.a. FQL). This article’s accompanying demo file, SQL Sandbox, includes some sample data, an interface for testing SQL code, a number of code examples, and the following four custom functions:

Q – wraps an expression in single quotes. I created this because I hate visually parsing single quotation marks. As I wrote last month,

...first I want digress for a moment to complain about discuss single quotes, a.k.a. apostrophes. They can be difficult to see when they appear adjacent to double quotes — I liken them to an annoying cloud of gnats — so to avoid confusion, I wrote a custom function which I call simply “Q”:

In a nutshell, this CF supplies the surrounding single quotation marks so we don’t have to type them or think about them, and ironically, the above image illustrates the problem perfectly. A single quote surrounded by double quotes is extremely difficult to parse visually. But once you’ve written the CF, you may never have to deal with this problem ever again (until you edit another developer’s SQL code at any rate).

Additionally, this CF escapes any internal single quotes or apostrophes by prepending another one… e.g., O'Malley becomes O''Malley (yes, this is a good thing if you don’t want SQL code to break… don’t worry: the extra apostrophe won’t show up in your data).

GFN – like GetFieldName(), but only returns the field name itself — essential if you want to protect your code from the hazards of field renaming. Note: since this CF uses the GetFieldName function, it requires FM 10 or later.

Guess which one breaks when someone decides to rename creditCardType to ccType, or idCustomer to idContact? On a related note, in addition to protecting you from ex post facto field renaming, GFN also prevents you from misspelling or entering non-existent field names in the first place.

Also, FQL has over 250 reserved words, including “date”, “action” and “group” — you can see the complete set of reserved words on pages 55–57 of the FileMaker 11 OBDC and JDBC Guide. If you’ve used a reserved word as a field name in your FM database, you normally have to remember to wrap that field name in double quotes… GFN does away with all that by automatically wrapping your field name in quotes for you. There’s no harm done if the field name isn’t a reserved word, and that way you don’t need to worry about it.

(I once wasted an hour debugging a SQL statement referencing a field named “group”, before realizing “group” was a reserved word. An hour of my life, gone forever. But it inspired me to add the quote wrapper to GFN, so something good came out of it.)

GFN’s protective quote wrapper will also deliver you from certain other evils, e.g., in FM 11 (with its newly-rewritten SQL parser), the necessity to escape field names if they begin with an underscore, e.g., “_pk_serial”.

DateToSQL – takes a date in a variety of input formats, and outputs it in this format: DATE '2011-02-28', which is apparently the only format that is acceptable to both the new (FM 11) SQL parser, and the SQL parser used in FM 10 and earlier versions.

The input formats are: US-style dates (MM/DD/YYYY), European-style dates (DD/MM/YYYY), and SQL-style dates (YYYY-MM-DD). For example, say you want to grab a list of foreign keys for all customers who have placed an order in the last 30 days…

Assuming today’s date is 26 March 2011, the final line will be rendered as DATE '2011-02-24' and the SQL gods will smile on you. It’s true, they will.

The preceding code examples have consisted of simple SELECT statements; here’s a more complex UPDATE statement, utilizing all three of the CFs we’ve looked at so far. The aim here is to update the tax rate on any unbilled or future-dated invoice line items, if the user changes the default sales tax rate in Settings (this is a single-user system, so I have the luxury of not having to worry about potential record locks by other users).

And finally, we come to a custom function I don’t actually need very often, but that sure comes in handy when I do…

FoundSetToSQL – the concept of a found set does not exist in SQL, but sometimes we need a SQL command to operate only on the records in our current found set. What to do? Well, it turns out that we can say, “Hey, SQL, only process records if the value in a certain field matches one of the values in this list: a,b,c,d,e,…”, and we can build that list from values in the current FileMaker found set. Take for example this found set of 14 records:

FoundSetToSQL ( customers::id ) will return the following…


…as one unbroken string of text. And this can be used by the SQL “IN” operator as part of a WHERE clause, as per this statement, which generates a list of unique cities in the found set.

Warning: FoundSetToSQL is a) not fast with large found sets, and b) subject to the standard 10,000 iteration recursion limit… you don’t want to point it at a found set with more than 10,000 records. There are various ways to work around these limitations, but that is a topic for another day (the second half of this article addresses ways to build a multiline key of values from the current found set).

In closing, I will risk stating the obvious: these custom functions are not the last word on the subject by any means (especially FoundSetToSQL). Feel free modify and extend them as you see fit, and as always, use at your own risk.

FoundSetToSQL ( customers::id )’000940′,’000540′,’001443′,’001545′,’000458′,’000639′,’000794′,’001629′,’000048′,’000973′,’000735′,’001139′,’001224′,’001449′