Ask Search:
Zac OteroZac Otero 

Need to count Related Records on a Custom Object that meet criteria.

I am a new Admin and have found myself in need of some help.
I am trying to automate the closing process of my companies Calling Campaigns.

I currently have a lookup relationship from the Account Object to a Custom Object (Calling Campaign) to list them on the Calling Campaign record. However, The indicator that an Account has finished calling rounds is a checkbox (Close Initiative) that lives on the Account Object. What I need is a way to indicate when ALL Account records associated to a Calling Campaign have been marked as closed (Close Initiative=true) so that I can build my closing process off of that indication.
I'm not sure of the best way to accomplish this. I feel like a Formula Field could do the job? but as a new Admin, I am a complete novice at writing Formulas.

Any suggestions would be helpful!

"Help me SteveMo-Kenobi, You're my only hope"      

Best Answer chosen by Zac Otero
Mayank SrivastavaMayank Srivastava
Zac, I can think of 2 ways to do this:

1. Using a Roll-Up App
You can use either Roll Up Helper or Declarative Roll Up Summaries tool.  Links below:
https://github.com/afawcett/declarative-lookup-rollup-summaries
Roll Up helper:
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI

I recently did a blog post on how to use DLRS for a bunch of common scenarios so that should help you set it up:
http://succeedwithsalesforce.com/going-beyond-native-roll-up-summary-fields/

After having installed the above, you need to:

Create three custom fields. Two of type number and third would be a formula field.
The two number fields would be populated using the aforementioned App:
Total Accounts: To store the total number of accounts associated to a Campaign
Total Closed Accounts: To store the number of accounts with Close Initiative = True

Formula field: Checkox type with a formula:
Total_Accounts__c = Total_Closed_Accounts

It would show a checkmark when all associated accounts to a campaign have been closed.

2. Another way of doing this is to use a Process + Flow
The Flow would eliminate the creation of the two custom fields and the whole logic of doing the roll up would reside inside the Flow. The final action in the flow would be to populate a custom field of checkbox type with a True or False depending upon the logic result.
http://www.salesforceweek.ly/2015/02/how-to-create-roll-up-summaries-using-flow.html

You can use the above link as reference however the Flow that you will implement will a little more complex than what has been described in the link above.

3. Apex trigger - Probably the most straightforward but would need developer assistance

4. Report charts- Not 100% sure on this and I would let a report expert chime in

All Answers

Mayank SrivastavaMayank Srivastava
Zac, I can think of 2 ways to do this:

1. Using a Roll-Up App
You can use either Roll Up Helper or Declarative Roll Up Summaries tool.  Links below:
https://github.com/afawcett/declarative-lookup-rollup-summaries
Roll Up helper:
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI

I recently did a blog post on how to use DLRS for a bunch of common scenarios so that should help you set it up:
http://succeedwithsalesforce.com/going-beyond-native-roll-up-summary-fields/

After having installed the above, you need to:

Create three custom fields. Two of type number and third would be a formula field.
The two number fields would be populated using the aforementioned App:
Total Accounts: To store the total number of accounts associated to a Campaign
Total Closed Accounts: To store the number of accounts with Close Initiative = True

Formula field: Checkox type with a formula:
Total_Accounts__c = Total_Closed_Accounts

It would show a checkmark when all associated accounts to a campaign have been closed.

2. Another way of doing this is to use a Process + Flow
The Flow would eliminate the creation of the two custom fields and the whole logic of doing the roll up would reside inside the Flow. The final action in the flow would be to populate a custom field of checkbox type with a True or False depending upon the logic result.
http://www.salesforceweek.ly/2015/02/how-to-create-roll-up-summaries-using-flow.html

You can use the above link as reference however the Flow that you will implement will a little more complex than what has been described in the link above.

3. Apex trigger - Probably the most straightforward but would need developer assistance

4. Report charts- Not 100% sure on this and I would let a report expert chime in
This was selected as the best answer
Tarun KhatriTarun Khatri
Hello Zac,

Seems like you want to fetch data from child object (Account) to Parent (Calling Campaign) which are in lookup relationship.
For that you can write one trigger on Account (On Insert/Update) which will fetch all accounts associated with that Calling Campaign and check the number of records which are not closed. If all associated account records get closed, your code will update the parent Calling Campaign record as Closed.

-Thanks,
TK
Zac OteroZac Otero
Thank you Mayank! 
I was originally thinking that your first option would work, but was limited by the type of relationship. The Rollup Helper seems like it will make this option work without having to change my current architecture. Thanks so much!