How to get accounts where their contacts has no email address?

Question

I need to report on all accounts, whom have not a single contact with email.

e.g., an account may have any number of contacts, if neither of the contacts has an email, I want to see that in a report.

The regular filters don't provide me such - if I select a filter with: email not to - I will get these accounts that have a contact with no email - but it does not ignore the contacts that do have an email. so if an account has 5 contacts, where one does not have an email - it will show on that report.

How to avoid that?

Answer 1

This is easily done with standard reporting using the relatively new feature for cross filters. Create an accounts report than add a cross filter for accounts without contacts, and then add a sub-filter that they must have an email.

Example

Answer 2

Create a rollup field that is a count of Contacts and then filter that count based on having a contact.

You can then report on any account with a value of 0.

Answer 3

In SOQL:

SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE Email = null)

In pure reporting - make a report on "Contacts" or "Contacts with Account", add filter criteria 'Email equal to ' (blank).

Flip it to summary report and summarize by Account Name (or Id?). Hide details. Should be pretty close?

Answer 4

I think you're approaching this from the "wrong end" so to speak. You need to look at this from the contacts end of things.

I'm not certain, but you may need to create a custom query. The query would look like this:

List <Contacts> noAcctEmls = [SELECT Name, Email, Account.Id, Account.Name FROM Contact WHERE Email = null GroupBy Account.Name] 

You could then sort on those results to see if any account had contacts where all contacts had no email addresses. To help with that, you could had aggregates on Account.Name or perhaps put a Count on that and use some of the other SOQL options for queries that might be of use to you.