To define your own rules, you must:
Here are some important tips that will help you write syntactically and semantically correct WHERE clauses.
Your SQL WHERE clauses will probably require references to both IM_V_Defects and IM_DefectHistory. The IM_V_Defects view stores information that is current for an issue, whereas the IM_DefectHistory table keeps a record of all actions that have been taken on an issue, along with the effects of those changes on some issue fields.
For example, IM_DefectHistory stores an issue's inbox assignment before an action is taken as well as the inbox assignment after the action. These columns are AssignedIN for the inbox assignment prior to the action and AssignedOUT for the new inbox.
All actions taken on issues are recorded in the ActionCode field of the IM_DefectHistory table. These actions appear as action codes in the Action column of the History tab. You may have noticed such codes as FIXED and VERIFIED in the sample database.
To see the action codes for most of the actions in your database, look at the Edit Action of State dialog box of each state (exceptions are ENTERED, REASSIGNED, and MODIFIED, which are hard-coded and cannot be viewed). To access the Edit Action of State dialog box for a state, go to , click the name of a state in the Button Label column. Look at the value in the History Action Code field.
Custom fields are identified in the IM_V_Defects view as Custom1, Custom2, and so on, depending on their position on the tabs. Each custom tab has up to 10 fields, 1-10, 11-20, and so on. On Custom Tab 1, the first five fields appear in descending order in the left column; field 6 through field 10 appear in descending order in the right column.
To find out the schema name of a particular custom field, go to . For example, in the dialog for the sample database, the Add Rel Note? check box is the fourth field in the left column of Custom Tab 1 and so is Custom4 in the issue table.
The value of an unselected check box is '.' (a period). The value of a selected check box is 'X' (capital X).
For example, to retrieve all issues where Add Rel Note? is checked, you would specify as part of the WHERE clause:
D.Custom4 = 'X'
Here are four situations in which you might want to create a new rule. The first two situations suggest rules used in notifications on individual issues. The last two situations suggest rules used in project-wide notifications. Following each example is the WHERE clause written against the sample database.
Technical support and other groups want to know when a particular bug fix has been verified as fixed.
The WHERE clause looks like this:
DH.ActionCode = 'VERIFIED'
The value of ActionCode in the IM_DefectHistory table is updated whenever a user takes an action; therefore, Technical Support will receive mail only once, when the Verify action causes the action code VERIFIED to be entered in the IM_DefectHistory table and the History tab.
If the clause were written instead by referring to the Reason Code (known as Disposition in the database):
D.Disposition = 'FIXED'
then Technical Support would almost certainly receive mail more than once, because the Reason Code remains FIXED in the database. A user who subsequently adds a comment or saves the issue would trigger email because the rule would still match.
An average user, one who does not act on bugs, would like to know what has happened to a bug that he entered. In particular he wants to receive email when the bug has received a developer's attention. In workflow terms, this means the issue has just left the Dev-Ready state, and consequently, the WHERE clause must test for state change after the action.
The WHERE clause is:
DH.StatusIN = 'Dev-Ready' AND DH.StatusOUT <> 'Dev-Ready'
The state was Dev-Ready before the action, but after the developer's action, the bug moved to another state.
The documentation department has all doc-issues sent to a group inbox, Doc (Product A), rather than a user's inbox, Judy -- Doc. The documentation manager wants to receive email when an issue enters the group inbox.
This WHERE clause is:
DH.AssignedIN <> 'Doc (Product A)' AND DH.AssignedOUT = 'Doc (Product A)'
It specifies that email should be sent when the inbox before the action was not Doc (Product A) but after the action is Doc (Product A). The email will be sent only once, when the doc-issue is routed to Doc (Product A).
Note that without the first part of the WHERE clause, the documentation manager would receive email when any action was taken on the doc-issue while it was assigned to Doc (Product A). Although the rule is likely to generate a large volume of mail, since the notification needs to be applied project-wide, the two lines together of the WHERE clause restrict the rule to single event: when the inbox becomes Doc (Product A).
The rule does not need to specify the issue type to be DOC-ISSUE, although it would not be incorrect, since Doc (Product A) only holds documentation-related issues.
The release manager wants to receive email during the next month prior to a major release regarding the most severe bugs that cannot be fixed. The WHERE clause must test for severity, Product B, and the Cannot-Fix action code assigned when a developer takes the Cannot Fix action.
The WHERE clause is:
D.Severity = '1: Fatal/Data Loss' AND DH.ActionCode = 'Cannot-Fix' AND D.ProductCode = 'Product B'
These restrictions are necessary because if the WHERE clause merely tests for severity, then the rule will match anytime the issue is changed and saved, because the severity will not change until a user explicitly changes it.
The release manager might receive a great deal of mail, especially if this rule is applied in a project-wide notification. However, he can easily delete the notification after the release cycle is over.
Work from the general to the specific. First, consider the general business situations that might require email notification rules. You might ask all Issue Manager users when they would like to receive email about issues. Find out precisely what information each user wants to glean from the email. For example, a user might tell you that he wants to know when a bug is fixed. Upon further discussion you might find that what he really wants to know is when the fix is verified by a QA engineer. This subtle change might require a different WHERE clause.
Then, when you're satisfied that you understand what users want, translate the situation in terms of your organization's workflow.
Finally, write the SQL WHERE clause. Try testing the WHERE clause through an advanced query to make sure you are specifying the conditions exactly as intended.
Generally speaking, rules intended for notifications on individual issues should be written simply and generically, whereas rules for project-wide notifications should be as precise and as restrictive as possible to avoid excessive email.
Ask yourself how often users want to receive notifications; only once or each time a change is generated. If a user wants mail only once, then make the rules more restrictive.