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.
Thanks Phil, I'll check it out now.
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?
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.
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.
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.
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.
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.
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. ........
Link didn't work.....
The requested URL /printouts/yzy_soft___Sample_Database.html was not found on this server.