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

I run into this problem all the time.

I want to write a quite routine in the System Log window to Mass Update some records (eg my previous Mass Delete via System Log window blog post). I want to find all records before a certain date, but SOQL never likes my date format, eg:

select Id from Opportunity where Expiry_Date__c < 2010-01-01

Yes, it is possible to convert it to Timezone format and do it this way:

select Id from Opportunity where Expiry_Date__c < 2008-01-01T00:00:00Z

but I’ve always thought that silly when comparing against a Date field.

So, I eventually figured out that I can do it this way:

select Id, Name, LastLoginDate from User where LastLoginDate > :Date.valueOf('2008-01-01')

Of course, this only works within the context of Apex, such as the System Log window. It won’t work in pure SOQL tools like SOQL Explorer. Here’s an example:

Opportunity[] opps = [select Id from Opportunity where Expiry_Date__c < :Date.valueOf('2010-01-01')];
System.Debug(opps.size());

The Bottom Line

• Date strings can’t be entered into SOQL
• Option 1: Use Timezone format: 2010-01-01T00:00:00Z
• Option 2: Convert from string: :Date.valueOf(’2010-01-01′)

Here’s a lesson I learned while making our Cloud Developer Challenge entry.

Take a look at these lines of code and tell me which ones are OK and which ones are dangerous:

integer count = [SELECT count() FROM Contact];
Contact c1 = [SELECT Id FROM Contact LIMIT 1];
Contact c2 = [SELECT Id FROM Contact LIMIT 1];
Id first = [SELECT Id FROM Contact LIMIT 1].Id;
Contact[] contacts = [SELECT Id FROM Contact LIMIT 1];
Contact[] allContacts = [SELECT Id FROM Contact];

If you executed the above code on most systems, it will run just fine. However, there is a situation in which you’ll get the dreaded List has no rows for assignment to SObject error. Can you figure out which one?

It’s when there’s no objects to return!

For example, if you have a system with no Contracts, try running the above code with Contract in place of Contact and you’ll get:

ERROR - Evaluation error: System.QueryException: List has no rows for assignment to SObject
ERROR - Evaluation error: AnonymousBlock: line 2, column 15

What happened? Well, these 3 lines will all give an error if there’s no row found:

Contact c1 = [SELECT Id FROM Contact LIMIT 1];
Contact c2 = [SELECT Id FROM Contact LIMIT 1];
Id first = [SELECT Id FROM Contact LIMIT 1].Id;

While a SELECT normally returns an array/list, these statements are using the shorthand syntax that assumes only one row is returned. What’s not obvious is that it also assumes that exactly one row is returned!

While this is unlikely to occur for Contact, it is highly likely to occur for any custom objects you create, especially when a WHERE statement is used that might return zero rows, such as:

Player__c player = [SELECT Id from Player__c where Name = :username];
if (player != null)
    p = player.Id;

The above code will fail if there is no Player__c record with the matching username. It doesn’t actually return a null.

It would be safer to do the following:

Player__c[] players = [SELECT Id from Player__c where Name = :username];
if (players.size() > 0)
    p = players[0].Id;

It’s one of those situations for which you wouldn’t normally think of creating a test, so it’s safer to just avoid the possibility rather than making an assumption about your data.

The Bottom Line

• The shorthand syntax that expects one row to be returned from a SELECT statement is very handy
• It’s also dangerous if no rows are found!