Simpleview SQL Server Views (Setup Step 3)

The Simpleview integration requires that two SQL server views in the Simpleview database copy. These are:

  • RemoteMasterSubscriberList
  • RemoteMasterInterestList

You will need the appropriate database permissions in order to do this work.

RemoteMasterSubscriberList

This View contains the email address field and all of the personalization fields that you want to use in Informz.

The first column in this view must be named contact_email and contain the email address of the members you will send to.

The second column in this view must be named contact_id and contain the primary key unique identifiers associated with the email address.

The integration supports up to 23 other fields for personalizing your emails (e.g FirstName, LastName, or other fields associated with members).

The SQL statement to create this View is as follows:

Create View RemoteMasterSubscriberList AS
SELECT email_field* as Contact_email, Key_field* as Contact_id, FirstName, LastName, etc.
FROM *
WHERE *

Each item above denoted with an asterisk (*) can be customized to fit business needs. Supply the actual database field names for email_field and key_field as they are named in your database. The FROM and WHERE clauses should be populated with table names and limiting criteria for the members you would like to bring over into Informz.

Below is an example of how this might appear:

select  Email, c.ContactID, g.groupname as ContactType, CT.ContactType as 
ContactStatus, c.GroupID, Fname, Lname, FullName, Title, Gender
from contacts c with (nolock)inner join contacttypes ct with (nolock)
on c.ctypeid = ct.ctypeid
innerjoin groups g with (nolock)on c.groupid = g.groupid
where contacttype in ('Primary','Active')
and sendemail = 1
and email <> '' and email is notnull
and ct.UseForEmail = 1

The above code should be very close to what you need for your database. Note that your contact account types that you wish to bring over may be different.

RemoteMasterInterestList

This view contains an aggregation of each InterestGroup and Member to be mailed to once inside of Informz. This view contains only two columns:

The first column in this view must be named Interest_Name and contains the name for that interest group inside of Informz.

The second column in this view must be named contact_id and contains the primary key unique identifier as listed in RemoteMasterSubscriberList. There should be one record for each contact_id in an interest. Each contact_id has a separate record for each Interest_Name it is associated with.

The SQL statement to create this View is as follows:

Create View RemoteMasterInterestList AS
SELECT name_field* as Interest_name, Key_field as Contact_id
FROM *
WHERE *

Each item above denoted with an asterisk (*) can be customized to fit business needs. The FROM and the WHERE clauses should be populated with table names and limiting criteria for the groups to bring over into Informz. Also, for multiple interest groups, this view can be made a series of unions with each section containing the criterion for that interest group.

Below is an example of how this might appear:

Select g.groupname + '-' + s.sourcename as interest, c.contactid
From accounts a with (nolock)
Innerjoin sources s with (nolock)on a.tblid = s.tblid and a.groupid = s.groupid
Innerjoin sourcerecs sr with (nolock)on s.sourceid = sr.sourceid
innerjoin  contacts c with (nolock)on c.acctid = a.acctid
innerjoin groups g with (nolock)on s.groupid=g.groupid
where s.active = 1
andlen(s.sourcename)> 1
groupby g.groupname + '-' + s.sourcename, c.contactid
UNION
select'ConsumerInterest-' + Interest as ConsumerInterestName, ContactID as RemoteID
from consumerinterests ci with (nolock)
innerjoin consumerInquiryxInterest cii with (nolock)
on cii.intid = ci.intid
innerjoin consumer_inquiries cin with (nolock)
on cin.inquiryid = cii.inquiryid
where ci.active = 1
andlen(Interest)> 1

Note that the type of interest is in front of the actual name to make it easier to differentiate.

View To Success

With these two views created and populated, your Simpleview data is ready to be used in Informz as the basis for target groups and personalization fields.