Look-ups for Form Letters

[Dave Keiser]

What's the best way to do lookups for form letters? I'm creating layouts in my database to send various letters for different occassions. It's easy if you simply build the layout. But I'd like for the text to be stagnant after I send the letter and not reflect any text changes I make to the layout further down the road. I'd also like to be able to alter the wording of the text on a per record basis without altering the text for every record.

Special Edition for FileMaker suggests creating a separate database with the text that I would simply lookup and fill in my forms. What's the best way to do that? Do I simply relate it with customer IDs?

[ April 03, 2002, 09:08 AM: Message edited by: Dave Keiser ]

[andygaunt]

If you want to create a letter that you can pull from a standard template and then change on a per record basis you would be best to have a seperate database for your letters.

Using this, in conjunction with a templates database you can then perform your request.

So, you would need a database to hold the basic letters. Fields required would be:

Serial Number for each template [Template ID].

Subject

Body

Then a database to hold the customers letters. Standard fields would be:

Serial Number for each letter [Letter ID]

Template ID

Customer ID

Subject

Body

Once you have found the customers, import their id's into the letters database. This gives you one record for each contact.

Then choose the template and set a field in the letters database to that of the template identifier. The body of the letter can then be looked up from the letters database and you can then change each record individually.

The benefit of this is in your templates database you do not have to go into layout, just create a new record and type away. Can also be used for mass emails if needed.

HTH.

[Glen62]

I am working on a project requiring a similar solution so thanks for the tip Andy.

Is it possible for the looked up letter ie the template to include merge fields?

[andygaunt]

Yes it can include merge fields (of sorts).

You can enter merge field information into the template, and then when you create the actual letters you can run a looping substitute to replace the merge field text with actual data.

You cannot use merge fields in a field so this is the a workaround.

So, if you had merge fields like <<Name>>, <<Company>>, <<Address>> etc.

you would create the actual letters and then run the substitute script.

This would be:

Allow User Abort [OFF]

Set Error capture {On]

Freeze window

Go to record [first]

Loop

Set Field (letter body field, Substitute(Substitute(Substitute(Letter Body Field,"<<Name>>",Name) ,"<<Company>>",Company) ,"<<Address>>", Address)

Go To Record [next, exit after last]

End Loop

HTH