Bits and Pieces

Cross-tab reports made easy

Here's a simple and powerful method to create cross-tab reports in FileMaker.

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.

Use the Gradient() function to apply a smooth gradient to a string

The Gradient( text, colors ) custom function paints the specified text with colors, applying a smooth gradient if more than one color is specified.

It may be not be terrible useful but looks nice and is fun to study as an example of a recursive function.

Technorati tags: FileMaker, FileMaker 7, FileMaker 8, custom function, gradient.