Layouts as record entry

[kenseye]

Is it possible to create a layout as a record?

Example: table of numerous documents for merging with customer records

[bruceR]

You're going to have to explain this some more.

[Vaughan]

What you want is merged letters. It's a bit of work and there are significant limitations to the sophistication of the merged letters that it can magage. (If you expect mail merge like in Ms Word then you'll be disappointed. If your needs are modest you'll be delighted.)

Make a table (or file) where each record is a latter template, with a field for letter name and another for the letter text. The letter text contains "merge codes" that get substituted with the record data. I format the merge codes "<<FirstName>>" without the quotes, to ensure that they are different from anything the user is likely to type in the letter. (The actual format does not matter.)

Back in the main database, the user selectes a related letter record and then merges it with the current record. The optimum merge method depends on which version of FMP you are using.

With FMP 6 and earlier no formatting (character or paragraph) gets carried through calculation fields. If the letters do not need character formatting (bold, italis, fonts, size) then a calculation using nested Substitute() functions will merge the letter text instantly and on-the-fly. If you need to preserve character formatting in the letter then you need to use the Perform Find/Replace script step. It preserves character formatting, but requires the activation of a script to merge the letter. For FMP 6 and earlier there is no way to preserve paragraph formating (alignment, tabs, line spacing). Not all versions of FMP before 6 have the Perform Find/Replace script step, so old versions are plain out of luck and get no formatting at all.

If you use FMP 7 there is good news: calculations carry across character formatting, so you can use a nested Substitute function to merge letters instantly (no script required). I'm pretty sure that paragraph formatting is retained too, but I'm not sure (I'm doing a big job in FMP 6 and have not had time to bone up on FMP 7 details).

[kenseye]

Thanks Vaughan. I'll keep working on it. Maybe i'll just keep the layouts as layouts and script their use and include a sample of the document in a container field. This limits altering the documentby the user but what the heck..... it's still better than nothing... will keep working on it.

Thanks again.

[Mark L]

Hi Vaughan-

I found this posting, as I'm grappling with a similar "letter merge" issue for a Patient database. Can you explain or give an example of a nested Substitute?

(The most complex needs I think I have are:

1) A single block of text - Ex. "Welcome to the Practice" - that says "Dear xxxx, We're glad you've joined our medical group..."

2) A block of text that make reference to a date, then another block of text. Ex. "Overdue for Visit" letter - that says "Dear xxxx, According to our records, your last visit was on 12/01/04 - we'd like to see you again soon..."

But do note - that I need about a dozen different versions - albeit - pretty simple letters.)

Thanks for any insight you have in advance,

Mark

[Vaughan]

It's a process similar to merge coedes in MS Word. Fields contecnts are inserted as merge codes in some sort of standard, unambiguous format such as "<<FullName>>" without the quotes. The merge code name need not correspond with actual field names. These codes are embedded in a text field.

The magic occurs throught he use of the Substitue() function: create a calculation field with the formula

Substitute( TextField, "<<FullName>>", NameFirstLast )

...wehere there is a field in the database called NameFirstLast. The Substitute function can be nested pretty deeply to accomodate several (maybe up to 20 or 30) merge codes. For instance, to merge an Address code it would be...

Substitute( Substitute( TextField, "<<FullName>>", NameFirstLast ), "<<Address>>", AddressFull )

In FMP 6 and earlier the calculation engine lost all character and paragraph formatting, so all merged dosuments are necessarily "plain" text, very minimal. Character formatting can be retained by using the Replace() function instead of Substitute(), but it needs copy and paste and becomes layout dependent... and I have doig anything that relies on the clipboard, since you begin to muck around with the user'swork space.

The calculation engine in FMP 7 supports character formatting directly so merge letters are much more powerful, flexible and easier to implement.

[Mark L]

Vaughan-

Thanks for getting back to me.

I'm still confused as to where the Calculated field would be placed. In an attempt to do this - I have a field called Letter_Text, and the contents kind of looks like this right now:

Dear <<Patient_FirstName>>,

On behalf of the doctors and staff of the St. Mark's Dermatologists, we would like to welcome you as a new patiernt to our practice.

But at the moment - I guess because I'm not using the Substitute, I see literally what you see above.

Somehow, do I have the Merge fields function confused with this method? What's Insert "Merge Fields..."

Thanks so much in advance,

Mark

[Mark L]

OK, OK my apologies Vaughan - I got it working. So I created a second field in my Letters database - so now I have Letter_Text and Letter_TextCalc. In Letter_TextCalc I tested with Substitute ( Letter_Text ; "<<Last Lab Date>>" ; LastLabDt ) - and it worked by taking Letter_Text off my Letter layout and replacing it with Letter_TextCalc.

So now if I nest the Substitute three or four times for the three or four fields the users might be allowed use - then I'm good. Now I understand.

One, hopefully final question on this topic - what happens if the text marker is not found - nothing, I presume?

Thank you, thank you, thank you,

Mark

[Ender]

You would use your user editable text field as the "TextField" input in the calc Vaughan provided. Then use the calc in the print layout.

Note that in FM7, you can simplify the format of the substitute function a bit for multiple substitutions by using brackets:

Result Text (calculation, text result) = substitute(TextField; [ "<<FullName>>"; NameFirstLast ]; [ "<<Address>>"; AddressFull ] )

[Vaughan]

Wow Ender, that is so cool.

Thanks.

[BobWeaver]

In version 7 it can get even simpler yet:

Evaluate(Substitute(Quote(TextField);["<<";""&"];[">>";"&""]))

but this will allow the user access to any field in the database, which may not be advisable.

[Mark L]

Ender, thanks for the simplification - that will make things easier - as I have about 10 fields or so to do.

-Mark

[Mark L]

Bob-

Now that's really powerful - and an option I might consider, in that I wouldn't have to worry about maintaining the fields they could use. I could "control" users - by just telling them that a particular set of fields was available - and then if they asked for another to be added to the list - I could respond really quickly with the "update" ;-)

Thanks,

Mark

[Ender]

Nice job, Bob. I hadn't seen that before. You may have made my FieldSubstitute() custom function obsolete.

Well, one thing your calc can't do is tell the user where a syntax error is. My CF kind of does this.

But in general, I agree that this type of dynamic substitution is not advisable (for security reasons.)

[bruceR]

Now how about modifying it to use style or color instead and get rid of the <<>> stuff!

[bruceR]

Now how about modifying it to use style or color instead and get rid of the <<>> stuff!

[bruceR]

Now how about modifying it to use style or color instead and get rid of the <<>> stuff!

[Vaughan]

Sure Bruce... give it a go and post us your results!

[Vaughan]

Sure Bruce... give it a go and post us your results!

[Vaughan]

Sure Bruce... give it a go and post us your results!