Tips ’n’ Tricks, part 2

Kevin Frank

8 years ago

Welcome back for a second dip into the developer bag of tips and tricks. A few of today’s entries assume familiarity with part 1, so if you haven’t already checked it out, I invite you to do so before proceeding.

We begin today with a couple ExecuteSQL tips.

Tip #1: Get to know COALESCE

The SQL Coalesce function takes a series of arguments and returns the first non-null result, like so:

COALESCE ( arg1 ; arg2 [; arg3 ; etc.] )

For example, given a table of customer data including these records…

…you’ve been asked to produce a list of customer name | address | city | state | zip from Bel Air, Maryland, with the Company name if there is one, otherwise the First and Last name… in text format, i.e.,

Diana Reeves | 336 S Main St | Bel Air | MD | 21014
I O Interconnect | 214 Fulford Ave | Bel Air | MD | 21014
Production Specialties | 418 S Main St | Bel Air | MD | 21014
Felipe Sanchez | 26 S Main St | Bel Air | MD | 21014

Of course there are various ways you could accomplish this via a non-SQL FileMaker approach, but for ease of implementation, I would assert that none of them can rival this:

ExecuteSQL ( "

SELECT COALESCE ( Company, FirstName+' '+LastName ), 
   Address, City, State, Zip
FROM Customers 
WHERE City = ?  
   AND State = ?

" ; " | " ; "" ; "Bel Air"; "MD" )

Tip #2: FM/SQL dates revisited

As you may recall from part 1, dates are returned by SELECT statements in this format…

YYYY-MM-DD

…which is fine if you’re going to continue working with the date in the SQL realm, but is gibberish as far as FileMaker is concerned. My recommendation was to transform the date into Japanese date format (YYYY+MM+DD) via simple substitution, but in the comments at the end of the article, a gentleman named Radu-Dan Sabau points out that you can instead use Coalesce to coerce the date into a FileMaker-friendly format.

For example, given a table called Ledger which includes the following records…

…this statement…

ExecuteSQL ( "

SELECT \"date\"  
FROM Ledger 
WHERE idStudent = ?

" ; "" ; "" ; "S00177" )

…returns this:

However, if you instead construct your query like so…

ExecuteSQL ( "

SELECT Coalesce ( \"date\", '' )   
FROM Ledger 
WHERE idStudent = ?

" ; "" ; "" ; "S00177" )

…your results will look like this:

Note 1: The second argument in the Coalesce clause is a pair of single quotes

Note 2: since “date” is a reserved SQL word, it must be escaped, hence the backslashes and double quotation marks. (If you’d rather not have to worry about whether a field name might need to be escaped, or about hard-coded field name brittleness, see my article on ExecuteSQL: Robust Coding from a few years back.)

Next up is a pair of tips I picked up from Mikhail Edoshin on the FMP Experts list.

Tip #3: use the NOT operator to simplify logic

Ever wish the Hide Object feature were a Show Object feature instead? Here’s a simple example: let’s say you have a layout object that should only appear on a particular layout when…

class::Name = “Kindergarten”
or
class::Size < 20

Now there’s nothing stopping you from applying this Hide Object calc to the object…

class::Name <> "Kindergarten" and class::Size >= 20

…but it means that, having first identified the real world problem you wished to solve, you must now come up with a definition that is (conceptually) exactly the opposite. This can be good as a logical thinking exercise, but isn’t it easier to define your Hide Object calc like so?

Not ( class::Name = "Kindergarten" or class::Size < 20 )

Of course when the conditions are simple, it may not make much of a difference, but as conditions become more complex, this approach becomes more compelling.

Tip #4: Use “” in place of Get ( FileName )

Many of the design functions expect a file name as their first argument, and developers typically use Get ( FileName ) to specify the current file, rather than hard-coding the file name. This is of course a very good practice, but you can simply use an empty pair of double quotes instead.

E.g., for the ValueListItems function…

…given this value list…

…either of the following will return identical results:

Tip #5: Addendum to the preceding

Incidentally, don’t assume the empty double quote trick will work the same way when the layout name is an argument, although it may initially appear to do so. E.g., the FieldNames and FieldIDs functions both take two arguments:

fileName
layoutName

…and using a pair of double quotes in place of Get ( FileName ) works just fine, but if you do the same in place of Get ( LayoutName ), you will get back a list for all the fields in the first table (or eldest surviving table) defined for the file… in other words, something that may superficially appear to be correct, but in reality most likely is not, unless you happen to be sitting on a layout based on that particular table.

For other design functions with a layoutName argument, i.e.,

FieldBounds
FieldRepetitions
FieldStyle
LayoutObjectNames

…using a pair of double quotes in place of Get ( LayoutName ) will prevent the function from returning anything at all.

Tip #6: Further addendum to the preceding

Finally, before leaving this topic, I would be remiss if I did not point out that for the FieldNames and FieldIDs functions, if you…

provide a table occurrence name as a layoutName argument
and
the file contains no layout with that exact name

…the function will reference all the fields in the underlying table, rather than just those on a particular layout. For example, given a solution with a table occurrence named Settings, but with no layout named Settings, this returns all field names in the table:

But if you create a layout named Settings and place one or more fields on the layout, then of course the layout will take precedence.

Tip #7: Retrofit FM 14 check marks into FM 12 or 13 solutions

Stuck in FM 12 or 13 but envious of FM 14’s check mark option for check boxes? Open the file in FM 14, and configure the check boxes to display check marks…

…and, voila, those check marks are available in FM 12…

…and 13.

Tip #8: Duplicating vs. Copying an Import Records script step

Apart from the obvious fact that copying a script step overwrites the contents of your computer’s clipboard, is there any reason to prefer duplicating an existing Import Records script step over copying and pasting it?

Short answer: duplicating is more accurate than copying/pasting. To see what I mean, let’s take a closer look at the original step on line 2:

Note that “matching names” has been specified. Now let’s take a look at the duplicated version on line 5.

As expected it is identical to the version on line 2. But what about the step on line 8 (which was copied and pasted from line 2)?

What the heck? The “matching names” did not survive the transition, and here’s why: when you paste a script step, what you’re really pasting (behind the scenes) is a bunch of XML code, and the XML representation of Import Records does not include the “arrange by” setting. Basically, any time you paste an Import Records step, you will get “last order”.

However, when you duplicate a script step, FileMaker utilizes a different internal mechanism, and the “arrange by” information will be preserved.

On a related note, here’s a portion of the original Import Records step as it appears if you generate a DDR (database design report) using FileMaker Pro Advanced. The source fields are represented in order of creation (not by name), and no “arrange by” information appears.

Tip #9: Find/Constrain/Extend via context menu

As you probably know, you can right-click in a field and choose either “Find Matching Records” or the constrain or extend equivalent from the context menu that appears…

…and you will locate records that match the contents of the field you clicked in.

But did you know that you can perform the same operation on selected text? I only figured this out recently (and accidentally), so perhaps this will be news to some of you as well. For example, if I select the word “Class” and choose Extend Found Set…

…this is the result:

Tip #10: Trap for missing layouts

[Note: for reasons that should be obvious, if you decide to test this for yourself, only do so on a backup copy.]

Let’s say you’ve got a scripted housekeeping routine that looks like this:

What happens if somehow the “session” layout is deleted?

Since FileMaker cannot go to the missing layout, it remains on the current layout
No error dialog is displayed (regardless of “set error capture” status)
All records in the active table are deleted

Recommendation: make sure it’s safe before proceeding. Error trapping routines can be very sophisticated, and are beyond the scope of this article, but at a bare minimum you could do this.

Incidentally, the error code for “missing layout” is 105. You can view a complete list of FileMaker error codes here: Error Code Reference Guide

Tip #11: The “?” character evaluates to Boolean true

Okay, so why is this a tip? Because from time to time I see code that looks like this:

If [ $theBooleanVar ]
   Delete Record [no dialog]
End If

And I suppose that’s fine if you can guarantee $theBooleanVar will never contain a “?”, but is it really so much extra work to code your If statement like this instead?

If [ $theBooleanVar = 1 ]
   Delete Record [no dialog]
End If

Proof that “?” evaluates to Boolean true:

Two ways you can accidentally end up with a “?” when you didn’t expect one:

A broken custom function, or one that exceeds its recursion limit
A malformed ExecuteSQL statement

And I think that’s about enough for today.