Creating a Custom View for Use in a Scheduled Trigger

This article provides information on how to create a database view for use in a Scheduled Trigger. Examples of Custom View Automations include, but are not limited to, membership renewals, abandoned cart notifications, and donor acknowledgments.

Required Criteria

The View name must begin with: LV_P2

We retrieve a list of all views from the database that begin with “LV_P2” which become available for selection within a Custom View Scheduled Trigger.

The view is required to contain a column titled: address

This represents the email addresses for constituents that may enter an Automation. The column name must be lowercase.

The view is required to contain a column titled: custom_date

This date is the date that is used by the Tessitura Date Section window in the Scheduled Automation. For example, if you wanted to create a Membership Reminder Automation, the custom_date column might be the date on which the membership expires.

The date format should be YYYY-MM-DD. Always make sure that the Tessitura customer_no 0 and the T_eaddress record with the eaddress_no 0 are suppressed from your view.

The view may contain any number of other columns, and all of these columns may be inserted into any email campaigns within the Automation to use as a personalization tag. For example, if you create a view named: LV_P2_Membership_Reminder, it could have the following columns:

Required Columns:	Additional Columns Such As:
address	membership_level
custom_date	renewal_amount

Any of the columns returned can be dynamically inserted into SMS, Site Messages, Campaigns, etc by using the personalization tag representation of these columns:

%CUSTOM__ADDRESS% %CUSTOM__CUSTOM_DATE% %CUSTOM__MEMBERSHIP_LEVEL% %CUSTOM__RENEWAL_AMOUNT%

When the Automation runs, Prospect2 will add a prefix of "CUSTOM__" to the columns returned by the view. This makes all of the columns in your script accessible for use as personalization tags in an email or SMS within a Scheduled Automation.

We strongly recommend the use of ISNULL() for any output column that could potentially contain a NULL value, as indicated in the example view below. This serves two purposes:

Returning the empty string instead of NULL allows a previous value on a contact's custom field to be overwritten, avoiding potential personalization data issues displaying data that isn't relevant to that job run.
The normalization of data across all returned contact records in the data set, ensuring each contact record contains the same columns on every record.

Custom View Example

The following is code for a custom view that has the required fields:

USE [impresario]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LV_P2_SAMPLE_VIEW]
AS 
SELECT e.address, 
	   ISNULL(c.fname,'') as first_name, 
	   ISNULL(c.lname,'') as last_name, 
	   ISNULL(suf.description,'') as suffix, 
	   ISNULL(e.customer_no,'') as customer_no, 
	   ISNULL(t.description,'') as eaddress_type, 
	   ISNULL(e.primary_ind,'') as primary_ind, 
	   ISNULL(e.inactive,'') as inactive_ind, 
	   ISNULL(e.market_ind,'') as market_ind, 
	   GETDATE() as custom_date
FROM T_EADDRESS e
JOIN TR_EADDRESS_TYPE t ON t.id = e.eaddress_type
JOIN T_CUSTOMER c ON c.customer_no = e.customer_no
JOIN TR_SUFFIX suf ON suf.id = c.suffix
GO