So far, we’ve examined how to use the SELECT statement in SQL to create SQL Views for use in Crystal Reports, and as List Views in TMS. Now we are going to use the SELECT statement to create an alert in TMS. An alert is a SQL SELECT that will be executed whenever the user logs into TMS. If the query returns any records, an alert will pop-up in the lower right-hand corner.

We are just going to create a very simple alert that tells us if when records have a particular Status Flag, and will start by getting the FlagID for our desired Status Flag, using the following SQL:

SELECT * FROM FlagLabels

Now that you have identified the primary key for your desired Status Flag, let’s create a new statement that will find all the Objects with that flag.

/* Example 1: Filtering with a WHERE clause  */
SELECT * FROM StatusFlags WHERE FlagID = Your Key

The problem is that while we are now filtering down the StatusFlags table to retrieve only records associated with one flag, the information retrieved isn’t really usable. We will use a JOIN to include the Objects table to help us translate the results into something our users can understand. Since all the objects we retrieve must also have the desired flag, we will utilize an INNER JOIN.

/* Example 2: Filtering with an INNER JOIN and a WHERE clause */
SELECT O.ObjectNumber
FROM Objects O
INNER JOIN StatusFlags F ON O.ObjectID = F.ObjectID
WHERE F.FlagID = Your Key

If your script worked you should see records in your result set. It is important that you have results before your proceed; You have to have at least one result in order to save your alert in TMS.

List View Designer

Open the List View Designer and login to your database. Select the “New” icon on the Alerts tab. Give your new Alert a name, and enter a description that explains what the alert does. Now copy and paste your SQL, and click “Preview.”

Save, activate, and include the appropriate security groups, and you now have a new alert in TMS!

A SELECT statement is a very powerful tool, that can help you unlock the potential of your database by enabling you to create custom List Views and Alerts. Hopefully, these tutorials have better illustrated how to leverage your existing SQL knowledge to enhance the user experience and add value to your system.