Hi,
We would like to send emails to ticket buying households and our marketing department is really set on having the first name salutation. The issue I'm having is that we keep the ticket history on the household, so the list criteria is pulling those records, but the First Name field, that I have in output set pulls blank data for households since there is no First Name in household records.
Has anyone figured out a way (aside from creating a separate salutation) to create an output set criteria with the first name field from the A1 or the A2 affiliate of the household?
Thanks,
Tiffany
Perhaps you can make a list of patrons in households whose e-mail addresses also exist in the household, and then, for those, pull the individual record only.
I just put together this query that should at least be a starting point, if you substitute the “##” for the id of your Household customer type as it appears in TR_CUST_TYPE. You might need to limit the e-mail address type you are looking at, though.
This can be pasted into the Manual Edit area of List Manager. Leave individuals as selected by the list. The resulting list should all be Household accounts, and you can suppress them in your extraction.
select a.expanded_customer_no
from V_CUSTOMER_WITH_HOUSEHOLD a (nolock)
join T_CUSTOMER b (nolock) on b.customer_no = a.customer_no
join T_EADDRESS c (nolock) on c.customer_no = b.customer_no
join T_EADDRESS d (nolock) on d.customer_no = a.expanded_customer_no
Where IsNull(a.inactive, 1) = 1
and a.expanded_customer_no in (select customer_no from T_CUSTOMER where cust_type = 13)
and a.expanded_customer_no <> b.customer_no
and c.address = d.address
Lucie
______________________________Lucie SpielerIT Development and Training Manager