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.
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.
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.
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 | |
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.
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.
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 | |
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.
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.