I am trying to figure out a best way to identify accounts with no activity for last x days. Account has a field called LastactivityDate which has the date when the activity was last logged.

But i need to exclude the activity which has used the mass email. All activity using Mass email is suffixed with "Mass Email" in subject. So using lastactivtyDate might not be possible.

One way i could think of is this

Create a custom field in account to log the activity date for non mass emails. for this write trigger on account to check all events and tasks which do not have Mass email in the subject and capture the latest activity date and update to this field if this field is null and if the field is filled in then check for tasks and events >= value in the custom field.

Today() - newcustomfield should give no of days since last activity.

Is there any other better approach ?

Attribution to: Prady

Possible Suggestion/Solution #1

Create a custom LastActivityDate field on Account.

A trigger on Task to update the LastAcivityDate custom field only if the subject doesn't have MassEmail in it.

This makes sure you always have the date available, rather than having to calculate at run time.

To use you can simply use a Where clause

Where LastActivityDate__c < Date.today().addDays(-60)

This will only work for activities logged here on, if you need to backfill accounts, you may have to consider BatchApex or similar.

Attribution to: techtrekker
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4840