Hello!
I am executing a output set for Name/Email. There are certain cases where there is only an email associated with an individual within the household, so the email field shows up blank for the household. Is there a way to pull all the emails within a household? Or must the email be saved on the household level in order to do this.
Thanks!
Michelle
Hi Michelle,
Does your list use the "Swap HH" feature? That comes from selecting the "Replace individual constituents with their primary households, if one exists" option on the Advanced Relationship Options tab in Last Manager.
Our business practice doesn't allow email addresses to be stored on HH records so for emails lists, we return Individuals by using the Search HH checkbox on the appropriate list manager criteria. We further limit the list to people with email address by adding the Eaddress Type criteria. On that specific criteria you wouldn't want to check Search HH.
The query below creates a view with all email addresses in a household collapsed into one field based on expanded customer number. Perhaps you could use it to create an Output Set Element that gets you what you're looking for? My only caution is that it isn't limited to A1 and A2 members of the household so children's emails could be returned as well.
SELECT DISTINCT a.expanded_customer_no, STUFF((SELECT '; ' + e.address FROM T_EADDRESS AS e JOIN V_CUSTOMER_WITH_PRIMARY_GROUP AS c ON e.customer_no = c.customer_no WHERE c.expanded_customer_no = a.expanded_customer_no ORDER BY e.address FOR XML PATH('')), 1, 1, '') [email_address]FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
Thanks, this is helpful. I'm still relatively new to Tess. (we implemented just about a year ago), but I am following for the most part (although my knowledge of SQL is very limited).
Just curious, is there a reason to store email addresses on the individual record only? I was able to create a list that included the individuals (in addition to the household), however, since the list is pulling on the individual and HH level, there will be multiple rows with the same email. We are in the process of implementing WordFly, which suppresses duplicates, so hopefully this will be a non-issue in the future.
We are embarking on the same cleanup after three years and ensuring that emails are only on the HH as well. I am also struggling with the reminder email list. No matter what I do the Household is still pulling into the list when we only want the affiliated individuals. Ultimately once this is sent to wordfly it will not go anywhere because there is no email on HH but I would prefer a cleaner list. Are you willing to share a screenshot of your list criteria?