How do you create a table of Accounts without Activity?

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.

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

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

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.

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!

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

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

Michael - here’s the most recent package URL:

You can find updates on the github page: