function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jvolkovjvolkov 

How many open activities does a record have?

Need to return in a view or a  report alll leads or accounts that do not have an open activit. 

 

Want to create a field that returns how many open activities a record has.  Is there a way to this using custom formula field or reports?

 

-Thanks

NPMNPM
I do not know how this is possible with the field approach but you can set up a custom report type with Accounts as the Primary object and Activities as the related object.  You would want to select "A" records may or may not have related "B" records.  Then you can set your report criteria to return only accounts without open activities.
JakesterJakester
This sounds like it would work, but in reality it doesn't. Salesforce can't really do an outer join, which is what you need in order to run this query. This is one of many ways that I use forceAmp's excellent product DBAmp which replicates your salesforce data down into a local SQL database so you can run regular SQL queries on it.
NPMNPM

That is true, tried it and there is no way to set the report criteria to filter on activities being open or not.  Sorry I did not think this one through far enough.

 

Thanks for the catch Jakester.

JakesterJakester
No worries! It certainly sounds like it work - very frustrating that it doesn't!
AnyaAnya

Hi folks.

 

Therefore, I cannot print a report of all my opportunities that have no open tasks?

 

Thank you!

 

Anya

JakesterJakester
Not using Salesforce Reports. There are lots of add-ons that will make this possible. My preferred method, because we already had SQL server and I am comfortable in that environment, is DBAmp which is made by forceAmp.com - it makes this report (and tons of other reports) very easy to do.
cgillinghamcgillingham
I need this functionality in SalesForce.... I really need to be able to show managment this info in a dashboard.  Anyone find a work around that doesn't require pulling data out of salesforce?  Any apps that integrate with SalesForce?
JakesterJakester
Seems to me this might be possible with a trigger on the notes table. It would fire any time a task was created, as well as any task's status had changed, and would probably do something like increment/decrement a counter on the Opportunity. Then you could run a report to show all open opportunities with a counter set to zero. I would ballpark that this would be 4-8 hours of developer time. There are many developers out there that would be interested in the work - I have a friend I could refer you to that does good work - let me know if you're interested.
jvolkovjvolkov
You can create a Next Activity field on the lead, contact, and account object.  Then write an APEX trigger that updates this field by taking the task date and inserting into the lead/contact/account Next Activity field.