First of all I would like to thank everybody who could attend this year’s Users Seminar, which took place here in Fresno from the 24th to the 26th of September. It was great to catch up with Charlie, Ingrid, Barry, Beth, Tina, Rhonda, Bill, Lynn, and many other customers.
On Wednesday morning I gave a talk on the use of Task Agent with our “Enterprise” modules (Inventory, Purchasing, Order Management, etc.), and I’d like to talk more about it here, as well as provide the sample code.
Scenario
The scenario I went through was to automate the delivery of sales information to sales managers and also individual sales persons. They would receive an email every Friday, containing performance comparison between the current week of the current year and the same week of the prior year. The manager would receive a .csv (comma-separated value) file with data pertaining to all sales persons, and each sales person would receive an email with his/her own numbers.
Methodology and database considerations
In order to achieve this I used Task Agent to run an “Execute SQL Script” task.
The script requires two functions that already exist in your database if you’ve updated it using a setup after 9/26/2007. Otherwise, you can add them manually.
You can get the main query and these 2 companion functions in the zip file located here.
The functions are:
>> fnAB_WeekOfYear: returns the week # given a date (from 1 to 52)
>> FnOM_GetHeaderTotalAmount: Returns the total amount for a given Sales Order
The main query returns the following columns:
ParticipantIdentityID,
ParticipantName,
RecipientEmailAddress,
ThisWeek_ThisYear,
ThisWeek_LastYear,
Difference
Although the main query is not harmful (it’s a simple select statement), please make sure to read carefully the comments on the top of it to understand its mechanism and filtering criteria.
Bird’s eye view of the complete Task Agent solution
Tasks
We’re using 2 “Execute SQL Script”-type tasks. They both run the same query and the only difference is the type of delivery. The first one delivers individual emails to each sales person, whereas the second task sends one single email with all the information to the sales manager.
Here’s an image to illustrate where you paste the query:
First task - emailing individual recipients
The first task emails the results to individual recipients and the information needs to be pertinent to that particular person. We do that by:
a) Set the Results Storage Type to MailMerge Format
b) Set the MailMerge Address to a specific column retrieved by the query (in this case the column called “RecipientEmailAddress”.
c) Leave the email recipients blank
d) In the email message tab, write your message, referencing the columns retrieved using tags (surround the column title with < >). Red arrows are pointed to these tags in the picture below.
e) Optionally, you can format your message using standard HTML tags, like the ones shown in blue in the image below.
Second task (task #3) - emailing the sales manager
To send one single email with all the information to one single recipient, we need to:
a) Set the storage type to be Comma-Delimited
b) Specify the manager’s email address
c) Do not use tags in the email message (standard HTML tags are still OK to use).
Conclusion
Task Agent is a great tool which really gives wings to our imagination. Although this was an example on a useful application, it barely scratches the surface on the use of this tool. I envisioned the job described here to be run on a weekly basis, but bear in mind that there are other “triggers” for jobs, such as data changes and system changes.
Also, additionally to SQL scripts, you can run reports, Abecas Insight programs, any external program, schedule FTP uploads and downloads, and much more.
Please send us feedback about this on any other topic, and if you have any questions, don’t hesitate to contact our support department (support at argosoftware dot com) or me directly (gustavoc at argosoftware dot com).
Gustavo Cavalcanti
Product Manager - EMS
Argos Software
