Bits and Pieces

Soundex sample

I didn't post a sample file for Soundex article, so here's a test file with about 14k names along with their Soundex codes.

Options

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

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.

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.

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.

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:

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.

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 note	Dear <<Name, please note
  Dear Name>>, please note	Dear 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.

Tips

• 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: FileMaker, FileMaker 7, FileMaker 8, custom function, merge.

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

Number to Decimal

A simple function to convert a number of any base from 2 to 36 into decimal form.

Number to Decimal( number, base )

If( IsEmpty( number ) or base < 2 or base > 36; ""; Let( k = Length( number ); ( Position( "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Left( number; 1 ); 1; 1 ) - 1 ) * base^( k - 1 ) + Number to Decimal( Right( number; k - 1 ); base ) ) )

Number to Base

A simple function to convert a decimal number to any base from 2 to 36.

Number to Base( number, base )

If( base < 2 or base > 36 or IsEmpty( base ); ""; Let( current digit = Middle ( "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Mod ( number; base ) + 1; 1 ); Case( number ≥ base; Number to Base( Div ( number; base ); base ) ) & current digit ) )