Tips ’n’ Tricks, part 3

It’s been a few years since we last had a tips ‘n’ tricks article here at FileMaker Hacks, and during the interim some new items have accumulated in the old developer bag of tricks. (If you like this sort of thing, you may also want to check out part 1 and part 2.) Some of these are obscure, and one is very basic. Ideally developers at all levels of expertise will find something useful (or worth arguing about) in what follows.

I recall Corn Walker introducing a DevCon session by saying, “I’m just a kid who paid attention in math class.” Along similar lines I pay attention when smart people share their hard-won wisdom, and a number of the following tips come from these individuals with (what I hope is) proper attribution.

Tip #1: One of these functions is not like the others

Some FileMaker functions require one or more arguments, e.g., Sum () or Evaluate ()… some do not require an argument, e.g., Random… and there is a single function where the argument is optional, i.e., the function can stand alone without parentheses like so…

Function

…or with parentheses like so:

Function ( argument )

Do you know which one it is? (See further discussion at the end of this article.)

Tip #2: Counter-intuitive behavior of certain word separators

A colleague mentioned recently that WordCount was “buggy” when evaluating UUIDs. Actually WordCount works as designed and we’ll get to that in just a sec, but here’s what he was referring to. Given this expression…

WordCount ( x ) can return different values even though the number of hyphens remains constant (4).

What the heck is going on? This Knowledge Base article (Word Separators in FileMaker Pro) is worth reading in its entirety.

This segment explains the method behind the seeming WordCount/UUID madness:

In FileMaker Pro 7 and later the hyphen, colon, and forward slash are treated as word separators unless both characters adjacent to the separator are numerals.

Tip #3: Does editing a related field also open the parent record?

The answer is… it depends. First off, you might think that having “allow creation” enabled/disabled between the parent and child would be the determining factor, but it makes no difference (the proof or refutation of this assertion will left as an exercise for the reader).

Let’s conduct a simple experiment… go to any database with a populated editable portal you may have lying around (or use my Winery JSON file from a few months ago). First click onto the background and confirm via the data viewer Watch tab that open record count = 0.

Next edit one of the values in one of the portal rows, and hit Refresh in the data viewer.

So the answer to the question “does editing a related field also open the parent record?” is yes… when you perform the edit manually. What happens if you perform the same operation via script?

Let’s find out.

As my little brother used to sometimes say in our poker games when picking up a freshly dealt hand: “Wow! Er, I mean… interesting.”

3 May 2019: See Krioni’s comment below, which explains this discrepancy.

Tip #4: Self-ishness can be a good thing

If you have a field that due to spatial constraints is too narrow to display all data contained therein, apply a tool tip like so…

…and all can be revealed in browse mode without the user having to click into the field.

One nice thing about using Self for tooltips (as opposed to specifying the underlying field) is that, should you so desire, you can bulk apply it to lots of fields at once.

Tip #5: More Self-ishness

All experienced developers have from time to time encountered the “clever” user who manages to enter garbage into our carefully designed systems. For example, they might paste in weirdly-formatted data copied from a web page, or enter returns or leading/trailing white spaces where we would have preferred them not to. Ideally we can nip this bad behavior in the bud by preventing the garbage from ever entering our systems in the first place.

Here’s how: use something like the following as an auto-enter calc on every text field where the user is allowed to enter data. As per the previous tip, the beauty of this is portability — i.e., you can apply it to any text field:

YourCustomFunction ( Self )

You might start with Ray Cologon‘s Trim4, which removes all leading and trailing “white space” (regular spaces, non-breaking spaces, tabs and hard returns). As a general rule, I also don’t want any consecutive spaces, or any formatting, and most of the time I don’t want any hard returns at all. Obviously one should fine tune as necessary, but here’s a general purpose CF based on Dr. Ray’s Trim4 that I call cfZap.

Tip #6: Don’t use = to locate empty fields

As pointed out by Russell Watson (a.k.a. MrWatson) at dotFMP last June, many developers reflexively search on = to locate empty fields. And most of the time it works… except… there is a specific circumstance under which it will return a false positive, i.e., a field that isn’t actually empty: when the field contains multiple hard returns and nothing else.

Best practice: search for empty fields using ==

Tip #7: A faster way to incrementally build a large block of text

Not content with the preceding, Russell Watson sent shock waves through the FileMaker world last August when he posted this to the FileMaker Community forum: Use “Insert Calculated Result” to concatenate strings!

Despite that exposure, recent conversations indicate that a number of colleagues are not aware of this technique, and I mention it because you will want to have this in your bag of tricks if a specific set of circumstances arises, and those circumstances are:

• You are looping to create an incrementally growing large block of text
• Each new string is appended to the end of the existing block
• Reminder: the block needs to be large for you to enjoy the benefit

To a certain extent I was guilty of unintentionally “burying the lead” when I published Faux Subsummaries via JSON + Virtual List a few months ago. Having prepared a demo file featuring these steps (in what became the “Method A” script)…

…this seemed an opportune moment to try out the Watson approach, so I duplicated the script and changed the four highlighted steps as follows:

And the results provide convincing proof that MrWatson’s miracle elixir is indeed worth imbibing.

Tip #8: Programmatically determine who is locking a record

Here’s a tip posted by David Head in the FileMaker Community forum — starting in FM 16 the Get ( LastExternalErrorDetail ) function returns both the account and user names of the user locking a record. Prior to FM 16 we did not have access to this information.

Tip #9: Bypass the character limit for PSOS script results

Did you know that PSOS parameters and results are limited to 1 million characters? If you’ve generated a large block of data on the server and need to transfer it back to the client, check out this neat technique from Fabrice Nordmann and Andries Heylen: dotFMP Developer Challenge

The idea of using a transfer table to move large chunks of data between the server and the client is not new, but they make a compelling case for using a container field + text file as opposed to, e.g., simply inserting the data into a text field. So what exactly is the compelling case that they make? Performance — across a WAN it’s blazingly fast.

Tip #10: Use CustomList to extend JSON’s power

There could be an entire blog article, or series of articles, devoted to the myriad ways CustomList can enhance JSON (or SQL, or, heck, just about anything… even my cats are being nicer to me since I embraced it). But in this case, I want to focus on a specific challenge that came up in one of the online forums.

Given a variable, $data, containing this JSON…

…is there a function to list all values matching a particular key, e.g., can we get a list  corresponding to the three “name” entries? Surprisingly, there is no native JSON function to allow us to do this. Of course one could build up the list using JSONGetElement inside a scripted loop, but CustomList allows us to do this with a single function call like so:

Let ( [
   a = JSONListKeys ( $data ; "bakery.product" ) ;
   x = ValueCount ( a ) 
] ;

CustomList ( 1 ; x ; 
   "JSONGetElement ( $data ; \"bakery.product[\" & [n]-1 & \"]name\" )" 
)

)

Result:

Donuts
Chocolate Cake
Baguette

Tip #1 Revisited

Returning to the question raised at the outset, the function is WindowNames (note the unusual placement of the braces)…

WindowNames {( fileName )}

…which optionally takes a file name as an argument, and like many functions that expect a file name, an empty pair of double quotes (“”) is equivalent to Get ( FileName ). If you leave the optional file name argument empty, then all visible as well as hidden windows for all files are listed, provided that they have drawn on screen at least once (in other words, all visible windows, as well as any under the “Window > Show” submenu that are not wrapped in parentheses).

Where this knowledge could come in handy: Given a multi-file solution, and the following script steps which are intended to close a “Reports” window in the current file:

Set Variable [ $x ; "Reports" ]
If [ not IsEmpty ( FilterValues ( WindowNames ; $x ) ) ]
   Select Window [ $x ; Current file ]
   Close Window [ Current window ]
End If

If you execute the above steps in your current file, but, rather than having an open window called “Reports” in your current file, you instead have an open window called “Reports” belonging to a different file…

• the “If” test will evaluate as true
• the “Select Window” step will fail (because “Reports” is not in the current file)
• Close Window will close whatever window the script was invoked from in the current file

Making the first change below in red will ensure this doesn’t happen… and while we’re at it, let’s play it safe and explicitly tell Close Window which window to close.

Set Variable [ $x ; "Reports" ]
If [ not IsEmpty ( FilterValues ( WindowNames ( "" ) ; $x ) ) ]
   Select Window [ $x ; Current file ]
   Close Window [ Name: $x ; Current file ]
End If