Data Hub: Custom Email Alert (Example)

This article will walk you through the steps for creating automated email alerts.  In this example, we are going to setup an alert that automatically notifies the Maintenance Department and Corporate whenever temperature specifications are out of range.  In a real-world example, you could use TruOI Forms to collect the data into the platform for real-time notifications.

Prerequisites:

• This requires Admin or Data Admin privileges for implementation.
• Data Hub: SMTP Setup

Let's Begin

1. Login to your platform and from the lobby click "Data Hub" and then select "New ETL Job".

   

2. Drag the ( E ) anywhere onto the canvas and then hover over the Extract Data icon and click on the gear.  You will then be given the option to either "Create a new data set" or "Use an existing data set."

3. In this example, we are going to use a custom query to select our sample temperature range data and filter by any temperatures that fall out of specification and for whom an email wasn't already sent out.

SELECT *
FROM dbo.sample_data_temp
where email_sent = 'false'
AND (((therm_cal < 31) OR (therm_cal > 33))
OR ((refrigerator_1 < 34) OR (refrigerator_1 > 40))
OR ((refrigerator_2 < 34) OR (refrigerator_2 > 40))
OR ((freezer_1 < -10) OR (freezer_1 > 10))
OR ((freezer_2 < -10) OR (freezer_2 > 10))
OR ((beverage_station < 34) OR (beverage_station > 40))
OR ((merchandise_cooler < 34) OR (merchandise_cooler > 40))
OR ((merchandise_heater < 105) OR (merchandise_heater > 145)))
AND date >= CAST(GETDATE() AS DATE)

Our result set looks like this:  (Refrigerator 1 temp too high 45°)

4. Once you have your dataset configured, drag the ( T ) onto the canvas in order to create a Task.  You will immediately be prompted to choose which task you want to use.  Navigate to "Column" and select "Add Columns."

5. Hover over the extracted data set and grab the arrow above the icon and drag the arrow to the center of the "Add Column" icon in order to create the link between the data and the task. 

   

6. Once connected, click on the gear above the "Add Columns" to configure it.

7. We are going to add 2 columns:  "email_display_name" and "email_address"

• Click on the blue "Add" button.
  • Enter "email_display_name" for the column name
  • Leave the Data Type as a String
  • Change Populate From to "A Fixed Value"
  • Enter a value for the Display Name, in this case "Maintenance"
  • Click "Save"

• Click on the blue "Add" button.
  • Enter "email_address" for the column name
  • Leave the Data Type as a String
  • Change Populate From to "A Fixed Value"
  • Enter a value for the Email Address, in this case "Maintenance@ABCCorp.com"
  • Click "Save"

You should now have 2 additional columns in the dataset, click "Save":

8. Next we are going to add another Task to the job.  This next task is called "Branch."  Drag the ( T ) onto the canvas in order to create a Task.  You will immediately be prompted to choose which task you want to use.  Navigate to "Miscellaneous" and select "Branch."

  

9. Similarly to step 5, hover over the "Add Column" task this time and grab the arrow above the icon and drag the arrow to the center of the "Branch" icon in order to create the link between the two tasks. 

10. For this example, we don't need to do any extra configurations to the "Branch" task.  It is setup by default to continue on if and only if the number of incoming rows is > 0.  We are using this task to stop the ETL Job at this point if there aren't any rows to act on from the dataset.

11.  Next, we want to add the, "Execute SQL Script" task to the chain so that we can flag when an email was sent out.  This allows us to ensure that each email is only sent out one time.  Drag the ( T ) onto the canvas in order to create a Task.  You will immediately be prompted to choose which task you want to use.  Navigate to "Miscellaneous" and select "Execute SQL Script."

12. Similarly to steps 5 and 9, hover over the "Branch" task this time and grab the arrow above the icon and drag the arrow to the center of the "Execute SQL Script" icon in order to create the link between the two tasks. 

13.  Once connected, click on the gear above the "Execute SQL Script" to configure it.

You will immediately be prompted with a warning that notifies you that this task can permanently alter and/or delete data and that there is no way to "Undo" the changes made by this script.  If you are ready to continue, click Yes. 

Note:   It is important that you use this task carefully to ensure that you don't accidentally change or delete data.  Make sure you know your data and that you've double-checked your SQL query before executing this task.  As mentioned in the "Warning," it is advised you backup your data prior to running the task.

• Click "Yes" to the warning prompt box.
• Select the target data store for the data that you are going to manipulate
• In the "Execute SQL Script" configuration window
  • Set "Execution Mode" to "2 | Multiple times, once for EACH incoming data row, using values from that data row
  • Enter the SQL Script that will be used to manipulate a table of data, in this case our sample database
  • Click "Save" to close the configuration window.

Update dbo.sample_data_temp
Set email_sent = 'true'
where pkey = '${value:pkey}'

View of the Execute SQL Script configuration window:

14. Finally, let's add the "Send Email" task."  Drag the ( T ) onto the canvas in order to create a Task.  You will immediately be prompted to choose which task you want to use.  Navigate to "Miscellaneous" and select "Send Email."

15. Similarly to steps 5, 9 and 12, hover over the "Execute SQL Script" task this time and grab the arrow above the icon and drag the arrow to the center of the "Send Email" icon in order to create the link between the two tasks. 

16. Once connected, hover over the "Send Email" icon and click the gear to configure it.

• Change "Recipients" to "From Incoming Data"
  • When prompted
    • Set "Display Name Column" to "email_display_name" (data from add column task)
    • Set "Email Address Column" to "email_address"  (data from add column task)
    • Click "Save"

• To the right of "BCC" click "Edit"
  • Enter the email addresses that you want BCCd on the email.  In this example we're going to copy "corporate@ABCCorp.com" and "headquarters@ABCCorp.com"
    • Enter "corporate@ABCCorp.com" and click the "+"
    • Enter "headquarters@ABCCorp.com" and click the "+"
    • Click "Save"

• To the right of "Email Message" click "Edit."  This is where we actually create the look and feel of the email.
  • Configure the "Subject."  I'm going to configure the subject line to have text and the store number
    • In the subject line, we're going to type "Temperature Alert ()"
    • Next we're going to click inside the () and then to the right of the subject line click on the "${}" icon
    • We are going to select Value: ${value:store_number}

Our subject line now reads:  "Temperature Alert (${value:store_number})"

17. Now we can configure the body of our email.  We want to display as much information as possible so that the recipients of the email have the information that they need.  Using "Macros" at the bottom left of the email configuration window, we're able to insert values from the incoming data into the email body.

When you're done configuring the email, click "Save" and then click "Save" again to exit the email configuration.

18. Click "Save" and pick the folder location and name the ETL Job.

Manually Run the ETL Job

Some jobs are created to run manually based on certain circumstances.  To do this:

1. Open the ETL Job using "Edit ETL Job" and selecting the job.
2. In the upper right hand corner, press the "Play" button (Looks like a right-facing arrow).

Schedule the ETL Job

Most of the time we want to schedule jobs to run independently of requiring human interaction.  To determine if you can schedule a job, a dark clock will appear in the upper right hand corner after you've saved the ETL Job. 

Note:  If the clock is light gray, the job cannot be scheduled based on it's current configuration.

1. Click on the Clock in the upper right-hand corner of the canvas.
2. Schedule the job:  Set the Months, Days, Hours and Minutes that you want the job to run.  In this example, we are going to set the job to run Mon-Fri between 5am and 10pm.

• • Click on Months and select every month
  • Click on the Days and select Mon through Friday
  • Click on Hours and select 5am through 10pm
  • Click on Minutes and select :00, :05, :10, :15, etc. through :50, :55
  • Click "Save"

 

Sample Email

Here is an example of what a delivered email would look like using our configuration:

Conclusion

Data Hub Custom Email Alerts are a powerful way to clearly communicate information to staff.  There are multiple reasons why you may want to configure these alerts:

1. Simply provide information when a form has been submitted.
2. Notify staff that data has been uploaded and processed within the system.
3. Alert key personnel that there are potential equipment failures.

No matter the reason, automating these alerts on a schedule allow you to know what's going on within your company without having to sit in front of a screen and monitor the system.