0

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 ?

  • 1
    Using child relationships, get the created date of the last activity where subject is not in'Mass email' and createddate is beyond 60 days. if the list of these activities is greater than 1, update a checkbox on account say 'Inactive for 60 days'. – Tanuj Kumar Sharma Nov 28 '12 at 7:18
  • 1
    From Apex - something like salesforce.stackexchange.com/a/4164/799 should get you started (just add in the subqueries order by createddate desc limit 1 or sth, then loop through them and compare with System.today()). It'd have to be @future or VF page though - emails are sent at the end of the flow so trigger wouldn't catch the change to LastActivityDate I think. – eyescream Nov 28 '12 at 7:55
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.