|
The object of this section is to show how to obtain the Recipient and CC Recipient email addresses for Event Alerts dynamically from a SQL query.
An alert can be created that reports on the details of an order that is to be addressed to the user that entered the order. Instead of creating an Event Alert specifically for each user, you can create one Event Alert that will have the email addresses dynamically assigned when the Event Alert runs.
If you need more information on how to set up a base Event Alert then please check out the section Base Alert Event Setup in the online manual at www.seradex.com/help
NOTE: The CC address MUST NOT be a required field.
The example query below will return the following email addresses,
a) Email address of the user who entered the Sales Order.
b) The email address of the Sales Rep assigned to the Sales Order.
c) The main customer email address.
SELECT SalesOrder.SalesOrderNo, SalesOrderDetails.[LineNo], ItemSpecs.ItemNo, SalesOrderDetails.QtyOrdered, SalesOrderDetails.ListPrice, Employees.email AS OrderEntryUser, SalesReps.SalesRepCode,
Employees_1.email AS SalesRepEmail, Customers.email AS CustomerEmail
FROM SalesOrder INNER JOIN SalesOrderDetails ON SalesOrder.SalesOrderID = SalesOrderDetails.SalesOrderID INNER JOIN Employees ON SalesOrder.EmployeeID = Employees.EmployeeID INNER JOIN SalesReps
ON SalesOrder.SalesRepID = SalesReps.SalesRepID INNER JOIN Employees AS Employees_1 ON SalesReps.EmployeeID = Employees_1.EmployeeID INNER JOIN ItemSpecs ON SalesOrderDetails.ItemSpecID = ItemSpecs.ItemSpecID
INNER JOIN Customers ON SalesOrder.CustomerID = Customers.CustomerID
The recipient of the Event Alert will be the Order Entry User, while the Sales Rep and the customer will be the CC recipients.
Based on the example above the data in the Event Alert Setup email fields will be as,
A) Recipient Address: OrderEntryUser = gpitt@seradex.com
B) Recipient CC Address: SalesRepEmail = knightwing19@gmail.com
The main reporting field (also known as the referenced field or alias field) must exist within the Event Alert Setup form. See letter C in the example above.
FYI: In the example above we are using a referenced field if we were using an alias it would look like this in the code, Customers.email AS CustomerEmail with CustomerEmail being the alias of Customer.email.
Multiple entries for Recipient and CC Recipient
Multiple entries can be specified in both the Recipient and CC Recipient text boxes by separating them with a semicolon ( ; ).
This will allow specifying both field names and fixed email addresses together (See letter D above). Fixed email addresses will be detected by looking for the "@" symbol with the entry. Any field/alias that contains a "@" symbol in it's name will not be available for use as a dynamic field as it will be treated as a fixed email address instead.
A field used for a dynamic email address will be excluded from the body of the email. If this data is desired to be included, it will need to be duplicated under a separate alias.
Upon saving of the Event Alert Setup, the field specified in the Recipient and CC Recipient will validated to ensure they exist in the Event Alert query.
The Event Alert query must return a valid Recipient email address for each record reported on for the alert for each record to be emailed successfully. When a record featuring an invalid recipient email address is encountered, flag will indicate an email could not be sent for the record then continue processing the next record returned by the Event Alert query. An email will then be sent to the email setup's default recipient (specified on the Setup Mail Senders form) with the details of the failures with FAILED to send added to the beginning of the Event Alert's subject. This way the cause of the failure can be determined.
Recipient CC email addresses can be null or blank. If the value is returned by the event alert query for the Recipient CC address but the value is not a valid email address (Example: @ is missing from returned value), that invalid email address will be excluded from the email and failure email as above will be sent containing the value returned as the Recipient CC email address so the reason for the failure can be determined.
|
