7 Replies Latest reply on Mar 2, 2017 8:28 AM by philmodjunk

    Send one email with data from multiple records of Found Set

    AAgraphics

      Title

      Send one email with data from multiple records of Found Set

      Post

      FMP 10 MAC and PC

      I have a found set of multiple records.  The number of records in this found set will always vary.  I need to send one email that contains the data from certain fields in all of the records in the found set.  Any ideas how to do this?  Example:

       

      Let's say the found set includes:

      Record #105
      Record #109
      Record #112
      Record #138 

      Need to send one email that contains all of the following information:

      Field A, Field D, Field G from Record #105
      Field A, Field D, Field G from Record #109
      Field A, Field D, Field G from Record #112
      Field A, Field D, Field G from Record #138 

      How can this be done?  Thank you for your time and help!

        • 1. Re: Send one email with data from multiple records of Found Set
          philmodjunk

          You have options.

          1. A script can loop through the found set gathering the data into a variable. That variable can then be included as part of the calculated expression setting up the body of your email.
          2. You can use a layout that contains only fields A, D, and G. Copy All Records will then copy the data from these three fields for the current found set with tab characters between the fields and returns between the records. This can then be pasted into a field referenced in the Body of the Send Mail step.
          3. WIth a self join relationship matching to only the records comprising our hypothetical found set of records, A calculation field can combine the values of the three fields in a single field. Then you can use the List function in the calculated body of your send mail to list the data in rows as you describe here.
          • 2. Re: Send one email with data from multiple records of Found Set
            AAgraphics

            PhilModJunk

            How would you write the script for option 1?

            For option 2, is there a way where you include the field name before the information in the field?

             

            Thanks!

            • 3. Re: Send one email with data from multiple records of Found Set
              philmodjunk

              1)

              #after find is performed...
              Go To record/request/page [first]
              Loop
                 Set Variable [$List ; List ( $List ; "FieldA: " & YourTable::FieldA & " FieldD: " & YourTable::FieldD &  " FieldG: " & YourTable::FieldG ]
                 Go to record/request/page [next ; exit after last ]
              End Loop
              Send Mail...

              Note: you can use Char ( 9) in the above expression if you want to put tab characters between the pairs of field names and data in the row.

              2) You'd have to put the field names into fields. You could use calculation fields for the field names--either with quoted text or calculations that use getFieldName to return the names of the fields.

              • 4. Re: Send one email with data from multiple records of Found Set
                kallain

                Thank you Phil! I had the exact same question that AAG had, and I implimented the code you posted above with great success. 

                 

                As an aside, I've used your advice from many other postings as well. You have proven to be a very good teacher, so thanks! Laughing

                • 5. Re: Send one email with data from multiple records of Found Set
                  DianeJohnson

                  Phil,

                  As you know, I am into creating an email using a script. I currently have a script to collects records from different parts of my database and creates a pdf file that attaches to an email. The receiver does not wish it to be a PDF and would prefer all of the data be located directly in the email. So as an example, there are different sections to the email that I need to collect different records from different tables.

                  Any starting ideas on how to building this? Do you think your option 3 from above is the correct way of going about it?

                   

                  Diane

                  • 6. Re: Send one email with data from multiple records of Found Set
                    cfawcett09

                    Hi philmodjunk,

                     

                    This is an extremely helpful post, thank you so much!  My question takes this a bit further.  Lets say each of the records in the found set are associated with a user email address.  Sometimes there are 40 records for a given email address, sometimes there are 2, and sometimes there are none.  Is there a way I could set the script such that separate lists (and therefore separate emails) are generated for users?  We have anywhere from 15-30 users, and the changeover is pretty rapid so I would prefer not to write in specific email addresses into the script.

                     

                    Thanks again for all of your help!

                    • 7. Re: Send one email with data from multiple records of Found Set
                      philmodjunk

                      This is also a very Old thread. The "List of" summary field, for example, did not yet exist and I forget whether ExecuteSQL also did not exist or if it was so new that I hadn't started using it yet.

                       

                      Yes, you can set up a looping script that sends out a different email for each user and the email address can be taken from the current record.

                       

                      It might be simplest if you had a related table with one record for each email address that linked to the records that make up these lists, but the GetSummary function used in conjuction with the "list of" summary field should be able to generate lists of data to include in the body of an email. The Getsummary function gives you a "sub total" result like you get when you place a summary field into a sub summary part.

                       

                      And ExecuteSQL can be very useful for this type of list building as it not only can reference specific groups of records, but gives you the ability to specify what text separates each field in a given row of data in your list at the same time.