Descriptions of Fields in the notify_templates table

The values in a notify_templates record encapsulate almost everything about a notification, so it is important to understand all of the fields in detail

Field	Description
template_id	A 32-char descriptive name for the template. This is the Primary Key of the table. You should use a string that describes the template’s purpose or usage, such as “ELEV_PERMIT_REMINDERS”
description	Optional 512-char long description for the template. You can use this to describe exactly what the template is used for. For example: “Notification template to send email to the maintenance supervisor when an elevator permit renewal is approaching.”
activity_id,notify_cat	This is a foreign hey to the notification category (notify_cat) table. These fields are optional, and they are used to assign a category to a template, in order to organize/filter/search notifications based on category (i.e. the notification category assigned to the notification’s template).
internal_code	An optional free text field. This is similar to an “option1” field. An example use is for a custom application to “tag” all templates it creates with a unique string so that it can identify a template as its own
trigger_date_field	Contains the name of the date field in the activity_log table to use (e.g. activity_log.date_scheduled, activity_log.date_scheduled_end, activity_log.date_required, etc.) for the date based criteria represented in the fields below. The string stored here should be the full table.field name as shown.
trigger_lead	The number of days before or after the date stored in trigger_date_field to send the 1st notification, but only IF the status condition (trigger_condition_to and trigger_condition_from) is met. Whether it is before or after the date is determined by the trigger_lead_seq field.
trigger_lead_seq	Indicates whether the number of days stored in trigger_lead is before or after the date in trigger_date_field. This is an Enum with values “None;Before;After”. If the value is None, then trigger_date_field and trigger_lead are Not Applicable and ignored. In other words, the notification is sent simply if the status condition below is met.
trigger_condition_to: SQL	Expression representing the test that the current status of the action/event must pass. The expression must contain the exact string ${status} to represent a placeholder for the status value of the action/event (i.e. activity_log.status).
trigger_condition_from	SQL Expression representing the test that the previous status of the action/event must pass. The expression must contain the exact string ${status} to represent a placeholder for the previous status value of the action/event. The previous status is the status that the action/event had when the WFR ran previously. Typically the WFR runs daily, so this condition tests the status value that the action/event had the day before. By contrast, the trigger_condition_to expression above tests the current status value that the action/event has at the exact moment when the WFR runs. Note that you can use any valid SQL expression involving any field in activity_log in the trigger_condition_to and trigger_condition_from fields.
notify_recurrence	Specify how often to resend notification, in days, until event status trigger condition is no longer applicable (e.g. event status has changed). This is field is optional with a default value of 0, which means that the email is sent only once when the trigger conditions are met. An example usage of this field is to resend an email notification to a manager every 1 or 2 days when a critical task is past its due date and it has not yet been completed. When the task is finally marked completed, the recurring email will automatically stop.
total_recurrence	When notify_recurrence>0, this field specifies the maximum number of notifications to send. The purpose of this field is to prevent a large number of emails from being sent when notify_recurrence>0 and the task/event status never changes to stop the recurring emails
notify_subject	Specifying the subject line text of the email notification by storinh the text directly in the notify_subject field of the template.
notify_subject_id	Create a messages record and store a foreign key in this field.
notify_subject_refby	Part of the foreign key for the messages table. To reference a messages record as the text for the subject line, store the same values in notify_templates for notify_subject_id, notify_subject_refby, activity_id as you used for the messages record in fields message_id, referenced_by, and activity_id, respectively.
notify_message_id	This is a foreign key to the messages table for the body text of the email message. You cannot store the body text directly in the template, you must create a record in the messages table and reference it here
notify_message_refby	Part of the foreign key for the messages table. To reference a messages record as the text for the email body, store the same values in notify_templates for notify_message_id, notify_message_refby, activity_id as you used for the messages record in fields message_id, referenced_by, and activity_id, respectively.
notify_recipients	The list of email recipients that will receive the email message. It must be a comma or semicolon separated list of email addresses.

Subject Lines

There are two methods for specifying the subject line text of the email notification.

• Store the text directly in the notify_subject field of the template. The advantage of this method is simplicity.
• Create a messages record and store a foreign key in the notify_subject_id field of the template. T advantage of this method is that you can specify translatable strings and include embedded database field value references.

Trigger Fields

The trigger fields can be used to send notifications based on 3 scenarios:

1. The current status matches a specified value/condition.
2. The status changed from a specified value/condition to any other (unspecified) value/condition.
3. The status changed from a specified value/condition to a different specified value/condition.

The following table illustrates the different use cases:

Status criteria	trigger_condition_from	trigger_condition_to
0. No status-based criteria, uses dates only	NULL	NULL
1. Current status matches Condition2	NULL	Condition2
2. Previous status matches Condition1 and current status does not	Condition1	NULL
3. Status changes from Condition1 to Condition2	Condition1	Condition2

Scenario Examples

Some examples for each of the scenarios above:

• You want to send a notification when a task has been completed:

trigger_condition_to = “${status} IN (‘COMPLETED’, ‘COMPLETED-V’, ‘CLOSED’)”

• You want to send a notification when a task has NOT been completed:

trigger_condition_to = “${status} NOT IN (‘COMPLETED’, ‘COMPLETED-V’, ‘CLOSED’)”

• You want to send a notification when a task has been started or completed:

trigger_condition_to = “${status} IN (‘IN PROGRESS’, ‘COMPLETED’, ‘COMPLETED-V’, ‘CLOSED’)”

• You want to send a notification when a task has been cancelled:

trigger_condition_to = “${status} = ‘CANCELLED’”

• You want to send a notification when a task is no longer on-hold:

trigger_condition_from = “${status} = ‘IN PROCESS-H”

• You want to send a notification when a task was in progress and the status changes:

trigger_condition_from = “${status} = ‘IN PROGRESS”

• You want to send a notification if a scheduled task is closed without completion steps:

trigger_condition_from = “${status} = ‘SCHEDULED”
trigger_condition_to = “${status} = ‘CLOSED”

• You want to send a notification if a scheduled task is completed and closed without verification:

trigger_condition_from = “${status} = ‘COMPLETED”
trigger_condition_to = “${status} = ‘CLOSED”

Sample notify_templates settings

The following are sample notification trigger requirements, and the corresponding notify_templates field values:

• Send notification [5] days [Before] the [Start Date] if [Not Started]

  [trigger_lead] = 5
  [trigger_lead_seq] = ‘Before’
  [trigger_date_field] = ‘activity_log.date_scheduled’
  [trigger_condition_to] = “${status} NOT IN (‘IN PROGRESS’, ‘COMPLETED’, ‘COMPLETED-V’, ‘CLOSED’)”
  [trigger_condition_from] = NULL

• Send notification [2] days [After] the [End Date] if [Not Completed]

  [trigger_lead] = 2
  [trigger_lead_seq] = ‘After’
  [trigger_date_field] = ‘activity_log.date_scheduled_end’
  [trigger_condition_to] = “${status} NOT IN (‘COMPLETED’, ‘COMPLETED-V’, ‘CLOSED’)”
  [trigger_condition_from] = NULL

Below is an example of a view to define notification templates from Compliance Management: