How do you create a table of Accounts without Activity?

  • 1
  • Question
  • Updated 5 months ago
  • Answered
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.
Photo of Michael Barnes

Michael Barnes

  • 200 Points 100 badge 2x thumb
  • frustrated

Posted 2 years ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
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
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
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.
Photo of Michael Barnes

Michael Barnes

  • 200 Points 100 badge 2x thumb
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!
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
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
Photo of Michael Barnes

Michael Barnes

  • 200 Points 100 badge 2x thumb
Thanks so much for the suggestion.  However, the app above has now been deprecated.  Any other ideas?