6 Replies Latest reply on Mar 20, 2010 10:05 AM by Kobyashi

    Mail merge salutations

    Kobyashi

      Title

      Mail merge salutations

      Post

      I'm using FM pro 10 Advanced and can't seem to think of a solution to a small problem.

       

      I have a table of properties and then a table with tenants who live in those properties. The tenants are linked to the properties via a table of tenancy agreements.

       

      To recap, each property could have one or more tenants but each tenant will have his or her own tenant record. The tenant records will share the agreement record ID and the properties agreement will contain the property record ID.

       

      When I'm doing mail merges I create a layout with my documents using the agreement table. My problem is that whilst I can list the tenants in a property by adding a portal and whilst this is ideal is most situations, I cannot see a way of adding all tenants to the letter address or combine the salutations for all the tenants in a property.

       

      By way of an example If property record 1 is has two tenants Mr Joe Smith (tenant record 1) and Mrs Jane Smith tenant record 2), I will want the address in the letter I have to say Mr Joe Smith and Mrs Jane Smith, followed by the address. Furthermore, each tenant has a salutation calculated from the tenant information. In the case above it would address the letter and start as Dear Joe or Dear Jane.  My merge produces one letter per property and will use the first tenant it finds linked to that property but I want it to use both (or as many as in the property). Likewise I want it to use all the salutations for these tenants so instead of producing one letter with Dear Joe I want it to say Dear Joe and Jane.

       

      How do I do this?

        • 1. Re: Mail merge salutations
          mrvodka

          You could go the to the related records for the tenants (Go to Related Records ) that are linked to the agreement record. Then you can gather the information with a loop. Another option would be to use a calculation from the agreement record using List (  ). I think in your case though I may choose using the loop from the tenants layout.

          • 2. Re: Mail merge salutations
            Kobyashi

            Being made aware of the List() function has helped me loads, thanks very much. I'm interested in the go to related record solution though seeing as that was your preferred solution for this.

             

            Obviously your suggested a scripted solution but how does that work with a mail merge? I mean, it might be perfectly acceptable and but If I have 100 or so letters to print in a found set, how would I implement a script? Would a script trigger set for when the layout loads work here? I'm thinking it would need to recalulate/run the script 100 times for 100 letters. 

            • 3. Re: Mail merge salutations
              mrvodka

              You could have your scripe Loop through the records and send the emails.

              • 4. Re: Mail merge salutations
                Kobyashi

                Yeah I guess. For the moment i've used the List() function for the address field and the below in a calculated field for the salutation.

                You helped me a lot, I'm really grateful to you.

                 

                 

                This works for two tenants which at the moment is the maximum number of tenants we have in a single property.

                 

                If (ValueCount(List(tenants::salut)) >1;
                Replace ( List(tenants::salut) ; Position(List(tenants::salut) ;"¶";1;1) ; 1 ; " & " );
                Trim(tenants::salut))

                • 5. Re: Mail merge salutations
                  mrvodka

                  How about:   Substitute ( List ( tenants::salut ) ; ¶; " and " )

                  • 6. Re: Mail merge salutations
                    Kobyashi

                    You're a genius