We are trying to integrate with a backup solution as an archival solution. In the UI, we have the ability to write a SOQL query that is to get the archival criteria on a per archival policy.

We would like to archive leads and opportunity records that have no activity.

So initially I did this:

select count(id) from Lead where Status_last_Modified_date__c < LAST_N_MONTHS:13 And Id not in(select whoid from Task)And Id not in(select whoid from Event)

That results in :

Entity 'task' is not supported for semi join inner selects

The next best approach that was recommended was to use something as follows:

FROM Lead 
WHERE LastModifiedDate < LAST_N_MONTHS:13 AND 
  LastActivityDate = NULL

However that would not work if we have activities with no Due Date which we do. Also some activities are archived.

The idea here is not to get all leads per one SOQL query but to have multiple archive policies that can cover all the lead records. Is there any other approach or SOQL we can employ to get some control over this? Thank You

  • Are you limited to SOQL only or do you have post-processing capability?
    – Adrian Larson
    Oct 6, 2022 at 16:08
  • Only SOQL unfortunately. Oct 6, 2022 at 17:52
  • Then unfortunately you may be stuck implementing a count rollup.
    – Adrian Larson
    Oct 6, 2022 at 17:53

1 Answer 1


I believe your only option to be implementing a rollup Activity_Count__c field. To do so, you will have to add some custom code or implement a common tool such as Declarative Lookup Rollup Summaries or RollupHelper (I have no affiliation to either). If you have the technical skills required, rolling your own is not particularly complicated. If you lack them, I strongly recommend installing one of these tools instead. If you roll your own rollup, you will also need a backfill batch.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .