[SalesForce] How to filter activities by record type

Issue

How can I filter the ActivityHistories sub-query by record type? When I use record type in the query it's saying the field isn't valid.

Background

I'm doing a custom last activity date for a hiearchy of accounts the excludes certain tasks and have run into a lot of issues handling the bulk cases since the task table doesn't support aggregate fields on ActivityDate.

In theory I'd do a query like this given a set of accountIds 

select max(activityDate), accountId
from Task
where included__c = true
and accountId in :accountIds
group by accountId

While that approach fails I'm nearly there with this query

select 
  (select activityDate
   from ActivityHistories
   where isTask = true
   and included__c = true
   limit 1)
from Account
where id in :AccountIds

But now I need to filter on record types and it's given me errors. Is there any way to filter on record type in the ActivityHistories query?

Best Answer

You can workaround this by creating a text field to store the record type developer name and use a workflow to push a value into the field. In theory a custom activity formula field, i.e. RecordType.DeveloperName, would work, but in practice the value is null when returned in an ActivityHistory sub-query.

Formula Field

RecordType.DeveloperName

SOQL Query

select 
  (select activityDate
   from ActivityHistories
   where isTask = true
   and included__c = true
   and recordTypeDeveloperName__c = 'ExcludedRecordType'
   limit 1)
from Account
where id in :AccountIds
Related Topic