Over the last few years I’ve been collecting miscellaneous tips and tricks with the thought of eventually aggregating them into a blog posting, and it appears that day has finally come. Ideally there will be something new and useful here for developers of all levels, from the neophyte to the most grizzled of FileMaker veterans.
We start off with a couple FM 14-specific tips.
Tip #1: Tool tips in layout mode
In layout mode in FileMaker 14 you can hover your mouse over any badge and an informative tool tip will display.
Tip #2: Shift-Return in the Script Workspace
Did you know you can add new line above the current line in the Script Workspace by pressing Shift-Return? It works with the standard Return key, as well as the numeric Enter key.
Our next three items concern the Relationships Graph.
Tip #3: An easy way to add a text note to your graph
Did you know you can Option-drag on the Mac, or Alt-drag on Windows, to create a text note? No need to select the text note tool first.
Tip #4: Minimalist text notes
Starting in FM 13, you can eliminate all the window dressing of a text note, by making the note background transparent.
Incidentally, here’s what you get if you try this trick in FileMaker 12.
Tip #5: Identifying separation model table occurrences
[If you aren’t familiar with the Separation Model, there’s a great overview on the Seedcode blog.]
In a single-file solution, you can easily identify table occurrences (a.k.a. TO’s) for each table by opening Manage Database and taking a quick look at the Tables tab:
But in a separated solution, what if you want to identify occurrences for tables located in a file other than the current one? Here are some tricks that can help. Given two files,
- NRLT_Database (i.e., the “interface” file)
- NRLT_Tables (i.e., the “data” file)
A ) we’d like to know whether there are multiple occurrences of TAGS in the interface file
B ) we’d like to select them all so we can change their color at one shot
So let’s view the Relationships Graph for the interface file, and locate and select an occurrence of the TAG table — any occurrence will do… hey, look, there’s one:
Next, press Cmd-u (on Mac) or Ctrl-u (on Windows), or click this little widget and choose the highlighted option:
Now make note of the state of these four widgets — if they become active then you know there are multiple occurrences…
(If there aren’t multiple occurrences, they will remain greyed out.)
And now that we’ve selected all the TAG TO’s, we can easily reassign their color like so.
[Note: on the Relationships Graph you can recognize TO’s referencing external tables because their names will be in italics.]
Another trick, and this falls into the “quick and dirty” category, if you’re in a hurry and want to see all the TAG TO’s in the interface file, having previously selected them with Cmd-u or the method of your choice, you can click the various Arrange widgets to temporarily align and distribute the TO’s for your viewing pleasure — it takes a bit of trial and error but you can end up with something like this.
(Just make sure not to save changes when you exit Manage Database.)
But to end this section on a more elegant note, instead of the above, you can throw this into the data viewer…
ExecuteSQL ( "
WHERE BaseTableName = ?
" ; "" ; "" ; "tags" )
…and will be rewarded with a nice clean list like this:
And you can use a similar approach to count the TAG TO’s.
[For more information see Andrew Duncan’s superb article: Using ExecuteSQL to Query the Virtual Schema/System Tables ]
Next up we’ve got a couple tips related to date fields. The first one is quite simple, but it can save you a lot of finger mileage over the years to come.
Tip #6: You can use a dot as a date separator
When you enter a date, you probably use a slash or a hyphen, but a dot will work as well, e.g.,
The benefit (for touch typists) being that you can keep your hands in a very comfortable position when you enter a date. I especially appreciate this when inputting a date range, e.g., searching for the first 20 days of August of the current year like so:
And of course you don’t need three dots as a range operator; two dots will suffice:
Tip #7: Moving a date from the SQL realm into the FileMaker realm
If you use ExecuteSQL to derive a date, it will be in this format…
…which FileMaker does not recognize as a valid date format. You can prove FM does not recognize it like so:
An easy way to cajole FM into recognizing the date is to transform it into Japanese format by substituting plus signs in place of hyphens, so the date takes this form:
Now FileMaker is happy to recognize it as a date.
In addition to GetAsDate (and GetAsTimestamp), you can use any of these functions with Japanese style dates…
Along the same lines, if you attempt to set “2015-11-30” into a date field, FM will simply display a question mark, but if you set “2015+11+30” into the field you will see a properly formatted date.
[For more on Japanese-style dates, see Birthday Challenges, part 1 and part 2, including some thought-provoking exchanges in the comments section of each.]
The remainder of today’s article explores various search techniques.
Tip #8: Finding entries that begin with a certain letter
You’ve been tasked with producing a list of all customers where the company name begins with the letter “Q”. Do you search on this?
Of course not, because that returns entries where any word in the company name starts with Q.
What if you try this instead?
Now that’s more like it.
Explanation: the “==” is the exact field match operator, so basically what you’ve told FileMaker is “locate company entries where Q is the first character in the field followed by anything”.
Tip #9: Finding entries that begin with a number
What if you want to locate records where the company name begins with a number? In this case, the # (“any one digit”) operator will come in handy, but you don’t just want to simply search on #* because that will return records where any word in the company name begins with a number…
…whereas you want just the companies where the entire name begins with a number. And, similar to what we saw in the previous tip, you can search on this…
…to achive the desired result.
Tip #10: Searching for numbers in text fields
Let’s say you want to find all companies with the number 100 anywhere in their name. You might think that searching on 100 would do the trick, but, depending on your data, that might not be a safe assumption, for example in this case you would get this…
…because you did your search in a text field, and 1000 is a valid text match — in the same way that searching a text field for Lou would not only return records with that name, but also Louis, Louise and Louisiana, if those terms were present.
To locate just the records (or in this case the single record) containing 100, use the = (word match) operator and search like so:
Yep, that works.
And if you want to find customers with any three digit number anywhere in their company name, search on this:
Tip #11: Locating odd or even numbers
Given this table…
You’ve been asked to locate records with odd number page counts. Now you could of course define a calculated text field like so…
If ( Mod ( pages ; 2 ) = 1 ; "Odd" ; "Even" )
…place it on the layout, and then search on the word “odd”…
Or, you could instead perform this search:
Actually, since pages is a number field, we can dispense with the = (word match) operator, and simplify the search to:
In either case, we’ve constructed a five-request find for values ending in 1, 3, 5, 7 or 9, and this is the result:
And if we’re looking for odd dollar amounts, we can do the same search in the cost field. However, if we’re looking for entries where the cents are odd, we would construct the search like so:
And here is the result:
Well that’s about enough for today, but there are still a few items rattling around in the old developer bag of tricks, so stay tuned for part 2, which will appear at some indeterminate point in the near future.
16 thoughts on “Tips ’n’ Tricks, part 1”
For Tip 7,
Instead of :
ExecuteSQL ("SELECT theDate FROM WorkDone";"";"")
ExecuteSQL("SELECT Coalesce(theDate,'') FROM WorkDone"; "";"")
[ pay attention to comma (not semicolon) and double
'use in Coalesce () ]
Thank you Radu-Dan, that is very cool. I already had a Coalesce example lined up for part 2, but was not familiar with this trick. I will elaborate on this in part 2.
Great tips! Thanks, Kevin.
Kevin, Lots of great tips!!! Thanks for posting.
Wow! Awesome set of tricks. Thanks for sharing. You made my day!
Kevin, thanks for the post. I look forward to part 2.
Great tips, Kevin. Thanks!
Great tips Kevin, especially the ExecuteSQL tip for listing TOs in the data viewer!
Top tips Kevin…
Its always amazing how much more there is to learn…
Awesome tips, Kevin. I didn’t know about the transparent text note on the graph, or the keyboard shortcut for “select all table occurrences with the same base table”. Also, the searching stuff (particularly finding odd or even entries) were stellar. Thanks!
Thank you Darren and everyone else for the kind words. Your feedback means a lot to me.
What Mark R. said…
Great gems Kevin. Now I have to try and burn the odd/even find techniques into my brain…
Thanks (again :) Kevin. I’d just been racking my brain to remember how to use the Japanese format w/ SQL dates so this came at a perfect time for me. It’s great now as a SQLDateToFMDate custom function.
Thanks for the great article, Kevin.
I have translated it into Japanese which you can read at http://notonlyfilemaker.com/2015/12/tips-n-tricks-part1/ .
Also looking forward to Part 2.