[SalesForce] Retrieving Dynamic SOQL results. Runtime error – “Invalid field Owner.Name for…”

I had a question in regards to dynamically retrieving the results of Dynamic SOQL that I have set up in my controller. I want to be able to access fields on the Lead object (Lead.Name) itself as well as relationships (Lead.Owner.Name).

The dynamic soql works great, but I'm having problems when I try to read in the values of the result. Is there any way I can pull in values for fields on the sObject itself as well as values from a relationship? Here's a snippet of the problematic code:

Lead lead = [SELECT Id, OwnerID, Owner.Name, Name from Lead where Id = '00QP0000003r4f8']; //*Note in the actual code I'm using a String and Database.query()
String apiname = 'owner.name';
String apiname2 = 'Name';

//this works, but we don't want to hardcode the value
system.debug('lead owner.name ='+lead.owner.name);

String result1 = String.ValueOf(lead.get(apiname2));
System.Debug(result1);
//returns name of the lead record just fine

String result2 = String.ValueOf(lead.get(apiname));
//ERROR on runtime - System.SObjectException: Invalid field owner.name for Lead

System.Debug(result2);

Best Answer

In addition to Doug B's answer, I wrote a utility method to do this:

public static Object getSobjectField(Sobject sObj, String fieldRef) {

        Sobject sObjNode    = sObj;
        //  fieldRef is relative to the sobject and may include relationshipNames
        //      Handle use cases:
        //          fld0                - fld0 on Sobject passed as argument
        //          fld0.fld1           - fld1 is on sobject fld0
        //          fld0.fld1. ... fldN - fldN is on sobject fldN-1
        String[] fieldList  = fieldRef.split('\\.');

        for (Integer i=0; i < fieldList.size() -1; i++) {
            sObjNode    = (Sobject) sObjNode.getSobject(fieldList[i]);  // intermediate fields are sobjects
            if (sObjNode == null) return null;
        }
        return sObjNode.get(fieldList[fieldList.size()-1]); // use fldN
    }

Example: String ownerName = (String) Util.getSobjectField(lead,'owner.name');

Related Solutions

[SalesForce] Retrieving value using dynamic SOQL

Since the other question relates technically to updating and not retrieving I'll give an answer here as well, as well as a security nod. Though in general its pretty much the same principle and area of Apex you need to understand. That being Dynamic Apex and also the methods described on the SObject class, specifically in your case the 'get' methods. In short the following will give you what you want.

opportunity.get(mycustomatt);

If your custom attribute was of type String, you would cast it as follows...

String mycustomattValue = (String) opportunity.get(mycustomatt);

NOTE: You also need to keep in mind SOQL Injection considerations, as described here. Even if your code is providing the additional SOQL query information (as apposed to some end user prompt or config), its usually good practice to follow the recommendations here, just in case the situation changes in your code in the future.

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

Absolutely there is. Use the following syntax:

// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';

// then use database.query() method.  
// Note its return value is a list<sObject> though you can cast it appropriately

list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);

You can also reference instance variables, but not any variable of an instance

set<id> myIdSet;

string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);

// XXXXXXXX   The following WONT work    X_X

map<id,Account> accountMap = new map<id,Account>(accountList);

string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()';  // X_X WILL NOT WORK 

list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());

And since you mentioned there being no equivalant to SELECT * FROM ... in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.

    //=================   QUERY UTIL METHODS   =================================

//#################   Format Fields For Query   ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)

public static string FormatFieldsForQuery(sObject M){
    return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)

public static string FormatFieldsForQuery(string objectName){
    return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)

public static string FormatFieldsForQuery(string objectName,string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
    if(objectType==null)
        return fields;
    for(string f :objectType.getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');  
}//END FormatFieldsForQuery(string objectName,string prefix)

public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){ 
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : m.keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)

public static string AllFieldsQuery(string objectName){
    return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery

public static string AllFieldsQuery(sObject s){
    return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery

Test Code:

//#################   Format Fields For Query   ###########################################
@isTest private static void testFormatFieldsForQuery(){
    // Test order (depending upon input:
    //
    // #1 (sObject M, string prefix)
    // #2 (sObject M)
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    // #5 (map<String,Schema.SObjectField> m,string prefix)
    // #6 AllFieldsQuery(string objectName)   <---- different method name ----
    ///////////////////////////////////////////////////////////////////////////
    test.startTest();
    //test Account Standard Fields
    Account a=new Account();
    assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
    assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);

    //test Contact Standard Fields
    Contact c=new contact();
    assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
    assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);

    // bad objectName for 
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));

}//END testFormatFieldsForQuery2

public enum fffqTestObj {Account,Contact}

public static void assertFFFQ(string fieldString,fffqTestObj objName){
    set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));

    if(objName==fffqTestObj.Account){
        system.assert(fields.contains('name'));
        system.assert(fields.contains('accountnumber'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('site'));
        system.assert(fields.contains('accountsource'));
        system.assert(fields.contains('annualrevenue'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('numberofemployees'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('industry'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('ownership'));
        system.assert(fields.contains('parentid'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('rating'));
        system.assert(fields.contains('sic'));
        system.assert(fields.contains('sicdesc'));
        system.assert(fields.contains('tickersymbol'));
        system.assert(fields.contains('type'));
        system.assert(fields.contains('website'));
    }
    else if(objName==fffqTestObj.Contact){
        system.assert(fields.contains('accountid'));
        system.assert(fields.contains('assistantname'));
        system.assert(fields.contains('assistantphone'));
        system.assert(fields.contains('birthdate'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('department'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('donotcall'));
        system.assert(fields.contains('email'));
        system.assert(fields.contains('hasoptedoutofemail'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('hasoptedoutoffax'));
        system.assert(fields.contains('homephone'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('lastcurequestdate'));
        system.assert(fields.contains('lastcuupdatedate'));
        system.assert(fields.contains('leadsource'));
        system.assert(fields.contains('mobilephone'));
        system.assert(fields.contains('otherphone'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('reportstoid'));
        system.assert(fields.contains('title'));  
    }
}//END assertFFFQ

Best Answer

Related Solutions

[SalesForce] Retrieving value using dynamic SOQL

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

Related Topic