Unable to query FederationIdentifier on case owner

Question

[select FederationIdentifier from user limit 15]

The above query works perfectly fine on user object workbench and developer console but when I write below query to pull list of cases along with user federation Id, I am getting "Error: No such column 'FederationIdentifier' on entity Name"

List<case> c = [select Id, ownerId, owner.name, owner.FederationIdentifier from case limit 15]

Can anyone suggest me where I am going wrong.

Referred Question # 47467 here which says owner.FederationIdentifier fails. So, is there any workaround for this ?

Thanks

Answer 1

This is the result of limitations when working with polymorphic fields (Case.Owner, Event.Owner, Etc.).

Basically, salesforce doesn't have enough context in SOQL to know which object to pull from since you might have a Case assigned to a Queue or a User.

My only known workaround is to create a custom formula field on Case called Owner.FederationId__c and reference this in your SOQL.

Use this as your formula (note the additional notation of User which provides object context for salesforce):

Owner:User.FederationIdentifier

I am aware you can request a feature to allow polymoprhism in your SOQL Queries. Here's some documentation on it:

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

Answer 2

Salesforce now has TYPEOF clause which can be used to query data that contains polymorphic relationships. In order to query FederationIdentifier on case owner, we can use the below query:

SELECT Id, TYPEOF Owner WHEN User Then FederationIdentifier END FROM Case WHERE Owner.Type = 'User'

Here, we're essentially saying that When the Case Owner's type is User, then query FederationIdentifier field. We also added a WHERE condition to filter cases where the case owner's type is User.

In my org, I have a case as shown below:

The above case owner (User User) has a federation identifier equal to 12345 as you can see below:

So, if I run the above SOQL query, I get the case record with owner's federationid as follows:

You can learn more about the TYPEOF clause in my detailed blog post here

Hope that helps!