How do you create a table of Accounts without Activity?

Questions
,
1

I created a model based on the Account object, and used a subquery pointing to the Events object. I limited events to those set for this quarter, and want to return Accounts not in that subquery. I am getting no results, where a standard Salesforce report pulls up 58 records without issue.

I read this may have something to do with 15 character vs 18 character ids, but I have tried using a “safe id” version of my Account Id to match up with and neither it nor the standard Id field return any results.

2

SELECT Id FROM Account WHERE (Id in (SELECT WhatId FROM Event WHERE (((StartDateTime = THIS_QUARTER))))) LIMIT 21 Error:Entity ‘Event’ is not supported for semi join inner selects

3

There’s another way to do this though. Stay tuned.

4

K… can’t think of way to do this without using DLRS and formula fields. Somewhat janky.

  1. Create a DLRS rollup from Event to Account set to Max and Aggregate by your Date.
  2. Then use this date field in your Account model condition.

5

What is DLRS?  I cannot use a standard Rollup Summary Field as I need my date range to be dynamic and roll from quarter to quarter.  Now I could update the rollup summary fields each quarter I suppose.  I’ll give that a try, but if I am missing something else, please let me know.  Thanks so much for the response!

6

Declarative Lookup Rollup Summary. Great Add-on package that enables rollups on lookups. Really slick must have in every org.

https://github.com/afawcett/declarative-lookup-rollup-summaries

https://login.salesforce.com/packaging/installPackage.apexp?p0=04tb0000000MXkF

7

Thanks so much for the suggestion.  However, the app above has now been deprecated.  Any other ideas?

8

Michael - here’s the most recent package URL: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t0N000000IyYr

You can find updates on the github page: https://github.com/afawcett/declarative-lookup-rollup-summaries