Include data(more than one row and column) into Email body

Toad Data Point
1

Hi, I have an automatic job that generates an excel file and includes the attachment into each email address found in the query. From this task, I want to eliminate the task of sending the attachment and send the **data directly into the body of the email. **

Sample of data:
Week_Ending RegHrs Missing Over_Booked Email
02-27-11 30 10 M@
03-06-11 32 8 M@…
03-13-11 55 15 M@…
03-06-11 41 8 1 P@…

I tried the email derivation but I do not know how to group the records before sending the email. I do not want to send data by rows.
If there is more than one row in the file for the same email address, I want to get all the data related to that user and send only one email with all the correlated rows and columns.
I am using TDA ver2.7

Thanks in advance for any help to this task.

2

Sounds like you need to use the Loop Dataset activity.

You would need to execute a query that deterimines who the separate emails would go to. Then for each row in that set execute your report query filtered by that user name(use a bind variable for this). This would give you a report only for that user that can be embedded in the body of the email.

See this Blog posting. There is an example file in the section “putting it altogether”. It shows using a loopdataset with email.

http://www.toadworld.com/BLOGS/tabid/67/EntryId/498/Automation-Variables-Automation-variables-using-datasets.aspx

Let me know if this was not enough to answer your question.

Debbie

3

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.
AutomaticTasks.mdb (276 KB)

4

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.
TimeSheet_Email.tas (28.5 KB)

5

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.
EMAIL_ADDRESSES-ForTimeSheet.sql (230 Bytes)

6

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.
FormLetter.txt (136 Bytes)

7

Thanks Debbie for your quick replied; the explanation was great however, I still cannot acheive my results of adding all rows for the same user in the same email. I am including my task, hope you could point what I am doing wrong. Thanks a lot.
GetJobsPerEmail-ForTimeSheet.sql (634 Bytes)

8

It seems to me you are almost there. You only need to change your GetJobsPerEmail-ForTimeSheet.sql to give you the sum of the values for each employee. I made a real simple example and have attached it.

Debbie
EmployeeHours.zip (16.6 KB)

9

P.S. Please my sample files in C:\Temp to run. Also add a connection to the access database I included.

Debbie

10

Thanks Debbie, but unfortunatelly, I could not open the database. I am still using MS Accces 2003. Anyway, I am going to try by changing my second sql to get the sum of hrs. I Hope soon I will get the results I need per user which shoud include all the weeks with the correlated totals where the employee has not report hours. In case you want to look again at my task, I attached my mdb with the rest of files I included yesterday. Thanks Debbie.

11

, TimeSheet-Step2-Finance.EMP_NO

, TimeSheet-Step2-Finance.Name

, SUM(TimeSheet-Step2-Finance.Over_Booked) AS Over Booked

FROM C:\TOAD\DatabasesAutomaticTasks\AutomaticTasks.mdb.TimeSheet-Step2-Finance TimeSheet-Step2-Finance

WHERE (TimeSheet-Step2-Finance.Email_Address = :EMAIL)

, TimeSheet-Step2-Finance.EMP_NO

, TimeSheet-Step2-Finance.Name

ORDER BY TimeSheet-Step2-Finance.Name ASC

I think you need to leave the week ending dates out of the results. If you use thise query you get one row per person of the sume of the three values hours worked, missing hours, and over booked.

SELECT TimeSheet-Step2-Finance.Service Center, SUM(TimeSheet-Step2-Finance.Hrs_Worked) AS Hrs Worked , SUM(TimeSheet-Step2-Finance.Missing_Hours) AS Missing Hours GROUP BY TimeSheet-Step2-Finance.Service Center
Hours.png

12

Thanks Dabbie for your time, but I cannot ignore the weeks because I need to point to each employee the week where they are missing hours.The system where employees report labour can be selected by week endings. Anyway, it seems what I wanted to do is not feasible at this point. Hope you can consider my request for future enhancements because if the data that needs to be forward to the employee is less than 5 lines, then for quickly access to put this data into the email body is faster than to do extra clickings to open the attachment.

13

I can enter an an enhancement but I am still not certain what the output is that you want. Using the sample data at the top of this post can you show me what you want the output to look like? PPerhaps a sample of one complete letter. From this I can see how to proceed.

Debbie

14

Hi Debbie,

Please find attached a sample of the data I would like to include on the email body that is generated for each user.Thanks
TimeSheetTemplate.rtf (5.66 KB)

15

I must be having a "blond moment". I totally forgot that I have already coded an enhancement for this.

Here is how you do it:

  1. Use the Select to File activity and export your employee hour data to a csv file.
  2. Then use the Find and Replace activity and use the Replace file option. This will insert the contents of the CSV file into the body of your text file.
  3. You will need to then use Find and Replace to handle the formating. IE: replace commas with spacses, etc.

I have one user that does this with carriage returns and all kinds of formatting. Give this a try or let me know if you need a sample.

Debbie

P.S. I actually am a blond

FormLetter.png
FormLetter.png592×579 16 KB

16

Thanks a lot Debbie for the good news. I am so happy to have the option to send more simple emails to users.
I added the activity and saved the file as CSV. I used the replace activity and now I can see the text of my file into the email body; of course all data needs to be formatted but that is secondary. For now I am more worried about finding out what I am doing wrong because the body of the email is showing the csv file but not the format that is on the text file template. An example will be greatly appreciated. Thanks a lot Debbie. TDA is fantastic.

17

Okay, I have sample files attached. I had to do a couple of things to accomplish what you need.

  1. Instead of using Select to file to export to csv, make an Export Wizard template. You need to export to csv without headers. I put the column headers in the template file. This made formatting easier.

  2. Use a token, such as #TABLE# and insert the csv file into the body of the template.

  3. Then replace all commas with two tabs. The Find and replace will take nonvisual characters. To do this you need to invoke the drop drown for text values. this is actually a richtext edit area. To enter a Tab press the ctrl and tab. Then use the enter key to close.

The email body now looks like this.

Dear Monica,

Please review your labour entries because hours are either missing or over booked.

Week_Ending Reg_Hrs Miissing_Hrs Over_Booked

03/06/2011 12 20

03/13/2011 8 32

**The above total hours per week ending includes vacation and statutory holidays in any valid status. **

Pretty slick, hugh?

Debbie
Sample.zip (4.79 KB)

18

Your job is amazing. Thanks a lot Debbie. TDA is wonderful.