7 Replies Latest reply on Aug 6, 2013 2:50 PM by EoinWhite

    Send One Letter to Multiple Recipients

    EoinWhite

      Title

      Send One Letter to Multiple Recipients

      Post

           Hi there,

           I have a database with a Contacts Table, a Letters Table and a Letter Layout.  I would like to create a letter record in my Letter Table and print a copy for multiple records in my Contacts Table using the Letter Layout.

           My contacts have references that identify different groups, e.g. contacts from Coca-Cola would have a reference COCA001, COCA002, COCA003, and so on. I have a button script that performs a find then prints the letters, but I’m unable to figure out what the find request between the two tables should be.  I’ve tried several combinations using variables etc. but have only managed to print 1 record or unable to find any records.

           I would also like it to be possible to send individual letters using these tables and layouts so that all letter records are stored in one place.

           Fields that I am using to perform find requests are:

           Company Reference: e.g. COCA

           Individual Reference: e.g. COCA001

           Some help would be greatly appreciated!!!!!!

        • 1. Re: Send One Letter to Multiple Recipients
          philmodjunk

               I cannot tell from your post what tables, fields and relationships are invovled. Nor have you described exactly what steps you attempted to perform the find--not even if you performed a manual find or a scripted find.

               I suspect the problem lies in your relationships. A contact can be sent many different letters and a letter can be sent to many different contacts. This suggests that you need to set up a Many to Many relationship.

               Contacts-----<Contact_Letter>-----Letters

               Contacts::__pkContactID = Contact_Letter::_fkContactID
               Letters::__pkLetterID = Contact_Letter::_fkLetterID

               You can place a portal to Contact_Letter on the Contacts layout to list and select a Letters record for each given Contacts record. Fields from Letters can be included in the Portal to show additional info about each selected Letters record and the _fkLetterID field can be set up with a value list for selecting Letters records by their ID field. You'd then print your letters using a layout based on the Contact_Letter layout where you'd combine fields from Contacts and Letters on the same layout.

               The exact criteria that you'd use to pull up the needed found set of Contact_Letter records will depend on how you need to manage this process. Does a given letter get printed and mailed only once to a specific set of letters or might a letter be sent to recipients ABC today and then be sent again to recipients xyz a month later? If a letter is printed and sent only once, Go To Related Records from a Letters layout or performing a find for the LetterID in the Contact_Letter::_fkLetterID field will find the needed records. If a given record is sent more than once, you'll need a date field in contact_letter to record when the letter is sent and then you would include that date as additional criteria.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Send One Letter to Multiple Recipients
            EoinWhite

                 Thank you PhilModJunk. I've implement the many to many relationship and I've go it half working. I've spent all day on it and now my head hurts. I'll fill you in on more details in a few day after I have given my head a rest. Just wanted to come on and say thanks for your input. Don't no why I didn't think of a many yo many. Doesn't pay to work late into the night on databases sometimes!!!

            • 3. Re: Send One Letter to Multiple Recipients
              philmodjunk
                   

                        Doesn't pay to work late into the night on databases sometimes!!!

                   Yawn.... yes I totally agree! wink

              • 4. Re: Send One Letter to Multiple Recipients
                EoinWhite

                     Ok. I have setup the many to many relationship:

                     Contacts-----<LettersContacts>-----Letters

                     Contacts::Contact_ref = LetterContacts::Contact_ref
                     Letters::Letters_id = CLetterContacts::Letters_id

                     In the Letters layout I have placed a portal to LettersContacts using the 'Contact_ref' field.  This allows me to select individual contacts whos details display on the LettersContacts layout (A4 letterhead).  This works fine, but.....

                     The trouble comes when I need to send the same letter to every contact from a particular company.  I have grouped my contacts by giving them references (Contact_ref) based on the first 4 letters of there company name. e.g. contacts from 'The John Doe Co.' would be JOHN001, JOHN002, JOHN003 etc.  I could select each contact from the portal but there could be 100 contacts in a company.  That would be very tedious and leaves room for error. 

                     I have tried a different option by adding a field for 'Contact_ref' to the Letters layout.  I then created a button to perform the follow script:

                      

                     Set Variable [$ContactReference; Value:Letters::Contact_ref]

                     Go to Layout ["Letter Printout" (Contacts)]

                     Perfom Find (Find Records      Contacts::Contacts_ref: [$ContactReference"]

                     Go to Related Record [From table: "LettersContacts"; Using layout: "Letter Printout" (LettersContacts)]

                     Print...

                      

                     The Perfom Find allows me to enter JOHN002 to the 'Contact_ref' field for a letter to a single individual, or simply JOHN to find all contact references beginning with JOHN.  This will create a letter for each found contact and display the contact details, but does not display any info from the Letters table... basically multiple blank addressed letters.  I would prefer to get this second option to work as I think it is cleaner/tidier than the portal option as you only need to entre data to one field.  Any ideas would be greatly welcomed.

                     P.S. In the Letters layout, I have tried adding a portal to LettersContacts using a 'Company_ref' field (first 4 letters of 'Contact_ref') with no success.

                • 5. Re: Send One Letter to Multiple Recipients
                  philmodjunk

                       WIth your given script, once you have pulled up a found set of contacts, you can set a script to loop through them and create one matching record in LetterContacts that links them to the appropriate record in Letters. Use a variable to capture the ID of the Letter and another to capture the ID of the contact so that it can switch to a LetterContact layout, create the record and copy over the ID's from the two variables so that the new record is correclty linked to a contact and a letter.

                       You might also find it useful to add a groups table and link contacts to a common record in Groups when they are all part of a group. That can simplify and make more user friendly the process of selecting a group and using a group to find the contacts that are members of that group. And with a join table, you can even set it up so that contacts can be members of more than one group.

                  • 6. Re: Send One Letter to Multiple Recipients
                    EoinWhite

                         Thanks! I really apprecitate this!!!!!!!

                         I have created the following Loop based on the Find Script as mentioned before:

                          

                         Set Variable [$ContactReference; Value:Letters::Contact_ref]

                         Go to Layout ["Contacts List" (Contacts)]

                         Perfom Find (Find Records      Contacts::Contacts_ref: [$ContactReference"]

                         Go to Related Record [From table: "LettersContacts"; Using layout: "Letter Printout" (LettersContacts)]

                         Go to Record/Request/Page [First]

                         Loop

                              Set Variable 1 (Pulls Contact Reference from Contacts Table)

                              Set Variable 2 (Pulls Letter ID from Letters Table)

                              Go to Layout ["Letter Printout" (LettersContacts)]

                              Set Field 1 (Enters value from Set Variable 1 above to LettersContacts::Contacts_ref)

                              Set Field 2 (Enters value from Set Variable 2 above to LettersContacts::Letter_id)

                              Go to Layout ["Contacts List" (Contacts)]

                              Go to Record/Request/Page [Next; Exit after last]

                         End Loop

                          

                         This loop copies the Contact_ref fine, but as its pulling all the data from a find in a Contacts layout it does not copy the Letters_id as they are not present in the Contacts table.  Do I need to add another loop, or another loop within this loop, using a Letters layout?  How woudl this look/work?

                         Again, thank you for all your time!

                    • 7. Re: Send One Letter to Multiple Recipients
                      EoinWhite

                           Phew! Forget my last post... I answered my own question in it.  I have updated the loop above to the following:

                            

                           Loop

                                Set Variable 1 (Pulls Contact Reference from Contacts Table)

                                Go to Layout ["Letter Printout" (LettersContacts)]

                                New Record/Request (This was in previous Loop but I excluded it by mistake!)

                                Set Field 1 (Enters value from Set Variable 1 above to LettersContacts::Contacts_ref)

                                Go to Layout ["Letters" (Letters)]

                                Set Variable 2 (Pulls Letter ID from Letters Table)

                                Go to Layout ["Letter Printout" (LettersContacts)]

                                Set Field 2 (Enters value from Set Variable 2 above to LettersContacts::Letter_id)

                                Go to Layout ["Contacts List" (Contacts)]

                                Go to Record/Request/Page [Next; Exit after last]

                           End Loop

                            

                           A big THANK YOU to you again for your time and help.  I hope to return the favour some day.....