[SalesForce] SObject row was retrieved via SOQL without querying the requested field:

I am getting this error with the code below. I've looked into the other cases on here. In my case I am referring to a relationship name (__r) which I am assuming is pulling correctly. It's a field which is a lookup to User :

Agreement_CSH_to_be_completed_by__r

Here is the code which is giving me problems : 

public void doManagedClients() {
        addMsg('Starting processing of Managed Clients.');
        Integer startSize = CSH_Data.size();

        String SOQL = 'Select Id, OwnerId, Owner.UserName, Account__c, RecordTypeId, Name, Agreement_Client_Status_History__c ' +
            'From Contract_Terms__c Where ' +
            '   ( recordtypeid = \''+ ((String)A_N_Rectype).substring(0,15) + '\' ) and ' + 


            '   Agreement_Client_Status_History__c = \'Monthly\' ' +
            'order by createddate desc nulls first' ;
system.debug('#####333#### ');     

        addMsg(DEBUG,'Managed Query is: ' + SOQL + '\n');
        for (Contract_Terms__c a: Database.Query(SOQL)) {
            if (!a.Agreement_CSH_to_be_completed_by__r.isActive) {
                totalInactiveOwners++;

                addMsg('Account: ' + a.Name +  ', has an inactive owner.  Username: ' + a.Agreement_CSH_to_be_completed_by__r.isActive + '.');
            } else {            
                totalManagedCSHs++;
                addMsg(DEBUG,'Creating a Managed CSH for Account: ' + a);
                CSH_Data.add(new CSH_and_Share_Pair(a, createClientStatusHistory(a)));
                needflush(false);
                if (error) break;
            }
        }
        needflush(true);
        addMsg('End of Managed Clients.');      
    }

Is it just because I don't have a.Agreement_CSH_to_be_completed_by__r included in the SOQL ? I thought I got different errors for that, so I'm a little confused.

Thank you for your help.

Best Answer

You need to add all fields that you want access to to your SOQL query, this includes relationship fields. Even if you query for 

Agreement_CSH_to_be_completed_by__c

You need to add the fields that you want to access in your query such as

Agreement_CSH_to_be_completed_by__r.isActive

If you update your SOQL, this should work fine

EDIT in response to Comment

try this in execute anonymous. This should return an errror

Contract_Terms__c term = [Select Id, OwnerId, Owner.UserName, Account__c, RecordTypeId, Name, Agreement_Client_Status_History__c From Contract_Terms__c limit 1];
system.debug('User is active: ' + term.Agreement_CSH_to_be_completed_by__r.isActive);

If you then add it to your SOQL like below, and re run it, it should work fine

Contract_Terms__c term = [Select Id, OwnerId, Owner.UserName, Account__c, RecordTypeId, Name, Agreement_Client_Status_History__c, Agreement_CSH_to_be_completed_by__c, Agreement_CSH_to_be_completed_by__r.isActive From Contract_Terms__c limit 1];
    system.debug('User is active: ' + term.Agreement_CSH_to_be_completed_by__r.isActive);
Related Topic