Product: Omatic Cloud

Description: This solution describes how to create a query in Altru that ignores records with a shared email

Environment: All

Versions: All

Video:


Answer:



There are times when multiple records in Altru may have the same email, such as separate spouse records. When sending both records to an email marketing system where email is a unique identifier this can cause problems with names overwriting and IDs not matching up correctly.


It is recommended to only send over the primary record from Altru to the email marketing system.


To send over just the primary record from Altru, two queries need to be created. The first will be used as a suppression query. It contains the non-primary records that share an email with a spouse. The second query is the query of records to be sent over to the email system where that suppression query is used as a filter.



Create the "Spouse with same Primary Email" Query

This query is used as a suppression query in the final query of records to send.


1. Select to Include the primary email for both the main constituent and the spouse: 

  • Email Addresses\Primary email address is equal to Yes 
  • Spouse\Email Addresses\Primary email address is equal to Yes

2. Wrap those filters in a set of parentheses

3. Add the email address for the main constituent as an output field:

  • Email Addresses\Email address

4. In the Include section, add the spouse's email is the same as the main constituent's email:

  • Spouse\Email Addresses\Email address is equal to <output field> Email Addresses\Email address


5. Add an Include field to indicate that this is not the primary household member:

  • Households Belonging To\Is primary contact is equal to No

6. Add any other output fields as needed for your review


7. On the Set save options tab, name the query "Spouse with same Primary Email" 

8. Under the Options for using this query, select

  • Create a selection?
    • Show this Selection in the Query Designer



Create the query of records to send to the email system

1. Some of the recommended filters to Include:

  • Email Addresses\Primary email address is equal to Yes
  • Email Addresses\Email address is not blank
  • Is individual is equal to Yes
  • Deceased is equal to No

2. Select the spouse suppression query just created

  • Selections\Spouse with same Primary Email (Ad-hoc Query) is equal to No


3. Include a date filter, such as Email Addresses\Date changed is yesterday

  • This will vary based on your organization's needs

4. Include a filter to exclude records last updated by the Omatic Cloud user

  • Changed by user name is equal to <Omatic Cloud user>

5. Include any other fields needed for your emailing list, such as membership dates, membership levels, giving amounts, interests, visit dates, and many more

6. Output should include at the very minimum the first name, last name, and email address

7. You may also want to output the Altru Lookup ID along with those other fields from step 5

8. If you've added the record ID from the email system as an Alternate Lookup ID, you can output that too

9. On the Set sort and group options tab, select any field for sorting the records