AddThis Feed Button

Recent Items

Tags: Apex, SOQL

We recently reassigned a heap of Opportunities between staff members. However, the previous Opportunity Owner had open Activities on the Opportunities. The new Opportunity owners couldn’t close those Activities since they belong to somebody else.

So, they asked me to find a way to bulk-close the Activities.

This sounded simple, but was made more difficult by the fact that Activities can link to many different object types: Account, Opportunity, Campaign, Case or Custom Object.

The connection is made via WhatID, which is the ID of the associated object. It can be accessed via SOQL like this:

SELECT Id, What.Name from Task

However, not all fields are available, so you can’t SELECT What.OwnerId.

Fortunately, I found a forum post called Getting Object type of WhatId/WhoId Task/Event fields gave me a few hints, and I came up with this code:

// Get list of Opportunities owned by new person
Opportunity[] opps = [Select Id from Opportunity where OwnerId = '005200300014jeN'];

// Get incomplete Activities owned by previous person attached to the above Opportunities
Task[] tasks = [select Id, What.Name from Task where OwnerId = '00520000000tSOj' and Status != 'Completed' and WhatId in :opps];
for (Task t : tasks) { 
  t.Status = 'Completed';
}
update tasks;

This grabs a list of ‘owned’ Opportunities and checks for any Activities (which are actually Task objects) that have a WhatId matching those Opportunities.

Straight-forward and pretty simple. Almost makes up for not being able to traverse directly to the linked object.

The Bottom Line

  • Activities can link to multiple objects
  • They connect via WhatId, but only a limited number of fields are exposed, eg What.Name
  • Use an ‘IN’ comparison to match the Activities with Opportunities

2 Responses to “Using WhatId to find Opportunities associated with Activities”

  1. Matt Kaufman Says:

    Actually, you could do this with only 1 query as follows:

    List taskUpdates = new List();
    for ( Opportunity o : [Select Id, OwnerId, (Select Id, OwnerId from Tasks where Status != ‘Completed’ ) from Opportunity ){
    for ( Task t : o.Tasks ){
    if ( t.OwnerId != o.OwnerId ){
    t.Status = ‘Completed’;
    taskUpdates.add(t);
    }
    }
    }
    update taskUpdates;

  2. The Enforcer Says:

    @mkaufman Ah! The good old Nested Select trick! Very clever!