6

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?

Improve this question
8

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 Cross Filter

Improve this answer
  • what do you mean - sub-filter? – Saariko Mar 20 '13 at 20:01
  • The sub filter is the "Email not equal to blank" portion. It means that showing all accounts with contacts that have an email. The docs for cross filter (in the answer) have a video that will walk you through everything (couldn't link to the video tutorial directly unfortunately) – Ralph Callaway Mar 20 '13 at 21:41
  • @Saariko did this work for you? – Ralph Callaway Mar 27 '13 at 20:04
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.

Improve this answer
2

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?

Improve this answer
  • the hide details means, that the report is 'not clean' - in order for reporting, I need the clean numbers. – Saariko Mar 20 '13 at 20:07
  • 1
    That wouldn't do what the @Saariko wants. It'd return all accounts that have a contact without an email even if that same account has another contact with an email – Ralph Callaway Mar 20 '13 at 21:42
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.