How to email "letters" with Merge field data

[Jason H]

I'm trying to create a solution that has a large number of prewritten letters that have Merge Fields in them.

This is quite easy and works great.

 

...but I need to email them.

Since the letters are just text and not in a field containing the text, I can't reference them with the Send Email script step.

 

What is the best way to do this?

 

Jason

[bruceR]

The best way would be to start by examining your assumptions.

The Send Email script can send emails based on field or on calculations.

It would also be helpful if you can post a copy or clone of your file or a simplified example. 

You can set a variable to the contents of your letter body, perform substitutions within  a script; set a variable to the result; and send the message using the resulting variable. 

[Ocean West]

Jason,

 

typically I create a table of your standard "template" of the letters inserting placeholder in the text such as <<MYFIELD>> or even {{MYFIELD}} then at the point of email or printing evaluate the field or use a calculated field that evaluates the templates substituting the fields with the data in the record. 

 

There were a few custom functions out in the community that help with the substitution such as:  http://www.nightwing.com.au/FileMaker/demos9/demo913.html

 

 

sd

[Jason H]

OK. This is looking good!

Let's see if I have this right.

I will have a "Letter" table that will contain a variety of "Letter templates" as records in that table.

In the Letter text I would insert place holders such as {{MYFIELD}} to represent fields that I want to insert later.

When the letter is to be sent a script would be triggered that would:

-set the template letter text as a variable

-use the SUBSTITUTE script step to replace the placeholders in the variable (assigning the variable as SUBSTITUTE's "text")

-Use the SEND EMAIL script step to send the final version of the variable containing the letter.

 

Did I miss anything?

[comment]

See also:

http://fmforums.com/forum/topic/73210-business-form-question/#entry346234

[Jason H]

Thanks for all the examples !!

I am examining one of the linked examples and it is perfect! Exactly what I wanted!

It turns out that the "Substitute" script step was the piece that I was missing in my own attempt. Otherwise, I was on the right track :-)

 

Thanks everyone !!

 

Jason

[Ocean West]

I really like this function as it allows you to also "evaluate" data on the fly

    // SYNTAX:  Merge ( Template )
   // Dynamic text processing function by Ray Cologon
  // © 2006 NightWing Enterprises, Melbourne, Australia

Let([
p0 = PatternCount(Template; "##");
p1 = Position(Template; "‡‡"; 1; 1);
p2 = Position(Template; "‡‡"; p1 + 2; 1);
p3 = Position(Template; "<<"; 1; 1);
p4 = Position(Template; ">>"; p3; 1);
p5 = Position(Template; "[["; 1; 1);
p6 = Position(Template; "]]"; p5; 1);
p7 = Position(Template; "{{"; 1; 1);
p8 = Position(Template; "}}"; p7; 1);
Pn = Middle(Template; p1 + 2; p2 - p1 - 2);
Fn = Middle(Template; p3 + 2; p4 - p3 - 2);
Cn = Middle(Template; p5 + 2; p6 - p5 - 2);
Et = Middle(Template; p7 + 2; p8 - p7 - 2);
Td = GetAsDate(Get(CurrentHostTimeStamp));
Nd = GetAsDate(Td);
Ld = Day(Td) & " " & MonthName(Td) & " " & Year(Td);
Sd = Day(Td) & " " & Left(MonthName(Td); 3) & " " & Year(Td);
Ps = "‡‡" & Pn & "‡‡";
Fg = "<<" & Fn & ">>";
Cf = "[[" & Cn & "]]";
Em = "{{" & Et & "}}";
Ea = TextColor("*InvalidExpression*"; 16711680);
Ma = TextColor("*FieldMissing*"; 16711680);
Pa = TextColor("*UnknownFlag*"; 16711680);
Er = If(EvaluationError(Evaluate(Et)); Ea; Evaluate(Et));
Fv = If(IsValid(GetField(Fn)); GetField(Fn); Ma);
Cc = If(IsValid(GetField(Cn)); GetField(Cn); Ma);
Cv = If(not IsEmpty(Cc); ¶ & Cc)];
Case(
Right(Template; 1) ≠ ¶;
Merge(Template & ¶ );
p0 > 1; Merge(Substitute(Template;
["##Date##"; Nd];
["##LongDate##"; Ld];
["##ShortDate##"; Sd];
["##AccountName##"; Get(AccountName)];
["##Workstation##"; Get(UserName)];
["##"; "‡‡"]));
p1 > 0 and p2 > p1;
Merge(Substitute(Template; Ps; Pa));
p3 > 0 and p4 > p3;
Merge(Substitute(Template; Fg; Fv));
p5 > 0 and p6 > p5;
Merge(Substitute(Template; Cf; Cv));
p7 > 0 and p8 > p7;
Merge(Substitute(Template; Em; Er));
Template
)
)