Linked fields

FileMaker auto-enter capabilities make it possible to create “linked” fields that are directly editable and update each other to keep them in sync.

What I personally like about this technique is they way it works: no buttons to press/scripts to run/events to trigger, but everything “just” changes to keep things linked. To me this makes a very smooth working experience, maybe because it visualizes the internal logic of the application.

QuickTime video.

Check the sample file I used to prepare two samples I describe.

Continue reading "Linked fields" »

Modular XSLT, part 2: Simple export

As you know you can export or request data from FileMaker in XML and then transform the XML using a XSLT stylesheet into some other format. The question is: how do you approach writing such a stylesheet? Are there some universal techniques? Can you save some effort?

Here's a general tutorial, covering a basic export to simple XML format. As you'll see it isn't just a look-ma-it's-XML sample, but more like a foundation for any many well, at least some XSL-transformation problems.

The tutorial assumes you know XSLT basics and maybe have even tried to export something from FileMaker using the built-in XSLT processor. For example, you must understand what <xsl:temlplate> or <xsl:param> are for. If you don't know XLST, you might want to start from some XSLT Tutorial, like this quick one by W3Schools or that more detailed one by Zvon; this is where I started.

Technorati Tags:

Continue reading "Modular XSLT, part 2: Simple export" »


Here are two functions I use to pass parameters to scripts: Option() and Get Option():

Continue reading "Options" »

Fraction(): approximate a number as a common fraction

The Fraction() function approximates a number as a common fraction. For example, Fraction( 0.625, 16 ) = 5/8. Here 0.625 is the number being approximated and 16 is the maximum allowed denominator of the result.

The function considers the maximum denominator only as a limit it cannot exceed, but uses the denominator that gives the most precise approximation. For example, Fraction( 3.1415926, 100 ) = 3 1/7, because 3 1/7 is the most precise approximation of 3.1415926 among all fractions with denominators less than 100; only 106 will give better precision.

Continue reading "Fraction(): approximate a number as a common fraction" »

Greatest Common Divisor

The function finds the greatest common divisor or greatest common factor for two numbers; for example, the greatest common divisor for 8 and 12 is 4. It's very simple and is widely available in fact, but I'll need it for some next posts, which are almost written now.

Continue reading "Greatest Common Divisor" »

Rounding to a given number

These custom functions round a number “naturally”. For example, they can be used to round time to 15 minutes, or money to $.25, or just any number to a multiple of another number.

All the functions have the same syntax:

Round To( number, precision )

Round Down To( number, precision )

Round Up To( number, precision )

where number is the number to round and precision is the number to calculate the appropriate multiple of. For example, Round To( 13, 5 ) rounds 13 to the nearest multiple of 5, i.e. 15. The Round Up To() and Round Down To() round the number to higher or lower multiple of precision respectively.

Functions themselves are very simple:

Round To( number, precision )

Round( number / precision; 0 ) * precision

Round Down To( number, precision )

Floor( number / precision; 0 ) * precision

Round Up To( number, precision )

Ceiling( number / precision; 0 ) * precision

How to round time

Don't calculate the number of seconds: use the Time() function instead. For example, to round to an hour:

Round To( Time Field, Time( 1, 0, 0 ) )

to 15 minutes:

Round To( Time Field, Time( 0, 15, 0 ) )

You might also want to make a few constant custom functions: HOURS and MINUTES. These function must return Time( 1, 0, 0 ), Time( 0, 1, 0 ) respectively. (You could make a function for seconds, if you need them, but you'll need to select a name carefully, because Seconds is taken already.) With such functions your code will be more readable, like this:

Round To( Time Field, 2 * HOURS )

Round To( Time Field, 0.5 * HOURS )

Round To( Time Field, 15 * MINUTES )

Round To( Time Field, 1/2 * MINUTES )

There's yet another good use of this function: it can help to approximate a number to a common fraction. I'll write about this later.

Continue reading "Rounding to a given number" »

Soundex and Miracode

Sometimes data are entered incorrectly because the person entering data mishears a word (most often a name); for example, enters Awbrey instead of Aubrey. This is especially true for English language, which has complex spelling rules. There are algorithms may help you to find all words that are pronounced closely to a sample. Given an English word, such an algorithm calculates a code that represents how the word sounds. Take a look at the following sample of an algorithm called Soundex:

Sample phonetic matches for name ?Aubrey?

Here A160 is the calculated code of Aubrey. You can see that similar names like Awbrey or Abra have the same code. Such algorithms are called phonetic or phonetic indexing algorithms; the word “index” reflects the fact that the algorithm takes a single word and calculates a phonetic code, which is same for all words that sound alike. (The latter is not always true because algorithms aren't perfect). The code can be used on its own for searching, sorting, grouping and so on. In FileMaker terms this means the calculated code can be stored and used for fast searching and making relationships:


Other algorithms that can make a fuzzy comparison work differently: they take two words and compare their similarity. This isn't as convenient as having an index of a word; every time you need to search for words similar to the given one you must check each record in a table. In FileMaker terms this means the calculation will be unstored and slow.

Most known of phonetic indexing algorithms are: the Soundex family, Daitch-Motokotoff Soundex, NYSIIS, Caverphone, and Metaphone; I wish I could be able to write a custom function for each. For now let's start from the simplest and the most famous Soundex and Miracode.

Continue reading "Soundex and Miracode" »

Handling exceptions

Let’s consider the problem of how to handle exceptions? Assume we have already chosen to go beyond the “method” of simply ignoring them by placing a Set Error Capture[ On ] step at the beginning of a script. Shouldn't we then just write code like this?


Well, I think that though the code above is correct, it still has a problem: it mixes the exception part with the main functionality. It's difficult to follow such a code. Imagine there's three error-checking conditions in a script and you're reading it after a while; you'll have to open three script steps and read their formulas to understand what's going on.

Though you cannot avoid the mix, it worth trying to clearly separate the part that handles exceptions from the rest of your code. After all, exception handling requires very limited functionality: you need to throw an exception, watch for it, and handle it somehow.

I'm going to describe a system I use. The description is fairly long, but actually the system is very simple and takes a couple of fields, a couple of scripts and a handful of very simple custom functions.

Continue reading "Handling exceptions" »

Merge Expressions

This custom function combines arbitrary text with field data preserving text formatting styles as much as possible. Short, it works like FileMaker native merge feature with some differences. Here's a screenshot from the sample file:


  • The function changes everything between between << and >> to the result of evaluating this as a FileMaker expression. One of simplest expression is a field name, but technically such an expression can contains any valid combination of fields, operators and functions. (If this is too much the function can be downgraded to fields-only variant.)

    Dear <<Name>>Dear John Doe
    the late fee is $<<Amount * 0.05>>the late fee is $6.5

  • The function doesn't change invalid or incomplete expressions.

    Dear <<Nmae>>Dear <<Nmae>>
    Dear <<Name, please noteDear <<Name, please note
    Dear Name>>, please noteDear Name>>, please note

  • The function sets the style of the merged data to the style of the placeholder, or, if the latter has mixed styles, to the style of the 1st “<” character.

    Dear <<Name>>Dear John Doe
    Dear <<Name>>Dear John Doe

    If the expression itself has text styles (for example, if it references a field which is manually formatted), these styles are combined with the styles of the placeholder. If the styles conflict and cannot be combined, the expression style is used. For example, if the Name field contains John Doe in blue, then:

    Dear <<Name>>Dear John Doe
    Dear <<Name>>Dear John Doe

  • You cannot set default number, date or time format for the whole piece of text as you can do for FileMaker layout-mode merge feature. If you need to use a special format, you'll have to do this via a custom function or extra field.

Merge Expressions( text )

If( IsEmpty( text ) ; "" ;

  Let( [ 
    S = "<<" ; E = ">>"; 
    start = Position( text; S; 1; 1 );
    end = Position( text; E; start; 1 ) ];

    If( start = 0 or end = 0; text;

      Let( contents = 
        Middle( text; start + Length( S ); end - start - Length( S ) );

        If( IsValidExpression ( contents );
          Left( text; start - 1 )
            & Replace( Middle( text; start; 1 ); 1; 1; Evaluate( contents ) )
            & Merge Expressions( 
              Right( text; Length( text ) - end - Length( E ) + 1 ) );

          Left( text; start + Length( S ) - 1 )
            & Merge Expressions( 
              Right( text; Length( text ) - start - Length( S ) + 1 ) ) ) ) ) ) )

It's a bit difficult to write a test unit test for this function, so I simply made a sample file and tested the function manually. Please tell me if you find an error.


  • If you want to downgrade the function to use fields only, you can use the GetField() function instead of the Evaluate().

  • If you want to restrict the function to certain fields only, you'd better fine tune this in FileMaker privileges. For example, if you set a field as “no access”, an expression that addresses such a field won't be evaluated.

Technorati tags: , , , , .

Custom functions to simplify reading the current state of modifier keys

The state of modifier keys in FileMaker is represented as a five-bit number taking values from 0 to 31. To read the state of a particular key means to read a particular bit of this number. FileMaker doesn't have native support for binary operations, so developers have to do this on their own. The best way, I think, is to write a few simple custom functions.

The “simplest” (that is without thinking) method would be to go ahead and check the current value of Get( ActiveModifierKeys ) each time you're to read the current state of modifier keys. This approach has several disadvantages. First, one must do it right. Simply checking for

Get( ActiveModifierKeys ) = 4

may or may not give you the state of the Shift key, because at the same moment the user may have the other modifier active. For example, if Caps Lock is active, the result of Get( ActiveModifierKeys ) will be 6; same is for other keys, though they're less likely to be mixed.

Second, it's difficult to check for combinations of keys: the code becomes hardly readable. Time, for example, how long does it take you to answer what exactly is checked by the following code:

Get( ActiveModifierKeys ) = 9

In my applications I use a few custom functions to check the modifier keys' state. For example, I have a function Shift Is Pressed. This function takes no arguments and returns either True or False depending on the current state of the Shift key. It doesn't care whether other modifiers are pressed or not. With such functions the calculations I write look much simpler:

If( Shift Is Pressed, ... If( Option Is Pressed and Shift Is Pressed, ... If( Option Is Pressed and not Control Is Pressed, ...

Continue reading "Custom functions to simplify reading the current state of modifier keys" »