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:
2015-10-06 2015-10-13 2015-11-05 2015-11-10 2015-12-01
However, if you instead construct your query like so…
ExecuteSQL ( " SELECT Coalesce ( \"date\", '' ) FROM Ledger WHERE idStudent = ? " ; "" ; "" ; "S00177" )
…your results will look like this:
10/6/2015 10/13/2015 11/5/2015 11/10/2015 12/1/2015
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.
Love these tips…thanks for sharing Kevin!
For a slightly simpler option for #2, consider:
'' || \"date\"
Concatenating an empty string with a date will change the format of the date to mm/dd/yyyy.
Nice! Thanks Greg.
Kevin
That’s a tip I learned from Greg at a DevCon a couple of years ago, and used frequently ever since…
Is the checkmarks example on a stand-alone file opened subsequently in previous versions??
Re: the check marks tip (#7), the file can be stand-alone or hosted. You don’t need to take the file offline to retrofit the check marks.
Re: tip #4, using blank quotes for file name, I only found out about that this past year. Definitely a facepalm moment.
Re: tip #3, simplifying logic, I favor the concept, like toggling a Boolean field with (not self). But when there are two or more clauses, I find a good ol’ Case statement generally easier to understand and maintain, e.g.:
Case(
class::Name = “Kindergarten” ; 0 ;
class::Size < 20 ; 0 ;
1 ) // 0=show, 1=hide
I like your approach, Tom. Not only is it clear, it also takes advantage of calculation short-circuiting. [For the benefit of anyone reading this who may not know about short-circuiting, for performance reasons the calculation engine stops evaluating as soon as it encounters a true condition.]
I also like your “key” at the end, reminding that 0 = show and 1 = hide.
Lastly, on tip #10 error trapping:
Like one who orders a kale salad, those who script a Delete All with no error trapping deserve what they get.
Great tip collection, Kevin!
“Studere argutiis, fons verae virtutis”
(Latin phrase I just made up with help from Google Translate)
Thanks, Kevin.
I have posted a Japanese translation at http://notonlyfilemaker.com/2015/12/tips-n-tricks-part2/ .
Kevin, thanks for sharing
A quick comment on the Get(FileName) function
(For Italian lovers see the original here: http://www.filemakerguru.it/guru-in-pillole/filemaker-quando-lestensione-fa-differenza/)
I run into a problem when using the Get(FileName) function: if the file name contains a dot you need to add the file extension to the formula, else it will fail
So:
File named iFatturaPA_2.1.fmp12:
LayoutNames(Get(FileName)) => fails
LayoutNames(Get(FileName) & “.fmp12″) => works
File named iFatturaPA.fmp12:
LayoutNames(Get(FileName)) => works
LayoutNames(Get(FileName) & “.fmp12″) => works
Final solution is the one you mention, the double quotes:
File named iFatturaPA_2.1.fmp12
LayoutNames(Get(FileName)) => fails
LayoutNames(Get(FileName) & “.fmp12″) => works
LayoutNames(“”) => works
All the best, happy 2016
I just tested in FM 14 and you are correct.
Thanks G — I did not know that.
#7 – huh! who’d have thought? Very helpful.
#8 – love the explanation – thank you!
(and all the rest is great too!)
Happy New Year, Kevin!
#8 is interesting that duplicating a script step works better than copying.
I have a number of script templates for creating new scripts. The ‘Subscript’ template contains a number of disabled steps, including a ‘Perform Script’ step that performs the ‘Subscript’ template. These disabled steps are pasted into any parent script that will perform the subscript.
Duplicating the ‘Subscript’ template causes the ‘Perform Script’ step to point to the original template. Copying and pasting the script has the ‘Perform Script’ step point to the pasted script.
I added the ‘Import Records’ step and tested the above and copying the script causes the ‘Import Records’ step to behave as you describe.
Thankfully, my script templates don’t include an import process! I’m now wondering what other steps will behave differently.
I had a similar problem with the “Delete All Records” I added a new table to hold temporary data to a large existing DB. It was for a report and I had a script that Deleted the old records then grabbed data from various places and made new records. I first made it in an offline copy, tested it and it worked fine. I then copied the table into the live DB made a layout called “temp” and then pasted in the script. First time I ran it I was surprised at how long it was taking to delete ~100 records. Then I noticed the records counter said 110,000. It was deleting our whole contacts DB. (Thank Zeus for back ups!)
It seems there were 2 other layouts called “temp” and when I pasted in my script the “Go To Layout” step latched on to the first layout named “temp” in the list. Unfortunately this was pointed to the contacts table instead of my temp one.
Now I always check for the layouts table name before using “Delete All Records”
Hi Brian,
Completely agree… Get ( LayoutTableName ) is your friend here.
Or if you think you might ever change a table occurrence name, you can instead point to the internal ID of a given TO as per the FMNID_Table custom function mentioned here — http://filemakerhacks.com/2012/05/13/fm-12-executesql-robust-coding-part-1/
Best wishes,
Kevin