kenseye Posted September 21, 2004 Share Posted September 21, 2004 Is it possible to create a layout as a record? Example: table of numerous documents for merging with customer records Link to comment Share on other sites More sharing options...
bruceR Posted September 21, 2004 Share Posted September 21, 2004 You're going to have to explain this some more. Link to comment Share on other sites More sharing options...
Vaughan Posted September 21, 2004 Share Posted September 21, 2004 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 More sharing options...
kenseye Posted September 21, 2004 Author Share Posted September 21, 2004 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 More sharing options...
Mark L Posted March 4, 2005 Share Posted March 4, 2005 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 More sharing options...
Vaughan Posted March 6, 2005 Share Posted March 6, 2005 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 More sharing options...
Mark L Posted March 6, 2005 Share Posted March 6, 2005 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 More sharing options...
Mark L Posted March 6, 2005 Share Posted March 6, 2005 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 More sharing options...
Ender Posted March 6, 2005 Share Posted March 6, 2005 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 More sharing options...
Vaughan Posted March 7, 2005 Share Posted March 7, 2005 Wow Ender, that is so cool. Thanks. Link to comment Share on other sites More sharing options...
BobWeaver Posted March 7, 2005 Share Posted March 7, 2005 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. Link to comment Share on other sites More sharing options...
Mark L Posted March 7, 2005 Share Posted March 7, 2005 Ender, thanks for the simplification - that will make things easier - as I have about 10 fields or so to do. -Mark Link to comment Share on other sites More sharing options...
Mark L Posted March 7, 2005 Share Posted March 7, 2005 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 More sharing options...
Ender Posted March 7, 2005 Share Posted March 7, 2005 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.) Link to comment Share on other sites More sharing options...
bruceR Posted March 7, 2005 Share Posted March 7, 2005 Now how about modifying it to use style or color instead and get rid of the <<>> stuff! Link to comment Share on other sites More sharing options...
bruceR Posted March 7, 2005 Share Posted March 7, 2005 Now how about modifying it to use style or color instead and get rid of the <<>> stuff! Link to comment Share on other sites More sharing options...
bruceR Posted March 7, 2005 Share Posted March 7, 2005 Now how about modifying it to use style or color instead and get rid of the <<>> stuff! Link to comment Share on other sites More sharing options...
Vaughan Posted March 8, 2005 Share Posted March 8, 2005 Sure Bruce... give it a go and post us your results! Link to comment Share on other sites More sharing options...
Vaughan Posted March 8, 2005 Share Posted March 8, 2005 Sure Bruce... give it a go and post us your results! Link to comment Share on other sites More sharing options...
Vaughan Posted March 8, 2005 Share Posted March 8, 2005 Sure Bruce... give it a go and post us your results! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7117 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 accountSign in
Already have an account? Sign in here.
Sign In Now