Query Tasks, Notes, Attachments by Object Type using SOQL

If you've worked with Salesforce for a while, you've probably come across the gotchas of some of the "standard" sObjects like Task, Event, Note, Attachment and some others. What these all have in common is that rather than having a standard lookup to a single object type, their parent can be any object type. For example, the CampaignMember object can be related to either a Lead or a Contact and has two distinct fields for those links; LeadId and ContactId. If you created a lookup or Master Detail on a custom object, you're asked to select a single object type for the link. In contrast, a Task can also be related to either a Lead or a Contact but has only one field to handle both relationships; WhoId.  

Recently, I needed to run a query in apex where I wanted a list of all the notes related to opportunities. Initially, I tried the following;

//the below does NOT work, LIKE cannot be used with Id fields
SELECT Id, ParentId FROM Note WHERE ParentId like '%006' 

This took me a while to figure out until I delved deeper into the API documentation and found the section Understanding Polymorphic Keys and Relationships . (Up until that point I had incorrectly been calling them dynamic id fields. And I'll probably continue doing so because Polymorphic does not exactly roll off the tongue.) In any case, the below is an example of how I was able to correctly restructure my query.

//returns a list of note records where the parent is a specific type, in this case Opportunity
SELECT Id, Parent.Id, Parent.Type FROM Note WHERE Parent.Type='Opportunity'

The Note object uses ParentId as does Attachment. For other types of objects, like Task and Event, you have the fields WhatId and WhoId. WhoId can relate to Leads and Contacts, while the WhatId is any object that supports activities. Those queries would be structured like;

//returns a list of tasks related to Leads
SELECT Id, Who.Id, Who.Type FROM Task WHERE Who.Type='Lead'

//returns a list of events related to Contacts AND Opportunities
SELECT Id, Who.Id, Who.Type FROM Event WHERE Who.Type='Contact' and What.Type='Opportunity'

One important thing to note (and something I think is unclear in the documentation), is that for the parent you are limited to the fields listed on the Name object. You can't try referencing other standard and custom fields that may be on the parent object. For example;

//This will FAIL even though CreatedDate is a standard field on the opportunity object
SELECT Id, Parent.Id, Parent.Type FROM Note WHERE Parent.Type='Opportunity' AND Parent.CreatedDate = Today

//This will work even though Title isn't even a valid field on the opportunity object
SELECT Id, Parent.Id, Parent.Type FROM Note WHERE Parent.Type='Opportunity' AND Parent.Title != 'Test' 

So, if you did need to filter by values specific to the parent object, you'll need to retructure your query to do a sub-query.  

//restructure your query
SELECT Id, Parent.Id, Parent.Type FROM Note WHERE Parent.Type='Opportunity' AND ParentID in (SELECT Id FROM Opportunity where CreatedDate = Today)

Hopefully, this saves you the 2 hours I spent Googling before breaking down and RTFM. Enjoy.

Advertisements
This entry was posted in Salesforce, Web/Tech and tagged , . Bookmark the permalink.

3 Responses to Query Tasks, Notes, Attachments by Object Type using SOQL

  1. Eric says:

    HUGE help to me. That could have taken hours to figure out and It’s already late. Thanks so much for this post!

    Like

  2. Dan says:

    I didn’t know this.
    well done

    Like

  3. Erica says:

    Very helpful and easy to understand. Any ideas on how I could order notes by the createddate on the parent record?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s