Duplicate functionality of merge fields in a calculation.

[jasonwood]

I wish to duplicate the functionality of merge fields within a calculation field so that database users can insert "merge fields" within a larger field, to be substituted by a separate calculation field.

I'm thinking the calculation should scan (somehow) for all spans of text beginning with << and ending with >> (or some other delimiter) then use "Evaluate()" to replace the enclosed text with the field value.

Is this possible?

[comment]

Why not use Substitute()?

[jasonwood]

If you are suggesting a nested substitute with all the possible fields that a user could want to merge, I thought of this but was just wondering if there would be a more dynamic method that wouldn't rely on a pre-selected list of qualified merge fields.

Or are you suggesting that Substitute can be used to perform the substitution dynamically as I described?

[David Jondreau]

Do you want to preserve formatting (bold, italics, etc) or not?

If no, it's a Substitute() calc:

Let ([

//escape quotes, including "smart" quotes

pass1 = Quote ( text ) ;

//sub merge fields with calc formatted fields

pass2 = Substitute ( pass1 ; ["<<" ; "" & "]; [">>"; " & ""]);

result = Evaluate(pass2)

] ;

result

)

If you want to keep formatting you'll need a recursive function that uses Replace() instead.

I've had success wit this: http://www.briandunning.com/cf/831

Edited November 12, 2010 by Guest
Quote() makes it simpler

[jasonwood]

Very good method! Thanks D J,

I do not need to preserve formatting as I'm using it to generate html bulk email source.

[comment]

If you are suggesting a nested substitute with all the possible fields that a user could want to merge

Yes, that's what I was suggesting, for two reasons:

1. I don't like exposing my real field names to the user;

2. This method allows for specific handling of selected fields, such as expanding a date into long date, formatting a number as currency etc.

How many fields do you have anyway?

[bcooney]

Take a look at Bruce's demo using the CF.

http://fmforums.com/forum/showpost.php?post/367782/

[jasonwood]

Yes, that's what I was suggesting, for two reasons:

1. I don't like exposing my real field names to the user;

2. This method allows for specific handling of selected fields, such as expanding a date into long date, formatting a number as currency etc.

Good points. I will keep in mind.

It's only used in my own company so I can always change it. Until I decide on a complete list of all the fields I would want to merge, I think I'll just keep it flexible by using the Mail Merge function suggested.

Thanks all!

[David Jondreau]

For formatting, I use custom functions like DateFormat() and Currency() and put "Date" or "Currency" in the field comments and test the field with FieldComments().

But, it seems like comment's technique makes more sense. You're going to have to give a list of fields for users to use anyway.

That list could be text as buttons that users click and it inserts the <>.