Derive email destination from database query?

julian.chasin_625 · September 5, 2009, 12:41pm

Using TDA automation features, is it possible to distribute reports via email based on values found in database tables?

Debbie_Peabody · September 9, 2009, 8:40pm

Yes. You can do this with version 2.5 that is now available. Automation can use variables in this release and can set these variables from the values in a table. Let me know when you have this installed and I can build you an example automation script that does this.

Debbie

julian.chasin_625 · September 10, 2009, 10:16am

Debbie, I have just upgraded from 2.1, so I would appreciate your guidance on this.
Thanks,
Julian

Debbie_Peabody · September 10, 2009, 3:03pm

I have attached some sample files on how use email addresses from a table. Here are the steps:

I have included two sql files. One creates a table (EMAIL_ADDRESSES) and inserts values for two email addresses. The second file does a select from this table.
For the rest of these steps please open up and refer to the Automation script called ‘Script_Email_Test.tas’.
First add a ‘Set Variable’ activity to hold a single email address. Give it a name, I used EMAIL_ADDRESS. Initialize it to a string such as ‘EMPTY’. (See the Set_Variable_1 activity in script)
Add a Execute Script activity. This activity will execute a SQL statement and keep the results in a variable that can be used in the rest of the script. I set this activity to the EMAIL_QUERY.sql in the attachement. I called the result variable EMAIL_TABLE. See Execute_1 activity.
Add a ‘Loop Dataset’ activity and set it to the result set you named in step 4. (EMAIL_TABLE). This activity will execute your steps you add for every row of EMAIL_TABLE.
You need to set your variable created in step 3 to the current row value. To do this, add a ‘Set Variable’ activity. Choose the name of your variable from the drop down(EMAIL_ADDRESS). Use the ‘#’ symbol to de-reference the value of the column. So to set the value of this variable to the current content use ‘#EMAIL_TABLE.EMAIL#’. See ‘Set_Variable_2’ in sample script.
Add whatever other activities you want to execute for every email address in your table.
Add your email acitivity. In the To; attribute de-reference your variable. IE: ‘#EMAIL_ADDRESS#’. See ‘Email_1’ activity in sample script.

Give this a try and if you have any issues please attach a screen shot and zip up all of your files for me to review. include the *tas file and any error log.

Have fun!

Debbie
TDA_AUTO_EMAIL.zip (1.89 KB)

Debbie_Peabody · September 10, 2009, 3:03pm

I have attached some sample files on how use email addresses from a table. Here are the steps:

I have included two sql files. One creates a table (EMAIL_ADDRESSES) and inserts values for two email addresses. The second file does a select from this table.
For the rest of these steps please open up and refer to the Automation script called 'Script_Email_Test.tas'.
First add a 'Set Variable' activity to hold a single email address. Give it a name, I used EMAIL_ADDRESS. Initialize it to a string such as 'EMPTY'. (See the Set_Variable_1 activity in script)
Add a Execute Script activity. This activity will execute a SQL statement and keep the results in a variable that can be used in the rest of the script. I set this activity to the EMAIL_QUERY.sql in the attachement. I called the result variable EMAIL_TABLE. See Execute_1 activity.
Add a 'Loop Dataset' activity and set it to the result set you named in step 4. (EMAIL_TABLE). This activity will execute your steps you add for every row of EMAIL_TABLE.
You need to set your variable created in step 3 to the current row value. To do this, add a 'Set Variable' activity. Choose the name of your variable from the drop down(EMAIL_ADDRESS). Use the '#' symbol to de-reference the value of the column. So to set the value of this variable to the current content use '#EMAIL_TABLE.EMAIL#'. See 'Set_Variable_2' in sample script.
Add whatever other activities you want to execute for every email address in your table.
Add your email acitivity. In the To; attribute de-reference your variable. IE: '#EMAIL_ADDRESS#'. See 'Email_1' activity in sample script.

Give this a try and if you have any issues please attach a screen shot and zip up all of your files for me to review. include the *tas file and any error log.

Have fun!

Debbie

julian.chasin_625 · September 14, 2009, 4:34pm

See attached; (original text lost in malfunctioning Quest browser)

Debbie_Peabody · September 14, 2009, 6:49pm

Yes. You can do this.
First make a text file to be used as a email template. In the body put some symbol or text you can recognize to do a find/replace. You will need to keep your values in separate variables. Once you have this, use the find/replace activity to find and replace the tokens with the variables values and copy to a unique file name. Then in the Email activity use the last attibute which is ‘Use file for body’. This will use your edited template for the body of the email.

This can be used in the Loop data activity as discussed earlier.

Have fun

Debbie

p.s. I attached some sample files of this.
EmailTemplate.zip (1.64 KB)

julian.chasin_625 · September 22, 2009, 2:16pm

See captured image above (within TDA the Forum doesn’t work properly)

julian.chasin_625 · September 22, 2009, 2:16pm

See captured image above (within TDA the Forum doesn’t work properly)
Derive_email.ppt (114 KB)

Debbie_Peabody · September 22, 2009, 3:06pm

Unfortunately, the Find/Replace activity only handles one find and replace at a time. You can still use the same output file name as long at the output file name is not the same as your origianl template.

I have added change request 67,150 to add this feature. I don’t think it will make our next minor release but will be in the next major.

Debbie

julian.chasin_625 · September 22, 2009, 3:24pm

Thanks for the reply, and for suggesting the add’l feature.

However, I can’t see how to reuse the same output filename: the 2nd find/replace would use the original template file as input? Then each subsequent f/r would eliminate the prior one. It seems I have to chain the input/output files for as many f/r as I need to make.

Debbie_Peabody · September 22, 2009, 4:19pm

The first Find/Replace would use the template as input and output to output file name.
The second and supsequent find/replaceswould use the output file as the input and output file. Make sure the overwrite attribute is set to Yes.

I did find a bug in this where you have to have the output file existing in order to choose it as an output file. That should not be that way, but is easy to work around.

I have attached a sample automation script that does this.
Script_1.tas (6.37 KB)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
ESCLATION.txt (21.7 KB)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
FTRESCALATIONOUTPUT.txt (704 Bytes)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
EMAIL_QUERY.sql (36 Bytes)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
ESCALATION.TXT (278 Bytes)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
EMAIL_TABLE.sql (267 Bytes)

suryaprakash.j_593 · November 16, 2010, 5:22am

i have data in a table as mentioned below table name temp_ftr_escalation

FTR_ID GROUP_ID ACTION NOTES STATUS DUE_DATE ESCALATION_LEVEL ESCALATION_DATE ESCALATE_TO EMAIL_ADDRESS FTR_ACTION_ID
7119 12 Welcome Call OPEN 01/11/2010 11:12:02 3 03/11/2010 11:12:02 LISA.MOYSE lisa.moyse@airtel-vodafone.je 566
7208 12 Welcome Call OPEN 01/11/2010 11:12:47 3 03/11/2010 11:12:47 LISA.MOYSE lisa.moyse@airtel-vodafone.je 572
7199 12 Welcome Call OPEN 01/11/2010 11:13:19 3 03/11/2010 11:13:19 LISA.MOYSE lisa.moyse@airtel-vodafone.je 580
7210 12 Welcome Call OPEN 01/11/2010 11:13:37 3 03/11/2010 11:13:37 LISA.MOYSE lisa.moyse@airtel-vodafone.je 585
7333 12 Welcome Call OPEN 01/11/2010 09:08:41 3 03/11/2010 09:08:41 LISA.MOYSE lisa.moyse@airtel-vodafone.je 592
7388 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 609
7429 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 621
7436 12 Welcome Call OPEN 01/11/2010 09:00:09 3 03/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 628
7441 12 Welcome Call OPEN 01/11/2010 09:00:10 3 03/11/2010 09:00:10 LISA.MOYSE lisa.moyse@airtel-vodafone.je 633
7554 12 Welcome Call OPEN 04/11/2010 09:00:09 3 08/11/2010 09:00:09 LISA.MOYSE lisa.moyse@airtel-vodafone.je 635
6729 12 Welcome Call OPEN 28/10/2010 15:33:31 3 01/11/2010 15:33:31 LISA.MOYSE lisa.moyse@airtel-vodafone.je 408
7258 12 Welcome Call OPEN 29/10/2010 09:00:15 3 01/11/2010 09:00:15 LISA.MOYSE lisa.moyse@airtel-vodafone.je 428
7266 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 436
7274 12 Welcome Call OPEN 29/10/2010 09:00:16 3 01/11/2010 09:00:16 LISA.MOYSE lisa.moyse@airtel-vodafone.je 444
6951 12 Welcome Call OPEN 01/11/2010 11:09:13 3 03/11/2010 11:09:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 531
6953 12 Welcome Call OPEN 01/11/2010 11:09:25 3 03/11/2010 11:09:25 LISA.MOYSE lisa.moyse@airtel-vodafone.je 534
6949 12 Welcome Call OPEN 01/11/2010 11:09:29 3 03/11/2010 11:09:29 LISA.MOYSE lisa.moyse@airtel-vodafone.je 535
6731 12 Welcome Call OPEN 01/11/2010 10:17:14 3 03/11/2010 10:17:14 LISA.MOYSE lisa.moyse@airtel-vodafone.je 446
6734 12 Welcome Call OPEN 01/11/2010 10:17:34 3 03/11/2010 10:17:34 LISA.MOYSE lisa.moyse@airtel-vodafone.je 449
6738 12 Welcome Call OPEN 01/11/2010 10:17:52 3 03/11/2010 10:17:52 LISA.MOYSE lisa.moyse@airtel-vodafone.je 453
6739 12 Welcome Call OPEN 01/11/2010 10:17:57 3 03/11/2010 10:17:57 LISA.MOYSE lisa.moyse@airtel-vodafone.je 454
6743 12 Welcome Call OPEN 01/11/2010 10:18:13 3 03/11/2010 10:18:13 LISA.MOYSE lisa.moyse@airtel-vodafone.je 458
6792 12 Welcome Call OPEN 01/11/2010 10:19:49 3 03/11/2010 10:19:49 LISA.MOYSE lisa.moyse@airtel-vodafone.je 479
6992 12 Welcome Call OPEN 01/11/2010 10:21:03 3 03/11/2010 10:21:03 LISA.MOYSE lisa.moyse@airtel-vodafone.je 497
7015 12 Welcome Call OPEN 01/11/2010 11:07:45 3 03/11/2010 11:07:45 LISA.MOYSE lisa.moyse@airtel-vodafone.je 509
7025 12 Welcome Call OPEN 01/11/2010 11:08:43 3 03/11/2010 11:08:43 LISA.MOYSE lisa.moyse@airtel-vodafone.je 524
7142 6 Check on Progress OPEN 28/10/2010 13:37:53 3 01/11/2010 13:37:53 VINOD.SUD vinod.sud@airtel-vodafone.je

i want to pick email id and send rest of the columns in th email text.

Can you suggest how i can automate this on tda

i have tried my level best for the same i am ataching the same for you reference so that if you can tell me how i can get this run

I need get this done on priority today can you please please help me to run this successfully
SScript_Email_Test.tas (13.8 KB)

IgorM · November 16, 2010, 9:35pm

Hello,

Do I understand correctly that you actually want to send information from all columns of temp_ftr_escalation table to an address from ESCALATE_TO column (one email per every row)? Also, do I understand correctly that you want to use ESCALATION.TXT as a template for those emails and FindReplace activity should be used for substituting fields in the template with real values from the table row? If this is the case, why not put content of the template into email body directly using variables?

What I would do:

Remove Set_Variable_1 activity
Remove Find_replace_1 activity
Change Set_Variable_2 activity to return EMAIL (Name: EMAIL_ADDRESS; Value: EMAIL_TABLE.EMAIL)
Change Set_Variable_3 activity to return FTR_ID (Name: FTR_ID; Value: EMAIL_TABLE.FTR_ID)
Change Set_Variable_4 activity to return GROUP_ID (Name: GROUP_ID; Value: EMAIL_TABLE.GROUP_ID)
Add more Set_Variables activities to return all other values from the template (ACTION, STATUS, DUE_DATE, ESCALATION_LEVEL, ESCALATE_TO, ESCALATION_DATE)
For the Email_1 activity set property “To” to #EMAIL_ADDRESS# and property “Body” to:

Good Morning

The below SR is going to escalate…

EMAIL: #EMAIL_ADDRESS#
FTR_ID : #FTR_ID#
GROUP_ID: #GROUP_ID#
ACTION: #ACTION#
STATUS: #STATUS#
DUE_DATE: #DUE_DATE#
ESCALATION_LEVEL: #ESCALATION_LEVEL#
ESCALATE_TO : #ESCALATE_TO#
ESCALATION_DATE: #ESCALATION_DATE#

I tried this approach and it worked in my case.

Hope this helps,

Igor.

suryaprakash.j_593 · November 17, 2010, 4:00am

Hi Igor

Thanks for you reply it works perfectly.