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.

Comments

[Ender]

This looks great, Mikhail!

It's simliar to my FieldSubstitute() CF:

http://www.fmforums.com/forum/showtopic.php?tid/120434/

but yours handles mismatched brackets more gracefully, and allows for user defined styles within the text.

Frankly, I'm a bit puzzled at how it's able to preserve the style of the source tag. Is this an ability of the Replace() function?

[m.edoshin]

Yes; as far as I understand FileMaker 7+ preserves text styles in calculations so when you replace something and the new text doesn't have its own styles, it inherits the style of the replaced part. This is same for all functions, I think. By the way, if the new text is styled, the styles are merged and the new text attributes take precedence. For example, if in the sample above you manually format the text in the Text field to be green, this attribute won't be overridden.

[Ender]

Makes sense. Thanks for the explanation. And thanks for the detailed information on all these techniques!

[Mark Valverde]

Your solution is awsome. I don't understand the calculation but I don't have to. It works in my solution by adding the fields and custom functions you have in your example. Thanks a bunch. Mark Valverde

[Lina]

Hello Mikhail.

This function is wonderful - just what I needed.

I just need to have dates & numbers specially formatted, which you mention need custom functions.
I have found these great 2 relative functions:
http://www.briandunning.com/cf/1018 (number formatting)and
http://www.briandunning.com/cf/600 (date formatting)
but I am not sure how to use them with your Merge function.

I am a newbie - can you please explain?
Many thanks!

[m.edoshin]

Hi Lina,

You need to install all three custom functions, the Merge Expressions() and the two others you've found and then write the merge files like:

... on [[ Date Format( My Field ) ]] ; you received a check for [[ fnNumberFormat( My Invoice::Total, 2, ".", "," ) ]]...

(I've used [ and ] to imitate angle brackets, because otherwise TypePad eats them.) That is exactly as you'd write this in a calculation field, only between the brackets.

[Lina]

Fantastic!
It worked perfectly, that's exactly the instruction I needed.

Thank you so much Mikhail for your kind and fast response.
(and for all the other useful tips and resources you post)