merge fields

[mikemccloskey]

I am attempting to create a file that will let me alter the text of a letter through the use of a text field. All is well but I would like to know if there is a way to insert "merge fields" (as in layout mode) into a text field so as to alter the text content of a letter. I don't want to have to go to layout mode and change the text of the letter. I want to to this via a field of information. Thanks for any help.

[mikemccloskey]

One other thought about this. I would like these letters that I am createing to be part of a separate file, each record being a different letter to be sent. The rub is - how do I send this letter to a found set of contacts from my contact file? thanks again

[Vaughan]

It's done with a humungous calculation field -- lots of nested Substitute()functions, one for each merge field. The database of letters is easy: each record has a text field with the leter/merge codes. A relationship is set up so that every record in the master database relates to the letter records.

I'm doing some now, most of the time and energy is in the Substitute() calculation field, it's soooo big and unfortunately the merge fields need to be hard coded into it.

[LeCates]

Hi Vaughan,

quote:

---

Originally posted by Vaughan:

...I'm doing some now, most of the time and energy is in the Substitute() calculation field, it's soooo big and unfortunately the merge fields need to be hard coded into it.

---

If FMP 5.5, you might use GetField() to get around "hard coding" the merge field?

ciao,

[Vaughan]

Hmmm, possibly. My clients (no pun) are still on FMP 5.0 but I've got a copy of 5.5 so will try it out.

The way I'm doing it (and I've gotta acknowledge I got the technique from a post to the Forums) is to paste merge codes like <<firstname>> into a text field and use Substitite(mergetextfield, "<<firstname>>", firstnamefield). If there are a dozen different possible merge text fields then there needs to be a dozen nested Substitute() functions, one for each code. So even though the calculation is simple, it *looks* mighty complicated.

Now that I write ot out I don't think GetField() will help, but then again I haven't really learned about it or anything yet, so there is hope.

[LeCates]

Hi vaughan,

GetField() probably wont be of much value for placeholders like <<firstName>>. However, it might be interesting for placeholders like <<price>> if it is possible for there to be multiple pricing possibilities (based on promotions, tax jurisdictions, customer relationships, etc.).

So in this case the field price could be a calculated pointer to a number of other price fields.

I'm new to this forum, so I don't know what kind of play GetField() has gotten to date, but I'm fairly sure it will get a lot more attention with time. It's extremely powerful for the developer.

ciao,

[mikemccloskey]

Whoa guys. Could you back up and slow down. I am a selt taught, non professional user. I have some good knowledge but you are posting over my head. What do you mean by pasting merge codes in a text field, etc. Could you go through it step by step?

[LeCates]

Hi Mike,

The solution Vaughan described is this. Suppose you have a text field called letterTemplate.

In this field, you have the data: "Hello ##name##"

Suppose you have another field called name and in that field you have the data: "Mike".

Now, you can setup a calculation field called letterCalc

code:

---

= Substitute(letterTemplate, "##name##", name)

---

The result in the letterCalc field will be: "Hello Mike"

This isn't actually using the "merge field" function of FileMaker, but using Substitute() to give you the same capability in a calculation field. Try it out -- it's very useful.

When Vaughan presented his example, he used something like "<<first Name>>" as his placeholder. I used "##name##" above. This placeholder string can be anything that makes sense to you. Vaughan's is consistent with FileMaker's built in merge capability (for layout text), so it is probably the best way, as long as you don't get confused about which is which.

Good luck!

[mikemccloskey]

It took a couple of read-throughs, but I think I understand the structure now. Thanks for walking me through it. One other issue. If I set up these letter layouts in a file called "Letters", where each record is a new or different letter, how do I create letters for each person in the found set of my "Contacts" file? I'm not sure what Vaughn meant by,

quote:

---

"The database of letters is easy: each record has a text field with the leter/merge codes. A relationship is set up so that every record in the master database relates to the letter records."

---

[mikemccloskey]

It took a couple of read-throughs, but I think I understand the structure now. Thanks for walking me through it. One other issue. If I set up these letter layouts in a file called "Letters", where each record is a new or different letter, how do I create letters for each person in the found set of my "Contacts" file? I'm not sure what Vaughn meant by,

quote:

---

"The database of letters is easy: each record has a text field with the leter/merge codes. A relationship is set up so that every record in the master database relates to the letter records."

---

[BobWeaver]

Another tip:

When I find that my calculation fields are getting humungous (and nested Substitute calcs tend to do that) I like to break my calcs into mulitiple intermediate calculation fields so that they are easier to understand.

And, in cases like this, make them un-stored to save file space. It's unlikely that you would need to search the text of the message since all the pertinent info is available in other fields. So having them un-stored really has no disadvantages..

[soriano]

quote:

---

---

Hi I'm try to use it this method to print a letter, but I need "merge" three fields more how can I do this?, and how can the calculation field save the text format like the text field, example if in the text field is George (Name field), in the calculation show like george.

Thanks for your help.

Jorge Soriano

[Vaughan]

Substitute(Substitute(Substitute(textfield, "<<field1>>", field1), "<<field2>>", field2), "<<field3>>", field3)

Three nested Substitute() functions. Looks complicated but it's not.

My field has something like 20 or 30 in it -- I'm getting worried I'm going to hit FMP's calc field length limit. Any body know what it is? Even better, can you tell me where the "specs" for FMP are, with things like file size limit, field number limit, etc?

[soriano]

Vaughan:

Thanks a lot it's working, now how can this field (calculation) save the text format like the text field, it's possible.

Thanks again

Soriano.

[Vaughan]

Text formatting: no.

Formatting is done, more or less, on a field-by-field basis on the layout. That is, the field contains data, the layout specifies the formatting. It is possible to apply character-level formatting within fields but in this case it won't work because the data is entered into one field, while another (the calc) field is used to display the results on the layout.

If FMP was able to render HTML we'd have a limited but easy way of doing it. I'd suggest it'll be coming soon -- not that I have any inside information, but it's almost a no-brainer IMHO.

[ November 06, 2001: Message edited by: Vaughan ]

[dodge]

FMP 5.5 Dev crashes on my Mac when I try to nest more than 64 substitute functions in one calc. At first I thought I tried to substitute an illegal character but they're all enclosed in quotes, so... I guess 64 is the limit.

--

dodge

[Steven H. Blackwell]

quote:

---

Originally posted by dodge:

FMP 5.5 Dev crashes on my Mac when I try to nest more than 64 substitute functions in one calc. At first I thought I tried to substitute an illegal character but they're all enclosed in quotes, so... I guess 64 is the limit.

--

dodge

---

I am surprised that you got 64 out of it actually.

Old Advance Man

[dodge]

Why r u surprised? I could send you the calc. definition...

dodge

[ December 04, 2001: Message edited by: dodge ]