You have options.
- 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.
- 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.
- 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.
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?
#after find is performed...
Go To record/request/page [first]
Set Variable [$List ; List ( $List ; "FieldA: " & YourTable::FieldA & " FieldD: " & YourTable::FieldD & " FieldG: " & YourTable::FieldG ]
Go to record/request/page [next ; exit after last ]
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.
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?
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!
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.