Phone number for list criteria

Brian,

In List Manager (or Extraction Manager), under the Constituent folder, there’s a criteria item for “Phone Type”.  We use the following criteria:

Phone Type DOES NOT HAVE Phone 1 and Phone 2

This weeds out people who don’t have any primary phone numbers in their accounts……

Christy

Seattle Rep

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Brian O'Neill
Sent: Wednesday, July 13, 2011 10:07 AM
To: Christy Carlson
Subject: [Tessitura Marketing Forum] Phone number for list criteria

I'm trying to pull lists for our upcoming telemarketing campaign and I'm trying to weed out people with no phone number from my lists, but I can't seem to find the proper criteria for this. Is there a way to do this in extractions / lists?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Marketing Forum. You may reply to this message to post to the Marketing forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Honestly, Ryan, I have *no* idea.  J  I didn’t realize that was a custom element – normally when we add things to List Manager, we add an “SRT” prefix to it to indicate that it’s not standard.  This element doesn’t have that.

Christy

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Ryan Rowell
Sent: Wednesday, July 13, 2011 12:02 PM
To: Christy Carlson
Subject: RE: [Tessitura Marketing Forum] Phone number for list criteria

Ah, I just realized that my "Phone Primary Indicator" is custom and that "Phone Type" was added by me as well.
Christy, does your phone type use vs_phone for the detail table because that doesn't only look at primary numbers.

Brian, I'll get back to you on how I have the primary indicator set up.

Ryan,

Thank you for this.  I just finished my first SQL class, so I actually do understand the code (yay me).  I think everything you’ve provided will let me get this set up – if I run into any issues, I’ll give you a shout.

Sean Rector, MCSE

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Ryan Rowell
Sent: Tuesday, December 06, 2011 5:41 PM
To: Sean Rector
Subject: RE: [Tessitura Marketing Forum] Phone number for list criteria

Hi Sean,

I'd recommend setting up criteria elements in t_keyword, but first you will need a local view if you want to keep out non-primary phone numbers.

Here is the view I use, which is basically just the normal vs_phone view with primary_ind added

USE [Impresario]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE View [dbo].[LVS_PHONE]
AS

Select    a.*, b.edit_ind, c.primary_ind
From    t_phone a
    JOIN vrs_phone_type b ON a.type = b.id
    LEFT OUTER JOIN vs_address c ON a.address_no = c.address_no
Where    ISNULL(a.address_no,0) = 0
    OR
    c.address_no is not null

GO

Then you would set up two criteria elements in t_keyword

Phone Primary Indicator
Description = Phone Primary Indicator
Data Type = String
Edit Mask = (leave empty)
Detail Tbl = lvs_phone
Detail Col = !.primary_ind
Ref Tbl = tr_gooesoft_dropdown
Ref Idcol = short_desc
Ref Desccol = description
Ref Where = code = 1
etc.

and, Phone Type
Description = Phone Type
Data Type = Number
Edit Mask = (leave empty)
Detail Tbl = lvs_phone
Detail Col = !.type
Ref Tbl = vrs_phone_type
Ref Idcol = id
Ref Desccol = description
Ref Where = (leave empty)
etc.

Last part is setting up the list using the following criteria in the top box
Phone Type IN (1,2)
Phone Primary Indicator DOES NOT HAVE ('Y')

and this criteria in the bottom (OR) box

Phone Type DOES NOTE HAVE (1,2)

Hope that helps,

Cheers!

E-Mail:         sean.rector@vaopera.org
Phone:        (757) 213-4548 (direct line)
_{+}

Tickets and Subscriptions On Sale Now!
Aida | Hansel And Gretel | Orphée | The Mikado
Visit us online at www.VaOpera.org or call 1-866-OPERA-VA

Experience the Beauty, Power & Passion of Virginia Opera.

This e-mail and any attached files are confidential and intended solely for the intended recipient(s). Unless otherwise specified, persons unnamed as recipients may not read, distribute, copy or alter this e-mail. Any views or opinions expressed in this e-mail belong to the author and may not necessarily represent those of Virginia Opera. Although precautions have been taken to ensure no viruses are present, Virginia Opera cannot accept responsibility for any loss or damage that may arise from the use of this e-mail or attachments.

{*}

Ryan,

I’ve set it up as you’ve outlined below – but am experiencing an error when trying to run the List:

SQLSTATE=42000

Microsoft OLE DB Providor for SQL Server

The SELECT permission was denied on object 'LVS_PHONE', database 'Impresario', schema 'dbo'.

I've obviously got something munged in the permissions - I'm guessing on the View?   In the Permissions section of the Properties, there are no users or roles listed.  I've just added ImpUsers - Select permission.  Does that group need any other permissions?

Sean Rector, MCSE

From: Tessitura Marketing Forum [mailto:forums-marketing@tessituranetwork.com] On Behalf Of Ryan Rowell
Sent: Tuesday, December 06, 2011 5:41 PM
To: Sean Rector
Subject: RE: [Tessitura Marketing Forum] Phone number for list criteria

Hi Sean,

I'd recommend setting up criteria elements in t_keyword, but first you will need a local view if you want to keep out non-primary phone numbers.

Here is the view I use, which is basically just the normal vs_phone view with primary_ind added

USE [Impresario]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE View [dbo].[LVS_PHONE]
AS

Select    a.*, b.edit_ind, c.primary_ind
From    t_phone a
    JOIN vrs_phone_type b ON a.type = b.id
    LEFT OUTER JOIN vs_address c ON a.address_no = c.address_no
Where    ISNULL(a.address_no,0) = 0
    OR
    c.address_no is not null

GO

Then you would set up two criteria elements in t_keyword

Phone Primary Indicator
Description = Phone Primary Indicator
Data Type = String
Edit Mask = (leave empty)
Detail Tbl = lvs_phone
Detail Col = !.primary_ind
Ref Tbl = tr_gooesoft_dropdown
Ref Idcol = short_desc
Ref Desccol = description
Ref Where = code = 1
etc.

and, Phone Type
Description = Phone Type
Data Type = Number
Edit Mask = (leave empty)
Detail Tbl = lvs_phone
Detail Col = !.type
Ref Tbl = vrs_phone_type
Ref Idcol = id
Ref Desccol = description
Ref Where = (leave empty)
etc.

Last part is setting up the list using the following criteria in the top box
Phone Type IN (1,2)
Phone Primary Indicator DOES NOT HAVE ('Y')

and this criteria in the bottom (OR) box

Phone Type DOES NOTE HAVE (1,2)

Hope that helps,

Cheers!

Information Technology Manager
Virginia Opera Association

E-Mail:         sean.rector@vaopera.org
Phone:        (757) 213-4548 (direct line)
_{+}

Tickets and Subscriptions On Sale Now!
Aida | Hansel And Gretel | Orphée | The Mikado
Visit us online at www.VaOpera.org or call 1-866-OPERA-VA

Experience the Beauty, Power & Passion of Virginia Opera.

This e-mail and any attached files are confidential and intended solely for the intended recipient(s). Unless otherwise specified, persons unnamed as recipients may not read, distribute, copy or alter this e-mail. Any views or opinions expressed in this e-mail belong to the author and may not necessarily represent those of Virginia Opera. Although precautions have been taken to ensure no viruses are present, Virginia Opera cannot accept responsibility for any loss or damage that may arise from the use of this e-mail or attachments.

{*}