Creating a list builder element for one interest only

Hey Everyone. My organization ran into an issue the other day and I was hoping for some guidance.  We are currently tracking our out-outs through Constituent Interests and would like to have list builder elements dedicated to the opt out parameters so that it is a bit more fool-proof for the users making lists/extractions.

The issue we are running into is that the custom interest element made in T_Keywords is not constraining the element to only one tkw id.  Can anyone point me in the right direction on how to get this element constrained to only one tkw from VSX_CUST_TKW?

Thank you in advance for any help or guidance.

 

Technical details on the current setup:

The T_KEYWORD entry reads:

Keyword No = 449

Description = Opt out of Paper Mailing*

Data Type = String

Edit Mask = None

Detail Tbl = VSX_CUST_TKW

Detail Col = !.selected

Ref Tbl = TR_GOOESOFT_DROPDOWN

Ref Idcol = short_desc

Ref Desccol = description

Ref Where = code=1 AND id=1

Ref Sort = (NULL)

Category = Contact Preferences*

Use for List = List Only

Multiple Value = N

Control Group = Default Control Group

Custom ID= (NULL)

Custom Limit =(NULL)

Custom Required = N

Custom Default Value = (NULL)

Incl In Basic Search = N

Keyword Desc = Customer opts out of receiving direct mail

Primary Group Default = Default Value No

Parent Table = (NULL)

Parent Key Column = (NULL)

Key Column = (NULL)

Using this setup we are getting the following: no constraints Where a1.tkw in (43)

Select Distinct a.customer_no 

 From V_CUSTOMER_WITH_PRIMARY_GROUP a  WITH (NOLOCK)

 JOIN (Select a1.customer_no From VXS_CUST_TKW a1 WITH (NOLOCK) Where a1.selected in ('Y')) as e ON e.customer_no = a.customer_no

 Where  IsNull(a.inactive, 1) = 1 

 

Parents
  • You need to reference both the "selected" field and the tkw id.  To do this without creating a custom view, the dropdown values need to be changed so that your users select the interest instead of "Y' or "N".

    The following changes should work:

    1. Change Detail Col to CAST(!.tkw AS VARCHAR) + '|' + !.selected

    2. Change Ref IDcol to CAST(id AS VARCHAR) + '|Y'

    3. Change Ref Tbl to VRS_TKW

    4. Change Ref Where to used_in in ('I', 'B')

    This keyword will then limit your list to customers who have the interest selected using one criteria.

    Kevin.

     

     



    [edited by: Kevin Holmes at 10:29 AM (GMT -6) on 26 Aug 2015]
Reply
  • You need to reference both the "selected" field and the tkw id.  To do this without creating a custom view, the dropdown values need to be changed so that your users select the interest instead of "Y' or "N".

    The following changes should work:

    1. Change Detail Col to CAST(!.tkw AS VARCHAR) + '|' + !.selected

    2. Change Ref IDcol to CAST(id AS VARCHAR) + '|Y'

    3. Change Ref Tbl to VRS_TKW

    4. Change Ref Where to used_in in ('I', 'B')

    This keyword will then limit your list to customers who have the interest selected using one criteria.

    Kevin.

     

     



    [edited by: Kevin Holmes at 10:29 AM (GMT -6) on 26 Aug 2015]
Children
  • I am wrong about changing the dropdown completely to selecting the interest... You could still limit it to selecting "Y" for your users by changing two elements in what I posted:

    RefDescCol should be (SELECT 'Y')

    Ref Where should be  id = 449

    List Manager throws an error if RefDescCol is just 'Y'.

     



    [edited by: Kevin Holmes at 10:44 AM (GMT -6) on 26 Aug 2015]