4 Replies Latest reply on Feb 21, 2017 3:43 AM by arjen.evertse

    Include Multiple Addresses for Group Form Letter

    mvangieson10

      I'm new to Filemaker and I am trying to figure out how to create a form letter that I can address to multiple contacts instructing them to send information to another contact. I am working with three tables: Projects (Main Table), Role Players (Related to Projects), and Contacts (Related to Role Players).

       

      An example of what I want to generate would be something like below:

       

      Role Player 1                              Role Player 2                    Role etc.

      [Address Block]                          [Address Block]

       

      Dear ______:

       

      Please send all bills:

       

                Role Player - 3

                [Address Block]

       

      Who I address and who is to billed changes throughout a project. I first started with the layout context over Role Player and that was great to write information to the Role Player, but I couldn't figure how to pull information in from other Role Players.

       

      Then I moved the context over Projects and created a popover at the top with a portal window that pulled in all project related role players into the portal. In that portal I created a column to check in each row to include the contact in the address block that field was generated in role player table. I can check the include box. But then I don't know what script to write to filter the selected group, copy each address block and paste that into the GroupAddress Field.

       

      I want something like the List function but I want it to be filtered based on the selections the user makes in the popover.

       

      Then I want to be able to put a return between the each returned result. I'm looking at loops and if statements and creating extra fields, but just not sure how to get it done.  And would love to receive some directions or guidance. Thanks.

        • 1. Re: Include Multiple Addresses for Group Form Letter
          arjen.evertse

          Do you want to send the letters to the Role Players or to the Contacts of each involved Role Player? Your description is a bit confusing.

           

          In Projects you could add a foreign key of the Role Player to be billed. Then one TO of Role Players related to this field gives you the Role Player to bill. Another TO of Role Players with multi condition relation matching Projects key and not matching Role Player to be billed key gives you all Role Players except the one to be billed from the context of the Project.

           

          Do you want to have all addressed on one and the same document? Do you really need them to be displayed next to each other? Do you have a max number of Role Players or can they potentionally be unlimited? These are questions that will give more insight on the best approach for your lay-out.

          • 2. Re: Include Multiple Addresses for Group Form Letter
            mvangieson10

            Thanks for the prompt response!

            Do you want to send the letters to the Role Players or to the Contacts of each involved Role Player?

            Role Player table is a join table between Projects and Contacts.  The role player table lets me assign each contact a role and give them the unique identifying number.  The role player table does not have many contacts. So the address information that I am pulling is coming from the Contacts Table.

             

            Do you want to have all addressed on one and the same document? Do you really need them to be displayed next to each other? Do you have a max number of Role Players or can they potentionally be unlimited? These are questions that will give more insight on the best approach for your lay-out.

            Yes I want all addresses on the same document. I'll be sending a letter to two or more people from the group of selected contacts as narrowed by related Project Role Players.

             

            Displayed next to each other or below each other works.

             

            The number of Role Players is dynamic and varies as the project goes along. I just want to be able to select two or more and from that selection and have the address block of them populate. So there might 10 role players - one is the primary person to process bills, another the secondary after the limits of the 1st end, etc.

             

            I want to send in a single letter to 2 or 3 role players a letter updating them who is the primary party to bill.

             

            I hope that clarifies it better. 

            • 3. Re: Include Multiple Addresses for Group Form Letter
              philmodjunk

              "I want something like the List function but I want it to be filtered based on the selections the user makes in the popover."

               

              That sounds like something that you could do with ExecuteSQL().

               

              Marking a selection for this purpose should either set a field in the join table or update a list in a global variable.

              • 4. Re: Include Multiple Addresses for Group Form Letter
                arjen.evertse

                You will need to account for the fact that you never know how many addresses will be selected.

                 

                The options you have:

                 

                1: Populate a field of selected addresses using ExecuteSQL and place the result in a global field on your lay-out. You can make the field very high and use Sliding and Resize options to shrink the field to it's content when printing. Downside, you might eventually have more addresses than you field height accounts for and something will be cropped. Shrinking does exactly that, it does not make the field bigger.

                 

                2: Grap the ID's of the selected addresses in a multikey globab field in the Project table. A TO from this field to Role Players will give you a relation to all selected addresses.

                 

                This can be used in a portal with the same Sliding and Resize options used, but the downside is the same. If you have more addresses selected than the number of portal rows accounts for it will not show all of them when printing.

                 

                You could also use this relation to do a GTTR to a print lay-out based on the Role Player records having the address in the body and Project related information in a trailing grand summary or footer. This will adopt for any amount of addresses selected as each selected address now has one record (body).