Jump to content

Layouts as record entry


This topic is 7047 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 5 months later...

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 ] )

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7047 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.