Do you want to log that a letter was sent, manage the actual letter content by sending the same "form letter" with data from your solution inserted into the letter, or do you want to send individually "hand crafted" letters?
You might need more than one new table.
The thank you letter will be a form letter that will pull the donor name and info from the donor table and the accession description etc from the accessions (inventory) table
Do you need to track who was sent a letter and when?
No I don't think so. I might need to check that a letter was sent. So I would look up the accession then go to the letter layout to look at the date created or indicate on the accession layout that a letter was sent. The letter will be printed and handed to the donor, who will sign it and return it to us. We will file it. I might add a field on the letter layout that holds "date letter signed". "Who" sent it will always be the same and that info is printed on the form letter.
Then yes you do. Some approaches that you might use would not retain a copy of the letter to look up.
For hatd copy letters (not email except as attached PDF), you could set up a letter as layout text with merge fields inserting data from your solution.
But this his does not necessarily support keeping a record of who received such a letter nor the exact content of that letter. (It's pretty likely that you will decide to alter your "form text" at some point in the future and then you don't know who got the original nor who got the new version.
There's a different approach that uses a text field and the substitute function that addresses those issues. It's beyond what I care to describe via iPhone, but I'll check back later and provide that info unless someone else does so before I do.
If each donation (accession) could cause a thank you letter to be sent. (Since when does one sign and return a thani you letter?), you would need a different set of occurrences and relationships:
Inventory::Accession Number = ThankYouLetters::Accession Number
ThankYouLetters::_fkLetterID = LetterText::__pkLetterID
This sets up ThankYouLetters as the join table that you asked about. You would create a text field, FormText in Letter Text and type in your basic "form letter" text with placeholder text marking the points where data from your other tables will be inserted into it:
Thank you for donating <<Description>> (and so forth....)
Then you this calculation can merge form text and data from your solution into a message:
Substitute ( FormText ; ["<<DonorLastName>>" ; Donors::DonorLastName ] ; ["<<Description>>" ; Inventory::Description ] )
To keep a "snapshot" of the exact text sent, you can put a text field in ThankYouLetters and use either an auto-enter field option or a script to set that field to the value of the above type of substitute calculation. A Layout based on ThankYouLetters can then be used to create a given instance of the form letter.
Should you latter need to edit this letter, you can go to a layout based on LetterText and edit the content of FormText. You will not need to make any script or calculation changes unless your change requires inserting information not originally inserted into the letter.
For what you describe here, you'd have exactly one such form letter record in the LetterText table, but this design support having many different form letters each of which can be used for different reasons and for which managing their content need not be a database developer skill level task as long as the message changes don't require inserting new information.
In one of my own systems, I've been developing a "Messages" table along with a layout with buttons and scripts for editing such messages. I'm gradually transitioning the solution to move more and more email and hard copy messages into this system and will soon be training selected power users in how to edit the messages that fall within their department's area of responsibility.
Thank you very much for taking the time to answer my question. I am studying what you wrote and trying to understand it. (And to clarify, the letter started out as a sort-of thank you letter but is now a Deed of Gift Form which the donor signs acknowledging he is giving up rights to the donation.) I have been distracted with problems on my layout. I have a one (donor) to many (accessions) relationships with a check in the "allow creation of records in accessions table). What I what is to start the data entry on the accessions (child) layout. On that layout enter the donor name then click a button to go another layout where I fill in other donor contact details. Because a donor could already be in the system, I don't know how to stop the user from creating a new record for a new donor and therefore entering the donor in the system twice.
It would be better to start data entry on the donors layout. Then the user can do a find for the donor and start a new record when none is found. A portal to accessions could then be used to log new accessions or a button on the donor layout could create the new accessions record on the accessions layout and fill in the donor ID as part of the same script.
You could also set up a form to fill out consisting of only global fields. When the user clicks 'save' or 'submit' the script then checks for a donor and creates one if one does not already exist--but this "check" could be both complex and fallible.