ApexSensei

Drills › Query the org (SOQL)

Find missing Contact emails

Query the org (SOQL) · Beginner · 12 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A data-quality report must tell apart Contacts that can get email from those with no address saved.

Query tagged Contacts with and without an Email, including the edge case of a blank Email.

What you will learn

SOQL uses = null and != null to find fields that are empty or filled in. Join two conditions with AND to require both: WHERE A AND B. OR works the same way.

SELECT Id FROM Contact
WHERE AccountId = :parent.Id AND Email = null

Gotcha. Salesforce saves an empty text value as null. An Email set to '' shows up in the = null results.

The task

Split this run's Contacts into those with no Email and those with an Email.

Examples:

  • Email null and Email '' → both missing → Null missing: 2
  • a real Email → present → Null present: 1
  • the blank Contact is in the missing list → Null blank is missing: true

Requirements:

  1. Keep both queries to this run's Contacts with AccountId = :parent.Id.
  2. Query missing Emails with Email = null in the WHERE clause.
  3. Query present Emails with Email != null in the WHERE clause.
  4. Print both counts, whether the blank Contact is missing, and whether the present Contact stayed out of the missing list. Print Null missing:, Null present:, Null blank is missing: and Null present excluded:.

Starter code

String tag = 'AS-Q305-' + UserInfo.getUserId() + '-' +
    String.valueOf(Datetime.now().getTime());
Account parent = new Account(Name = tag);
insert parent;
String presentEmail = 'q' + String.valueOf(Datetime.now().getTime()) + '@example.com';
List<Contact> seeded = new List<Contact>{
    new Contact(LastName = tag + '-Null', AccountId = parent.Id, Email = null),
    new Contact(LastName = tag + '-Present', AccountId = parent.Id, Email = presentEmail),
    new Contact(LastName = tag + '-Blank', AccountId = parent.Id, Email = '')
};
insert seeded;

List<Contact> missing = new List<Contact>();
List<Contact> present = new List<Contact>();
System.debug('Null missing: ' + missing.size());
System.debug('Null present: ' + present.size());
System.debug('Null blank is missing: false');
System.debug('Null present excluded: false');

When it passes

Two Contacts are missing an Email, one has one, and the blank Email acts like null.

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

‹ Query with a Set of Ids · Read a Contact's Account ›