I Hope someone could direct me on the following:
I have a script that generates a backlog report, but I want to send only some records from that result to the corresponding user. So by using automation, I run the script, send the result to excel; the excel file contains records so far for 2 locations. Is there any way to run the script only once and then filter the results; save the results and send the file to the appropriate user? . I know I can get around by creating 2 similar scripts where I can place the where condition and then each result send to the appropriate user; but I am wondering if there is something else in automation that I could use to replace the 2 scripts.
Regards,
Currently there isn’t any way of doing this that I can think of. It is a good idea so I have entered this as an enhancement request. Cr67,563
Debbie
P.S. Instead of using two scripts as you mention, use one script with a bind var. Saves a little…
Hi Debbie thanks for your quick replied.
Do you mind giving me an example on how to use the bind variables and one script only. Please note I do not want to be asked to enter the values.For your reference, I am attaching a data sample. So my idea is to be able to identify that records with the same sub_entity need to go to one particular user by email. Thanks a lot. TDA 2.5 is great
Backlog.xls (15 KB)
Hi,
I am following in the CSR you were going to open in regards the task I am trying to schedule; my task is as follow: Once I run a script and the results shows several records but not all the records need to be emailed to all users, I am combining the example you provided for “Derive email destination” to send the email by the email address available in the email table but at the same time in the automatic email I want to attach the results of my query only where the email address matches the email I have in the table. On the test you created for Email destination, before the last step, send email, I added my script, but I do not know what I can do to send only the records where the email address from my script matches the email address record. Looking forward for your help on this task.
Regards
Monica
Attached is a quick example. Place this in C:\Temp and then make an excel connection to the backlog.xls file.
This is the sample file you sent earlier. I added a worksheet that had the same job managers in the first sheet and made up some email addresses. The example is going to select from your original file and only send the rows applicable to that job managers email address. P.S. You need to use this example using TDA 2.6. The BETA that is currently posted is very close to the GA version about to be released.
I follow these steps in the Automation script:
- Add a variable JMGR to hold the mgr id.
- Add a variable EMAIL to hold email address.
- Using the Execut Script activity I query the Job_Manager worksheet. This will put all of the rows into one variable called MGRS.
- Add a Loop DataSet variable and point it to the MGRS table variable.
- For each row in that data set
a) Set the Job_MANGER column value to the JMGR variable.
b) Set the email column to the EMAIL variable.
c) Add a Select to Excel. Query the JOBS worksheet using a a bind var. This has to be the same name as JMGR as this is the value we want to filter on. Export the data to an excel file that has the manager id as part of the file name. You don't have to do this but it is a nice touch.
d) Now add an email that uses the email address and attaches the excel file that has the data in it just for that manager.
Run the script and it should help out. I noticed that if you click on the variable acitvities they might jump around so make sure the sequence looks like what is in the screenshot attached.
Let me know what questions you have or if this is what you had in mind.
Debbie
Attached is a quick example. Place this in C:\Temp and then make an excel connection to the backlog.xls file.
This is the sample file you sent earlier. I added a worksheet that had the same job managers in the first sheet and made up some email addresses. The example is going to select from your original file and only send the rows applicable to that job managers email address. P.S. You need to use this example using TDA 2.6. The BETA that is currently posted is very close to the GA version about to be released.
I follow these steps in the Automation script:
- Add a variable JMGR to hold the mgr id.
- Add a variable EMAIL to hold email address.
- Using the Execut Script activity I query the Job_Manager worksheet. This will put all of the rows into one variable called MGRS.
- Add a Loop DataSet variable and point it to the MGRS table variable.
- For each row in that data set
a) Set the Job_MANGER column value to the JMGR variable.
b) Set the email column to the EMAIL variable.
c) Add a Select to Excel. Query the JOBS worksheet using a a bind var. This has to be the same name as JMGR as this is the value we want to filter on. Export the data to an excel file that has the manager id as part of the file name. You don’t have to do this but it is a nice touch.
d) Now add an email that uses the email address and attaches the excel file that has the data in it just for that manager.
Run the script and it should help out. I noticed that if you click on the variable acitvities they might jump around so make sure the sequence looks like what is in the screenshot attached.
Let me know what questions you have or if this is what you had in mind.
Debbie
BackLog.zip (8.05 KB)
Thanks Debbie for your support.
I downloaded the Beta version but for some reason I cannot establish the excel connection. To establish the connection, I just clicked on new connection> Select Excel> find my file. When I click on connect the attached error is generated. My file looks OK; any idea?
In regards the task I am trying to accomplish, I saw a new tab within the excel file was created to identify the email address. In my file, I have already the email address by the required jobs, so my idea is as follow:
My attached backlog file contains already the email address. From the excel file select only the records that belong to the same email address; send those records to the required user; the email address is already in the file.
In order to connect to an Excel file and query as a table you must contain the data in a named range. Follow the instructions in the screenshot above.
You can use the same Ecel file to export to that named range. Just select to clear the data first and then select the named range as the target location for the data. See second image.
Debbie
In order to connect to an Excel file and query as a table you must contain the data in a named range. Follow the instructions in the screenshot above.
You can use the same Ecel file to export to that named range. Just select to clear the data first and then select the named range as the target location for the data. See second image.
Debbie
Thanks a lot Debbie, it works beautifully. I love TDA.
I would like to do this same query, but my data is in an Oracle database and the emails are in an excel file. The Oracle data will result in 1 record per recipient but does not include all of the emails in the excel file. How do I get this query to work using both files?
TIA,
Marge
