• Replies 10 replies
  • Subscribers 336 subscribers
  • Views 324 views
  • Users 0 members are here
Related

First name blank in lists when there is a household

Former Member
Former Member $organization

Hello Tessitura world!

I'm trying to work on our triggered emails (WordFly) and running into an issue with personalization. If the constituent is a household, the list pulls their first and last names into the "CUSTOMER_LNAME" column, whereas if the constituent is an individual they put the first name in "CUSTOMER_FNAME" and the last name in the "CUSTOMER_LNAME" column. Does anyone know how to reliably have the correct names in the correct columns? I'm only willing to have *so many* emails addressed to "Friend", ya know? 

Similarly, is there a way to have the word "Household" come off the record? It autopopulates on the constituent record and it's annoying to clear off when creating print campaigns.

Thank you all!

Corinne

Parents
  • $organization

    We work around this within the view for WF, but we're only pulling in fname for now... something like this should get you started. We then have custom output set elements referencing this View. 

    Code Credit: 

    ALTER VIEW [dbo].[LV_2ST_PRESHOW]
    AS


    SELECT DISTINCT
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then ini.customer_no
    else a.customer_no
    end as 'customer_no',
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then iniea.address
    else c.address
    end as 'eaddress',
    v.description as perf_name,
    DATENAME(dw, d.perf_dt) as perf_day,
    --d.perf_dt as friendly_perf_dt, -- converting to "friendly" date in Query Elements
    format (d.perf_dt, 'h:mm tt') as perf_time,
    d.time_slot,
    convert(varchar,perf_dt,101) as 'perf_dt', --day without perf time to match with list manager criteria
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then (case when ini.fname is null then ininame.fname else ini.fname end) --if initiator exists and is a HH, use fname of prim aff of HH
    else (case when e.fname is null then g.fname else e.fname end)
    end as 'fname',
    a.order_no,
    d.prod_season_no
    FROM dbo.T_ORDER a (nolock)
    join T_SUB_LINEITEM b (nolock) on a.order_no = b.order_no
    outer apply
    (select top 1 address, customer_no
    from T_EADDRESS ea (nolock)
    where a.customer_no = ea.customer_no
    and ea.primary_ind = 'y' and ea.inactive = 'n'
    order by last_update_dt desc) as c --get the most recently updated active primary email address on the order owner account
    join T_PERF d on d.perf_no = b.perf_no and d.perf_status = 1 --select * from TR_PERF_STATUS
    join T_INVENTORY v on v.inv_no = d.perf_no
    join dbo.TR_SEASON AS s ON d.season = s.id

    --these tables are joined to get order initiator information / order initiator affiliate information (to pull a fname if the order was on a HH record)
    left outer join T_CUSTOMER ini on ini.customer_no = a.initiator_no
    outer apply
    (select top 1 address, customer_no
    from T_EADDRESS ead (nolock)
    where ini.customer_no = ead.customer_no
    and ead.primary_ind = 'y' and ead.inactive = 'n'
    order by ead.last_update_dt) as iniea --get the most recently updated active primary email address on the initiator account
    left outer join T_AFFILIATION iniaf on iniaf.group_customer_no = ini.customer_no and iniaf.name_ind = -1
    left outer join T_CUSTOMER ininame on ininame.customer_no = iniaf.individual_customer_no

    --these tables are joined to get order owner affiliate information (to pull a fname if the order was on a HH record)
    join T_CUSTOMER e (nolock) on e.customer_no = a.customer_no
    left outer join T_AFFILIATION f ON f.group_customer_no = e.customer_no and f.name_ind = -1
    left outer join T_CUSTOMER g (nolock) ON g.customer_no = f.individual_customer_no

    WHERE (
    (isnull(ini.customer_no,0) > 0
    and isnull(ini.customer_no,0) <> a.customer_no
    and e.cust_type not in (1,8)
    and exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = ini.customer_no and primary_ind = 'Y' and inactive = 'N')
    ) --for cases where there is an initiator, initiator must have email on file

    or exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = a.customer_no and primary_ind = 'Y' and inactive = 'N') --otherwise, order owner
    ) --must have email
    AND b.sli_status IN (3, 12) --select * from TR_SLI_STATUS where id in (3,12)

    AND (d.perf_dt BETWEEN CONVERT(DateTime, DATEdiff(DAY, -1, getdate())) AND --beginning of tomorrow
    DATEADD(SECOND, - 1, CONVERT(DateTime, DATEDIFF(DAY, -2, getdate())))) --end of tomorrow
    AND d.perf_no not in (select inv_no from TX_INV_TKW where tkw in (19,20,21)) -- Devo, Dummy, and Champ Nights select * from TR_TKW where id in (19,20,21)

    GO

Reply
  • $organization

    We work around this within the view for WF, but we're only pulling in fname for now... something like this should get you started. We then have custom output set elements referencing this View. 

    Code Credit: 

    ALTER VIEW [dbo].[LV_2ST_PRESHOW]
    AS


    SELECT DISTINCT
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then ini.customer_no
    else a.customer_no
    end as 'customer_no',
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then iniea.address
    else c.address
    end as 'eaddress',
    v.description as perf_name,
    DATENAME(dw, d.perf_dt) as perf_day,
    --d.perf_dt as friendly_perf_dt, -- converting to "friendly" date in Query Elements
    format (d.perf_dt, 'h:mm tt') as perf_time,
    d.time_slot,
    convert(varchar,perf_dt,101) as 'perf_dt', --day without perf time to match with list manager criteria
    case
    when (
    isnull(ini.customer_no,0) > 0 --initiator exists
    and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
    and e.cust_type not in (1,8) --order account is not HH/Indv
    )
    then (case when ini.fname is null then ininame.fname else ini.fname end) --if initiator exists and is a HH, use fname of prim aff of HH
    else (case when e.fname is null then g.fname else e.fname end)
    end as 'fname',
    a.order_no,
    d.prod_season_no
    FROM dbo.T_ORDER a (nolock)
    join T_SUB_LINEITEM b (nolock) on a.order_no = b.order_no
    outer apply
    (select top 1 address, customer_no
    from T_EADDRESS ea (nolock)
    where a.customer_no = ea.customer_no
    and ea.primary_ind = 'y' and ea.inactive = 'n'
    order by last_update_dt desc) as c --get the most recently updated active primary email address on the order owner account
    join T_PERF d on d.perf_no = b.perf_no and d.perf_status = 1 --select * from TR_PERF_STATUS
    join T_INVENTORY v on v.inv_no = d.perf_no
    join dbo.TR_SEASON AS s ON d.season = s.id

    --these tables are joined to get order initiator information / order initiator affiliate information (to pull a fname if the order was on a HH record)
    left outer join T_CUSTOMER ini on ini.customer_no = a.initiator_no
    outer apply
    (select top 1 address, customer_no
    from T_EADDRESS ead (nolock)
    where ini.customer_no = ead.customer_no
    and ead.primary_ind = 'y' and ead.inactive = 'n'
    order by ead.last_update_dt) as iniea --get the most recently updated active primary email address on the initiator account
    left outer join T_AFFILIATION iniaf on iniaf.group_customer_no = ini.customer_no and iniaf.name_ind = -1
    left outer join T_CUSTOMER ininame on ininame.customer_no = iniaf.individual_customer_no

    --these tables are joined to get order owner affiliate information (to pull a fname if the order was on a HH record)
    join T_CUSTOMER e (nolock) on e.customer_no = a.customer_no
    left outer join T_AFFILIATION f ON f.group_customer_no = e.customer_no and f.name_ind = -1
    left outer join T_CUSTOMER g (nolock) ON g.customer_no = f.individual_customer_no

    WHERE (
    (isnull(ini.customer_no,0) > 0
    and isnull(ini.customer_no,0) <> a.customer_no
    and e.cust_type not in (1,8)
    and exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = ini.customer_no and primary_ind = 'Y' and inactive = 'N')
    ) --for cases where there is an initiator, initiator must have email on file

    or exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = a.customer_no and primary_ind = 'Y' and inactive = 'N') --otherwise, order owner
    ) --must have email
    AND b.sli_status IN (3, 12) --select * from TR_SLI_STATUS where id in (3,12)

    AND (d.perf_dt BETWEEN CONVERT(DateTime, DATEdiff(DAY, -1, getdate())) AND --beginning of tomorrow
    DATEADD(SECOND, - 1, CONVERT(DateTime, DATEDIFF(DAY, -2, getdate())))) --end of tomorrow
    AND d.perf_no not in (select inv_no from TX_INV_TKW where tkw in (19,20,21)) -- Devo, Dummy, and Champ Nights select * from TR_TKW where id in (19,20,21)

    GO

Children