How to Retrieve TYPEOF Fields From a SOQL Query

Talk about pulling your hair out. I took a deep dive into Salesforce polymorphic fields recently and just when I thought I knew how to retrieve their underlying fields in a SOQL query using a TYPEOF clause — boom! I didn’t. Which means I couldn’t, no matter what I tried. All I wanted to do was see the fields the TYPEOF clause was supposed to return in the query editor. Was that too much to ask?

Yes, apparently it was.

For those who don’t know, a polymorphic field is a reference (lookup) field that can represent one of several possible objects. One of the best examples of this is the WhatId field on the Task object. Here, WhatId can represent either an opportunity or account (non-person-like objects). Likewise, the WhoId (another polymorphic reference field on Task) represents a person-like object, often a lead or contact.

The following is an example of a SOQL query using the TYPEOF clause on a task object. Its job is to query the WhatId object type and return the indicated object-specific fields:

SELECT Id, Subject, Priority, Status, CreatedDate,
    TYPEOF What
      WHEN Opportunity THEN Name, Amount
      WHEN Account THEN Name, Rating
    END
FROM Task

For those who have worked through my SOQL 101 for Admins series, the above query should look very familiar except for the TYPEOF clause.

And as you may have noticed (but don’t worry if you didn’t), the TYPEOF clause acts not unlike a Switch statement in Apex or a Case statement in a Salesforce formula — both of which have the mechanics of an IF statement working behind them. (Right?)

Look carefully enough, and you’ll also notice that the TYPEOF clause falls within the SELECT clause. For this reason, it’s treated just like another field — and depending on where you’ve placed it, you will need to add any preceding or trailing commas. Here, it’s preceded by a comma.

So why do we even have to use TYPEOF? Why don’t we just use dot notation to pull in those parent fields instead?

Great question!

I mean, couldn’t we just write a query like this?

SELECT Id, Subject, Priority, Status, CreatedDate, WhatId, What.Name, What.Amount, What.StageName
  FROM Task
 WHERE What.Type = 'Opportunity'

You could try, but you’d get an error. Specifically, you would get one that warned: No such column ‘Amount’ on entity ‘Name’. (Name here appears to be referring to the polymorphic field. If I later learn why it’s referred to as ‘Name’ and not ‘Type’ or ‘What’, I’ll come back and let you know.) So, continuing on, if you removed the What.Amount field in response to the warning, the next error message would balk at your StageName field.

Why? Because a polymorphic field (WhatId in this example) is severely restricted as to which fields it can access using dot notation. In fact, it appears you only have access to the following: Id, Name, and Type. And this kinda makes sense, right? Every object has an Id and object type. And nearly all have a name field. So, as a polymorphic field that hasn’t committed to the object it represents, querying the Id, name, and type is going to work for most objects, especially the ones we’re dealing with in these situations (eg, Account, Opportunity, etc.).

So, with that, this is the best you would be able to do when it comes to pulling in relevant fields using dot notation:

SELECT Id, Subject, Priority, Status, CreatedDate, WhatId, What.Name, What.Type
  FROM Task
 WHERE What.Type = 'Opportunity'

The good news? Those are pretty important fields and they’re available!

The bad news? You’ll need to run a separate query to pull in the other fields. Unless, of course, you use the TYPEOF clause.

But TYPEOF only gets us halfway there. I mean, it grabs the fields, but then (drumroll please)… it doesn’t display them in a query editor. And that, my friends, was what I was going on about in the first paragraph. I wanted to see those fields in my query results.

But to see them, you need Apex and the instanceof keyword. And here’s how you might go about doing that in the Execute Anonymous window of the dev console:

List<Task> tasks = [SELECT Id, Subject, Priority, Status, CreatedDate,
                        TYPEOF What
                            WHEN Opportunity THEN Name, Amount
                            WHEN Account THEN Name, Rating
                        END
                    FROM Task];

System.debug('Number of Tasks found: ' + tasks.size());

for (Task tsk : tasks){
    System.debug('');
    System.debug('Task Id: '  + tsk.Id);
    System.debug('Subject: '  + tsk.subject);
    System.debug('Priority: ' + tsk.Priority);
    System.debug('Status: '   + tsk.Status);
    if(tsk.What instanceof Opportunity){
        Opportunity opp = tsk.What;
        System.debug('Opportunity Name: '   + opp.Name);
        System.debug('Opportunity Amount: ' + opp.Amount);
    }else if (tsk.What instanceof Account){
        Account acct = tsk.What;
        System.debug('Account Name: '   + acct.Name);
        System.debug('Account Rating: ' + acct.Rating);
    }
}

After assigning the SOQL query to a list, we loop through each task. After using debug statements to print task details, we use an if statement and the instanceof keyword to determine if What represents an opportunity. If so, assign tsk.What to an opportunity variable. At this point, you can use that variable to access and print the opportunity fields the TYPEOF clause returned. We do the same for accounts.

So, there you have it. You can see the object-specific TYPEOF fields returned by the query but you have to use Apex to do it. Boo, hiss!

(Did I get something wrong above? Set me straight by leaving a comment below. Also, do you know why they refer to the polymorphic field as ‘Name’ in the error message described above? If so, let us know in the comments. Thanks!)

Leave a Comment Cancel Reply