[SalesForce] Querying custom field on User using createdBy

I want to extract a custom field value in a soql using createdby reference.

List<CaseComment> cc = [Select createdby.customField__c from caseComment];

The soql is failing to recognize the customField.
Is this not allowed?

Best Answer

The audit fields that refer to the user object are of the Name data type; this data type represents a few standard fields you can get data from, because these fields can actually refer to things other than just users. For example, CaseComment records can be created by a Contact record (by virtue of the Self Service Portal or Community portals).

What this means is that you can only access a handful of certain standard fields designed to work with the Name data type. If you want custom data, you'll need to use TYPEOF, which is still a developer preview feature, or you'll need a separate query, in the usual fashion:

Map<Id, User> users = new Map<Id, User>();
CaseComment[] comments = [SELECT CreatedById FROM CaseComment];
for(CaseComment record: comments) {
  users.put(record.CreatedById, null);
}
users.putAll([SELECT CustomField__c FROM User WHERE Id = :users.keySet()]);
User commentUser;
for(CaseComment record: comments) {
  if((commentUser = users.get(record.CreatedById)) != null) {
    // Do something with commentUser
  }
}

Related Solutions

[SalesForce] SObjectException “SObject row was retrieved via SOQL without querying the requested field” not being thrown

I cannot seem to reproduce what you've described, I get the sObject not retrieved error for Case 1 too!

The only caveat would be that since sObjects are passed by reference, hopefully there isn't a sneaky bit of assignment which is setting the value of CustomField1 somewhere in the foodchain.

If there is an assignment of value then it wouldn't throw the exception for any subsequent access. (Even though it wasn't initially retrieved as part of the soql)

As a simplified example, this will throw the SObjectException (assuming you have at least one Opportunity):

Opportunity opp = [select Id, StageName From Opportunity limit 1];
System.debug(opp.Probability);

And this will not (opp.Probablity will be null):

Opportunity opp = [select Id, StageName From Opportunity limit 1];
opp.StageName = 'foo';
System.debug(opp.Probability);

[SalesForce] Querying against a multi-select picklist

If you are querying against a mulit-select picklist, you need to use includes in your query.

list subscriptionCampaigns = [
    SELECT ID FROM Campaign 
    WHERE Newsetter_Subscriptions__c INCLUDES :contactSubscriptions];

If Newsletter_Subscriptions__c is the multi-select and contactSubscriptions contains all of the values you want this should return any Campaign that has at least one selected. If you need to only select those that match all then you need to join the contactSubscriptions into a single semi-colon delimited string and have that as the only item on the list

There are some more examples in the Querying Multi-Select Picklist docs

Best Answer

Related Solutions

[SalesForce] SObjectException “SObject row was retrieved via SOQL without querying the requested field” not being thrown

[SalesForce] Querying against a multi-select picklist

Related Topic