Tips ’n’ Tricks, part 1

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 ( "
SELECT TableName
FROM FileMaker_Tables
WHERE BaseTableName = ?
" ; "" ; "" ; "tags" )

…and will be rewarded with a nice clean list like this:

_g_TAG__ac
con_TAG
con_TAG__clear_tags
con_TAG__kill
DelDep_con_TAG
TAG
tag_TAG__deDupe

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

4.15.2016

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:

8.1...8.20

And of course you don’t need three dots as a range operator; two dots will suffice:

8.1..8.20

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…

YYYY-MM-DD

…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:

YYYY+MM+DD

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?

Q*

Of course not, because that returns entries where any word in the company name starts with Q.

What if you try this instead?

==Q*

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:

=100

Yep, that works.

And if you want to find customers with any three digit number anywhere in their company name, search on this:

###

Mission accomplished.

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:

Conclusion

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.