0

I am trying to do reports on Contact and Account. I'm trying to create a filter to show an Account if all the related contacts has a field named "Important" set to false.

Right now, I just have Important equals False but it is showing an account even though one of the contacts on that account has the "Important" field set to true.

  • You should use dlrs to create a conditional lookup rollup summary here. – Adrian Larson Jan 11 '17 at 22:22
  • What is a dlrs? Do you have any references about it? – akcorp2003 Jan 11 '17 at 22:25
  • That should be really easy google fodder...github.com/afawcett/declarative-lookup-rollup-summaries – Adrian Larson Jan 11 '17 at 22:27
  • Sorry... I didn't search the github stuff :( . It looks like a managed packaged and my organization needs to approve managed packages. Can Salesforce do the filtering I want out-of-box? – akcorp2003 Jan 11 '17 at 22:30
  • 1
    If DLRS is out of the question, then your Account object needs three custom fields: count of Contacts, count of Important Contacts and formula field AreAllContactsImportant__c that is true if the first two fields have the same value and are greater than zero. Populating those first two fields requires apex as ProcessBuilder won't run on deletes – cropredy Jan 11 '17 at 23:03
1

You could do this w two cross filters on an account report type. But, you couldn't see the contact data without creating a custom accounts with contacts report type.

Standard Report Type

Create a report on accounts (record type equals accounts). Cross filter one. Accounts with contacts, where important equals false. Cross filter two. Accounts without contacts, where important equals true.

Cross filters are always AND so this will show you accounts with at least one contact where important equals dalse and without any contacts where important equals true.

Custom Report Type

If you need to see the contact fields, you need to build a custom report type, with primary object = Accounts and secondary object = Contacts.

Then you can create a report, add cross filters as above, and see the contact data. I used email opt out as the checkbox in my testing, but same idea applies.

sampleWithCRT

  • Your solution does indeed (at a preliminary test) works correctly as I wanted. However, is there any way to show the contact fields? – akcorp2003 Jan 12 '17 at 16:31
  • looks like a custom report type on accounts with contacts will let you cross filter against contacts. The standard contacts and accounts report type is 'special' in a number of ways, and will only let you run cross filters on contacts. I think the standard report type is effectively like a contact report type where you add account fields via lookup, and it has some magic where it will sub account address if contact address is blank, perhaps other stuff. I'll update my answer to reflect CRT option. – gorav Jan 12 '17 at 16:57
  • Thanks for the update! I ran it with the custom report and the data displayed is as expected. The only slight inconvenience is that the account name shows up once for each contact tied to the account. Is there a way to just display the account name once? – akcorp2003 Jan 12 '17 at 17:37
  • You could group by account name – gorav Jan 12 '17 at 20:04
  • 1
    Cross filters FTW. They are the best. – gorav Jan 13 '17 at 23:33

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.