Merged Letter Demo

[Vaughan]

This is cool. It's a quick and dirty demo of the substantially improved letter merging features in FMP 7.0.

Basically, text and paragraph formatting can be used in a template that is then applied to another database through a related calculation field.

It is seriously serious stuff. For some reason my clients all want letter templates like this. In FMP 5 it was possible but no formatting (character or paragraph) could be used -- if they did it was lost during the merge. In FMP 6 character formatting could now be applied in the templates, which was a big step forward, but it required the use of the Replace function, copy and paste, and each record had to be processed individually.

The big leap forward with FMP 7 is that character and paragraph formatting now works, and the formatting is carried across through calculated fields and relationships.

The demo is unlocked. To add or modify new letters, switch to the Letters layout.

Version: v7.x

Platform: Mac OS X Jaguar

Letters.zip

[Rangoon]

Hi Vaughan,

I've had 7.0 for awhile now, trying to find time to really shake it down before converting files. You know haw that is!

I could not thank you enough for your "Letter" demo file. I frankly had no idea we could do this in 7.0 and as you said "it is reason enough to get 7.0". Letter merging has always been a "work around" in FileMaker and I guess that's understandable when your thinking Database, not Word Processor. But FileMaker has always been so, dare I say elegant, we just expected to have it all! This seamless merging ability is really great stuff and it just happens to work right into a project I'm working on.

Thanks for your post vaughan and I'll spend the rest of this week really giving 7.0 the attention it deserves.

You are in fact "The Big Thunder fro Down Under"!

You're the best...

Harry

[Aussie John]

Vaughan,great stuff but I noticed if you change the persons title then try to change it back-well you cant-- Is this a bug in Filemaker?

[Vaughan]

It's a behaviour that has been around since FMP 3.

Actually it's an artefact from basing a value list on a field when there are few records in the table. If "Mr" is cleared from the first record the "Mr" disappears from the value list because it's no longer in the database. If there were a dozen records with Mr then it'd persist because the value exists in other records -- at least until the MR title had been removed from all of them, they it work disappear.

[Vaughan]

Heh heh... I just had to download my own demo file to remember how to do a mail merge!

And then I found a bug in it! In the People table, the DateLong and DateShort calculation fields should be set to unstored. Otherwise they will perpetually show 30 March 2004 as the date in the letter.

Odd bit of trivia for the HHGG fans: 30 March 2004 was EXACTLY my 42nd birthday...

[Inky Phil]

That made me smile!! but what's HHGG?

Phil

[Genx]

I think this was the first mail merge demo i ever saw - about 6 months ago, but it was locked at the time. I did however manage to find a file somewhere else in the forums, but its good you've unlocked it finally

~Genx

[Inky Phil]

That made me smile!! but what's HHGG?

Phil

[Genx]

echoing i can understand, but echoing yourself 37 minutes later?

[Inky Phil]

I got distracted, went away from my desk and the reply box was still there when I got back so I hit the button - Ooops!

[Ender]

I didn't remember this one, Vaughan.

Just one recommendation for anyone using this technique: to improve readability of those nested substitutes, use the bracket format:

Substitute ( Letters::LetterBody;

["<>"; TitleLastName ];

["<

>"; Title ];

["<>"; Last Name ];

["<>"; First Name ];

["<>"; Name Suffix ];

["<>"; AddressFull ];

["<>"; DateLong ];

["<>"; DateShort ];

["<>"; Street ];

["<>"; State ];

["<>"; Postcode ];

["<>"; Get(UserName) ];

["<>"; Full Name ] )

[Hasse_r]

I liked this one. I am working with a database for a travelagency and need to make it possible to print out e-tickets for several different flight companies. I think that with some minor modifications, this might be the solution I was looking for. Thanks.

[ralph.nusser]

With the evaluate function it is possible to merge form letters with fields that are not hard coded in the solution. It is even possible to make calculations in the form letter, e.g. how many days until the birthday of the recipient [see attached example], but the disadvantage is you cannot bring over the formatting.

Is it possible to merge the method of [color:blue]Vaugham with the method of [color:red]Mike Duncan?

http://www.briandunning.com/cf/438

Happy FileMaking Ralph Nusser

Letters_1.0.1.zip

[Ender]

Hi Ralph,

The two methods are mutually exclusive, one using substitute() and the other parsing the text for the <> characters.

I would say, however that Mike Duncan's CF is lacking as it doesn't handle invalid field names within the merge brackets, nor does it handle mismatched brackets. You might check my CF here that does handle those issues:

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

Comparing the substitute() method and the evaluate method, I'd say I like the simplicity and portability of the CF. But it's important to understand the possible problems with it.

First, it's hard for users to understand that table names must be included in related field names. Users don't know which tables hold which bits of information (and they shouldn't need to,) but the CF with the evaluate function wants to have the correct path to each field. You could get around this problem by having local copies of related fields, but then you lose some normalization. You can check in that above thread for my demo showing how I handled this.

Second, for a large, well integrated solution, there may be security risks in allowing users to evaluate whatever they might type in. If you didn't have access to sensitive fields restricted in the access privileges, someone could type the field name to see what's in there.

For those two reasons, I think I prefer the substitute() method. The field names your users type don't need to be the real (local or related) field names, and the only substitutions are those that are explicitly listed.

You might also look at the method Robert Kidd came up with here, using the corrections I suggest at the end:

http://fmforums.com/forum/showtopic.php?tid/174672

[Vaughan]

Inky Phil: HHGG = Hitch Hiker's Guide to the Galaxy -- Douglas Adams.

The answer to the Ultimate Question of Life, The Universe and Everything is... 42.

--

Great ideas for improving the mail merge folks!

My only reluctance to use custom functions is that they can only be defined with FM Pro Developer/Advanced and not the plain old Pro client. That wipes out a huge slice of the audience here at the Forums. And me too!

As an extension to the mail merge thing (and the reason I dug out the demo again) a client wanted a mail merge system where they can type in merge codes into the template, as many as they want, and the system parses them out prompts the user for information (information that might not even be in the database) when the template is processed. All this plus letter templates with page breaks. With features like these the actual merging of the codes is the most trivial part of the process!

[Splitseconds]

Hey thanks for posting those templates. What a resource!

I am using one of the stock BPS solutions that came with filemaker and have modified it slightly for my needs. Is there an easy way to have this letter be available in my database at a click of a button? For example - I have my client list and select Jane Smith from ABC company, can I easily add a button that says Write Letter, click it and this template opens with Jane Smiths fields in place followed by the intro letter?

Let me know if that makes sense?

[igotit]

Hi Guys,

I just want to ask if there is anything that should be changed in the "Letters" file if it is going to be added to a solution done in 8.5? I do have 8.5 Advanced.

I do not understand the information on the post from Ender regarding

"Substitute ( Letters::LetterBody;

["<>"; TitleLastName ]; etc. Could you explain that please.

One other question if I may. I also downloaded a merge file called "MergeTest". How should I decide on which one to use in my solution? I'm a FMP novice.

Thank you for your help,

Milo

Edited July 2, 2007 by Guest

[hcmbrbj]

I realize this thread is very old, but hope someone has some recent data regarding FM10 in regard to Vaughan's Letters.fp7. I'd like to insert a logo into a new letter, but when creating a new letter can't get a picture insert to stay. It will go into the layout mode just fine, but doesn't show up in Browse mode. Any suggestions will be deeply appreciated.

[michelle06]

If your solution requries many of letters and other printouts or many of slightly different versions of the same documents.

Your chances are you take of dozens of layouts. :

A single custom function becomes the template text into merged content. :

[Vaughan]

I'd like to insert a logo into a new letter, but when creating a new letter can't get a picture insert to stay. It will go into the layout mode just fine, but doesn't show up in Browse mode. Any suggestions will be deeply appreciated.

Images cannot be merged. Only text.

It might be possible to do something with the web viewer, by coding with html. It would no longer be a wysiwyg editor since the formatting would have to be done in raw code.

[VincentO'B]

You could try a global container field for the logo.