Pulling Emails of Individuals within a Household

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)

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?

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?