The examples of simple and conditional email business rules are explicitly defining the sender and recipient of notifications.
The sender is specified in the From parameter of the email business rule script. The recipients are specified in the To parameter.
From: "Sales Admin" <YOUR_EMAIL_ADDRESS@gmail.com>
To: RECEIPIENT@northwind.com
Subject: Price of "{ProductName}" has been changed
. . . . .
Most real-world web applications will use the same email address as the “From” parameter. This email address is also known as system administrator.
Multi-tenant web applications may require a different system administrator for each tenant.
There are also instances when an email is sent on behalf of a person associated with the data that has changed. For example, a notification about a new customer may be sent from a regional sales manager to a subordinate sales representative.
It is not uncommon to have multiple recipients for a single notification.
Referencing “Sender” and “Recipient” Fields
If the sender and recipients of a notification are stored in the fields of the data controller, then the field names can be referenced directly in From and To parameters. The format of the reference is similar to the ProductName in the Subject of notification from the email business rule fragment presented above.
For example, the fragment of the notification can be changed as follows.
From: {SalesManagerEmail}
To: {SalesRepEmail}
Subject: Price of "{ProductName}" has been changed
. . . . .
The example makes an assumption that the data controller views include the data fields with the names SalesManagerEmail and SalesRepEmail.
The actual email addresses may be quite flexible.
The address in the From data field may be stored with or without a display name. The address in the To field follows the same specification. Multiple email addresses can be stored in this field as well. Email business rules will automatically parse and detect multiple addresses and optional display names.
For example, the following list can be stored in the database in the table column SalesRepEmail.
johndoe@acme.com, john.doe@acme.com,
John Doe <john.doe@acme.com>; "Doe, John" <johndoe@acme.com>
The application framework will parse the field value and will send the email to four recipients.
Using SQL Business Rules to Determine Sender and Recipient
Consider the Northwind sample, a line-of-business app of a mail order company.
Suppose that there is a business requirement to notify the sales rep placing an order if this is a repeat purchase by customer. A personal thank you note must be written to a repeat customer.
Another business requirement is to send a shipping reminder to an employee associated with an order when its shipping date has changed.
Let’s make an assumption that every employee has a user account composed of their first and last names separated with period. The user account andrew.fuller is shown in the screenshot of Membership Manager.
Sending a “Thank You” Note Reminder to Current User
If the data fields with sender and recipients are not available, then you can determine this information with a help of an SQL business rule that makes use of a current user identity.
Create a new SQL business rule in Orders data controller with the following properties.
| Property |
Value |
| CommandName |
Insert|Update |
| Type |
SQL |
| Phase |
After |
| Script |
set @BusinessRules_Whitelist = 'Empty'
if @Arguments_CommandName = 'Insert'
begin
-- count the number of orders
declare @NumberOfOrders int
select @NumberOfOrders = count(*)
from Orders where CustomerID = @CustomerID
-- update the Whitelist if this is the second order
if @NumberOfOrders = 2
begin
set @BusinessRules_Whitelist =
@BusinessRules_Whitelist + ',ThankYouReminder'
-- find the company name of the "repeat" customer
select @Session_RepeatCustomer = CompanyName
from Customers where CustomerID = @CustomerID
-- find the email address of the current user
select @Session_EmailAddress = m.Email
from aspnet_Membership m
inner join aspnet_Users u on m.UserId = u.UserId
where
u.UserName = @BusinessRules_UserName
end
end
|
The script determines if the current order is the second order placed for the customer by the sales rep.
The parameter named @BusinessRules_UserName is used to find the email address of the current user. The other highlighted parameters are used in the script of the email.
Proceed to create a new Email business rule in the same data controller
| Property |
Value |
| Command Name |
Insert |
| Name |
ThankYouReminder |
| Type |
Email |
| Phase |
After |
| Script |
Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true
From: "Sales Admin" <YOUR_EMAIL_ADDRESS@gmail.com>
To: {@Session_EmailAddress}
Subject: Customer "{@Session_RepeatCustomer}" placed the second order!
Please send a personal "Thank You" note to the customer.
Northwind,
Administrator
|
Make sure to change the highlighted STMP account parameters with your values.
The hierarchy of the data controller with two business rules is shown next.
Generate the app, sign in as andrew.fuller and change the user email address in My Account settings to your own email. Create a new customer and place the first order - there will be no notification. Place a second order for the same customer and a notification will be waiting in your inbox.
Standard UserEmail Parameter
The SQL business rule can be made shorter thanks to the property UserEmail available in BusinessRules class of the application framework. Remove the code that assigns a value to @Session_EmailAddress and change the email business rule as shown in the next fragment.
. . . . .
To: {@BusinessRules_UserEmail}
Subject: Customer "{@Session_RepeatCustomer}" placed the second order!
. . . . .
The property returns the email address of the current user. The parameter value will be equal to the value of the property.
Sending a Shipping Notification
The partial database schema of Northwind database shows Orders and Employees tables.
We can easily determine the user name of an employee thanks to our assumption that the user name is composed of a period-separated first name and last name (andrew.fuller, steven.buchanan, etc.)
Let’s send another notification that will go out as soon as an order has a new shipping date. The email will be sent to an employee specified in Orders.EmployeeID field.
Change the SQL business rule script by adding the following condition at the end of the original.
-- send an order shipping notification to the sales rep
if @ShippedDate_Modified = 1 and @ShippedDate_NewValue is not null
begin
-- find the user name of the sales rep
declare @SalesRepUserName nvarchar(50)
select @SalesRepUserName = lower(FirstName) + '.' + lower(LastName)
from Employees where EmployeeID = @EmployeeID
-- find the email address of the sales rep
select @Session_SalesRepEmailAddress = m.Email
from aspnet_Membership m
inner join aspnet_Users u on m.UserId = u.UserId
where
u.UserName = @SalesRepUserName
-- update the Whitelist to allow notification
if @Session_SalesRepEmailAddress is not null
begin
set @BusinessRules_Whitelist =
@BusinessRules_Whitelist + ',ShippingNotification'
-- prepare a session variable used in the notification
select @Session_ShipCustomer = CompanyName
from Customers where CustomerID = @CustomerID
end
end
The highlighted parameter from SQL script will be referenced in the To parameter of the email notification.
Add another Email business rule with these properties.
| Property |
Value |
| Command Name |
Insert|Update |
| Name |
ShippingNotification |
| Type |
Email |
| Phase |
After |
| Script |
Host: smtp.gmail.com
Port: 587
UserName: YOUR_EMAIL_ADDRESS@gmail.com
Password: PASSWORD
EnableSSL: true
From: "Sales Admin" <YOUR_EMAIL_ADDRESS@gmail.com>
To: {@Session_SalesRepEmailAddress}
Subject: Order placed by "{@Session_ShipCustomer}" has shipped
Order placed on {OrderDate,d} by {@Session_ShipCustomer} has shipped on {ShippedDate,dddd, m/d/yyyy}.
The amount of freight is {Freight,c}.
Northwind,
Administrator
|
Notice the custom data format strings in the field OrderDate, ShippedDate, and Freight.
This is the new hierarchy of Orders data controller.
This is how the shipping notification will look in the Gmail inbox of the sales representative.
Carbon Copy and Blind Carbon Copy
If you need to copy additional recipients when sending a notification, then specify parameters Cc and Bcc in the scrip of the email business rules.
Both parameters follow the same format as To parameter.