1 2 Previous Next 16 Replies Latest reply on Dec 29, 2013 8:16 AM by philmodjunk

    Editable Form Letters



      Editable Form Letters



           I have letters on my database which for the most part are standard and the same text but occasionally I have to change the text on a particular one and don't want to have to recreate the form.  So I was trying to do something with the letters being on tables and using lookups.  I have the following tables/Fields...

           Clients: ClientID, amongst a bunch of others, name, address,etc.

           Letters:  LetterID, LetterTitle, LetterText

           Client_Letters:  ClientID, LetterTitle, LetterText, LetterID

           The Clients table and Client_Letters table are related by the ClientID.  The Letters table and Client_Letters table are related by LetterID.  On the Client_Letters table the field LetterText is a lookup field.  So when they select the letter title it fills this in.  I've got those basic parts working well.  

           What I'm not sure how to get working is (because the couple of ways I have tried are unsuccessful) is: If i'm on a layout based on the Clients table, and press a button bringing me to the layout based on Client_Letters and choose the letter I want, how do I get the merge fields to work.  The body is set up with various merge fields from the Clients table.  It shows the merge fields in the body but it doesn't actually perform the merge when I preview it.  

           Obviously I've missed a step but I'm not sure what.  


        • 1. Re: Editable Form Letters

               You might find the method used in this demo file useful: https://www.dropbox.com/s/6xw8buafjuohncu/MessageTemplateBuilder.fp7

               If you are using FileMaker 12 or newer, open this file from the File menu to get a copy converted to the newer file format.

          • 2. Re: Editable Form Letters

                 Thanks Phil, I'll check it out now.  

            • 3. Re: Editable Form Letters

                   Ok....I see the way you have the tables set up similar to how I described, but unless I'm not understanding this properly, to edit the letter they are having to edit the template?  Which then edits it for all future uses as well no?

              • 4. Re: Editable Form Letters

                     Yes, but you can duplicate a record and edit the duplicate to create a new one. wink

                • 5. Re: Editable Form Letters

                       Yes but then I could end up with hundreds of templates all changing only a sentence or two.  I'm trying to figure out how to start from the template and only change for add a sentence or two for a record not add more templates.

                  • 6. Re: Editable Form Letters

                         A looked up value field option should then be possible to copy the template text into a different table--so long as your calculation field used to substitute placeholder text with data is also set to work from the field in this table instead of the template table. This will require setting up the correct data references to clients from the context of Client_Letters.

                    • 7. Re: Editable Form Letters

                           I tried altering what you had in the example you sent slightly

                           My lookup is working, and it allows me to alter the text which is great.  

                           But where I had merge fields it doesn't replace those with the client info.  So i thought creating a calculation field like you had in your example database, replacing text i had in between the << >> with fields from the client table would work....but despite the two tables being related by the client ID it tells me i can't used unrelated info unless it's global fields.

                      • 8. Re: Editable Form Letters

                             Which means that either you don't have the correct relationship set up or you are referencing fields from the wrong Tutorial: What are Table Occurrences?.

                        • 9. Re: Editable Form Letters

                               Yes, stupid me....it was the "evaluate from context of " which was the wrong table occurrence.  frown

                          • 10. Re: Editable Form Letters

                                 Phil please help me figure out what I'm doing wrong.  How I currently have the tables related it doesn't insert the correct record information.  

                                 In addition to the table mentioned above I have the following:





                                 (Additional EOC Table Occurrence ) EOCLetters::EOCID=Clients_Letters::EOCIDfk

                                 (Additional Service Table Occurrence) ServiceLetters::ServiceID=Clients_Letters::ServiceLetters::ServiceIDfk

                                 (Additional StaffActivity Table Occurrence) StaffActivityLetters::StaffActivityID=Clients_Letters::StaffActivityIDfk

                                 So basically a client can have multiple EOC's, an EOC multiple Services, so on and so on.  

                                 What I am trying to do is when on a layout based on one of those tables, have a button that brings me to a letter layout based on Client_Letters, and show information for the related records.  For example if on the Service table layout and going to the letter layout it show info from that particular service, with the EOC it was related to and the client it was about.  But as it is set right now it shows the first record but not any additional records created after that.  I had created a letter based on an EOC, but when I created a second EOC for that client and go to the letter its showing the info for the first one, despite that I have a script bringing me to the letter and pasting the EOC ID into the EOC field on the CLients_Letters table.  I just don't know where to start, are my relationships wrong, or how I am scripting it is wrong.

                            • 11. Re: Editable Form Letters

                                   Let's first focus on one key set of tables/relationships:


                                   Clients::ClientID = Clients_Letters::ClientIDfk
                                   ServiceLetters::ServiceID = Clients_Letters::ServiceIDfk

                                   This is recreated from your previous post and describes a classic "many to many" relationship implemented with Clients_Letters serving as a "join" table between Clients and ServiceLetters. I am assuming that you have one record in ServiceLetters for each "standard letter" from which you wish to select, copy and customize for a given client. A Client can be sent many different letters and a given ServiceLetter can be customized and sent to many different clients.

                                   That make sense?

                                   If you define a text field in Clients_Letters, you can use either an auto-enter calculation or the Looked up Value auto-enter field option to copy the contents of a text field in ServiceLetters into that text field when you select/enter the correct ID value into Clients_LettersIDfk. This field can be set up with a drop down list of Service letters and their ID's or more sophisticated designs may be implemented for selecting a service letter and entering it's ID into the fk field in Clients_Letters.

                                   Does that part work for you yet?

                                   From there, you may have issues to resolve for accessing data in other tables in order to include them in the form letter. There are numerous options for linking things correctly such that a layout based on Service letters has access to all the correct data so that the text substitution can work to replace the "place holder text" wth data from related records.

                                   I suspect that this latter part of the process is one of the places where you are currently having trouble.

                                   You appear to have linked in occurrences of EOC and StaffActivity to fk fields in the join table. This looks correct, but it means that you need to select a ServiceID, EOCID and StaffActivityID in the Service_Letters fk fields before you have the needed links in place in order for your form letter to incorporate data from those tables. These can also be set up with drop down lists or more sophisticated methods for finding and linking in records from these tables.

                              • 12. Re: Editable Form Letters

                                     Forgive me, I'm not repeating myself for you but more myself to make sure I'm explaining, AND doing things properly.  

                                     I think I have done my table occurrences wrong because what your interpreting as service letters is different from what it is, I think.  

                                     I have a letters table which is all my standard letter templates where my lookup is getting the info.  Then I created a Join Table, Client_Letters,  to the clients to show all the letters to a client and this has the lookup field on it.  On this table I have two fields, one is the lookup showing the letter where I have placeholder, and another field which is a calculation and I have it substituting the placeholders for the field from the tables.  

                                     Clients --> EOC -->  Service -->  Staff Activity  ....all related to the table before it by a fk.  

                                     so because those tables already have a relationship, when I went to create another one with each of their pk's to the Clients_Letters table, I named the second table occurrence EOCLetters, ServiceLetters, StaffActivityLetters.  I presume I've done the correct table occurrences??  Maybe not.  

                                     So a client can have many EOC's, an EOC can have many Services, and Service many Staff Activity.  So the one record for each letter is going to be in the clients_Letters table....i think.

                                     I think I could be in over my head here because I would need letters that require info from more than one table at a time which is where the trouble is, it may need to reference the record in EOC and Service in the one letter for a client.  ........  frown


                                • 13. Re: Editable Form Letters

                                       You might find it useful to examine this free template, designed to generate pretty letters and reports.


                                  • 14. Re: Editable Form Letters

                                         Link didn't work.....


                                    The requested URL /printouts/yzy_soft___Sample_Database.html was not found on this server.

                                    1 2 Previous Next