How to get Email from Task object record using SOQL

Question

I am trying to get the Contact/Lead email from the Task object using SOQL (I am creating an interface in PHP to back up messages with a specific subject). Here is my query right now:

SELECT Subject,Who.FirstName,Who.LastName,Who.Email,Who.Phone,Description FROM Task

This works/doesn't throw an error and gives me results, but Who.Email is always empty (and, coincidentally Who.Phone is as well, but it is not very important for this). If I try just using Email I get an error that the field doesn't exist, which is weird because Email is under Task Fields as a standard field.

I have also tried several google searches with no sort of assistance found.

Punic

Answer 1

The SOQL Polymorphism article linked to by Zach above works perfectly.

SELECT Subject,
  TYPEOF Who
    WHEN Lead THEN Email
    WHEN Contact THEN Email
  END
FROM Event

Mockup

I'm making this the answer as it is much, much more up to date. Its crazy to think how many versions of Salesforce have been released since I first attempted this in Feb 2013. – Punic

Answer 2

Because the WhoId and WhatId fields of Task are polymorphic (i.e. can point to many different kinds of objects) you can't just query relationships through them like you can for normally-related objects. Instead, you'll have to do 2 SOQL queries, the first to get the Task information and the second to get the info from the Contact or Lead that the Who is pointing to.

Estragon

This was what I eventually did as my solution, its too bad there wasn't something more elegant. – Punic

"you can't just query relationships through them" --- this is only partially-true. If you were to query "Who.FirstName", you would get results. Supposedly all fields on the "Name" API Object can be queried across polymorphic relationships without issue --- and indeed, a query for "select Who.Email from Task" does not fail, it just doesn't return data for certain fields on the Name object, but DOES for other fields on the Name object, even when the Who DOES have data for those fields! This is very inconsistent behavior, in my opinion. – Mellicent

In fact zachelrath is right. At the time I wrote that comment, my statement was true, but Salesforce.com has since improved the ability to do relationship queries across polymorphic fields. – Estragon

and you think it's ok to jump through all these hoops just to get Contact's email ? – Grubby

Answer 3

Ask Salesforce Support to enable "Polymorphic SOQL" in your organization, then you'll be able to determine whether it's Lead or Contact. It's in Developer Preview at the moment but it's pretty neat. Here's sample usage:

http://blogs.developerforce.com/tech-pubs/2012/09/soql-polymorphism-or-how-i-learned-to-love-the-polymorphic-relationship.html

Or at least leave a "TODO: rewrite this" comment in your code ;)

Eschalot

Answer 4

As mentioned before, the WhoId and WhatId fields on Task are Polymorphic (meaning that they can point to multiple target objects. Unless you have the SOQL Polymorphism feature enabled, as @eyescream describes, the only "related" fields that you can query for are those that are on the 'Name' API object, but there are exceptions, which are documented on this document.

Example 1: select Who.FirstName, Who.LastName, Who.Email from Task

This will return data for FirstName and LastName if the Contact/Lead has those fields populated, but, as the above document says, it will never return results for Who.Email, because neither of the two possible target objects of the Who field (Contact,Lead) is the User object.

Example 2: select Owner.Name, Owner.Email from Case

If you are using Queues with the Case object, then a Case's Owner can be either a User or a Group (of which a Queue is a type) record. Because both Name and Email are on the Name object, the above query will always return data for Name and, if the target object is a User, it WILL return data for Owner.Email.

Example 3: select What.Name, WhatId from Task where What.Type in ('Case','Account','Solution')

This is a very interesting example because the Case object's "Name" field is actually CaseNumber, not Name --- there are a couple other standard objects with "non-standard" (ironic) Name fields, e.g. SolutionTitle, and then there are others that don't even have Name fields, like CampaignMember. HOWEVER, one of the great things about Salesforce having polymorphic fields technically point to the Name object is that the above query WILL return results for Cases! If a Case's CaseNumber is 00001234, then What.Name in the above query will return 00001234, whereas for an Account it will return the Account's Name, and for a Solution it will return SolutionTitle.

It is also helpful to observe here that you CAN limit which target objects are returned by filtering on the 'Type' field, which is a special field on the Name object.

Mellicent

Answer 5

Try this in Console for fetching the value of task/Event

List e1=new List(); e1=[select Id, whoId from event WHERE id= '00U9xxxxxxxxxxx']; System.debug('@@@@@'+e1);

Set whoId=new Set(); for(event e2 : e1) { whoId.add(e2.whoId); } list c=[select name,phone,email from contact where Id IN : whoId]; System.debug('@@@@@'+c);

----------------END-------------

Thanks

Tortuosity