function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Kristen LundKristen Lund 

Help Updating SOQL - System.SObjectException Error

Hello developers - -

A little background, we have a Design object that has a Master-Detail field of Group and Lookup field of Contact. We've created a button which allows us to email all contacts associated with a Group and it works great! However, I added another Lookup field on the Design whose email I also want to be included in this string.

I keep getting the System.SObjectException: SObject row was retrieved via SOQL without querying the requested field: Design__c.Contact2__r error, when I try to add the second contact/email field to the for loop or create a second for loop. I didn't create this and don't see the typical SOQL query fashion, so I'm not entirely sure where to add this field in order to get this second email to show up. Any ideas? Thank you in advance for any assistance!

  
global class GroupEmailListExtension {
    
    webService static String getEmailBCCList(String groupId) {
        String emails = '';
        if(String.isBlank(groupId)) return emails;
        Design__c[] design= DesignHelper.FindAllByGroupIDAndStatus
            (groupId, DesignHelper.STATUS_CONFIRMED, true);  
        
        for(Design__c des: design){
            if(des.Contact__r.Email != null && !emails.contains(des.Contact__r.Email)){
                emails += des.Contact__r.Email + '; ';
            }
        } 
        return emails; 
    }
}

//attempted: 
//1:         for(Design__c des: design){
//               if(des.Contact__r.Email != null && !emails.contains(des.Contact__r.Email)){
//                   emails += des.Contact__r.Email + '; ' + des.Contact2__r.Email + ";" ;

//2:         for(Design__c des: design){
//               if((des.Contact__r.Email != null && !emails.contains(des.Contact__r.Email))   
                      || (des.Contact2__r.Email != null && !emails.contains(des.Contact2__r.Email))){
//                   emails += des.Contact__r.Email + '; ' + des.Contact2__r.Email + ";" ;


 
Best Answer chosen by Kristen Lund
Tai SchullerTai Schuller
Ahh yes I see the problem. On line 5 you are prepending a comma, but then line 07 is prepending a comma again. This should work: 
if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            queryString += contactSelection;            
            String contact2Selection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contact2Selection = contact2Selection.replaceALL(',Contact', ',Contact2__r');
		    queryString += contact2Selection; 
        }
       queryString +='   FROM Design__c  '
                   +'    WHERE Status__c = :status  '
                   +'    AND Group__c = :groupId  '
                   +'    ORDER BY Booked_On__c';
       return Database.query(queryString);

Although I don't like that as much because we are calling Helper.getFieldsWithPrefix twice needlessly. Something like this should work but also a bit more efficient:
  
public static List<Design__c> FindAllByGroupIDAndStatus(ID groupId, String status, Boolean includeContact){
        if(groupId==null || status==null) return new List<Design__c>();
        String selection = String.join(Helper.getFields(Schema.SObjectType.Design__c), ',');
        String queryString = 'SELECT ' +selection;
        if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            String contact2Selection = contactSelection.replaceALL(',Contact__r', ',Contact2__r');
            queryString += contactSelection;
            qyeryString += contact2Selection;            
        }
       queryString +='   FROM Design__c  '
                      +'  WHERE Status__c = :status  '
                    +'    AND Group__c = :groupId  '
                    +'   ORDER BY Booked_On__c';
        return Database.query(queryString);
    }

 

All Answers

Alain CabonAlain Cabon
Just test: des.Contact2__r != null   that could be sufficient.
  
global class GroupEmailListExtension {
    
    webService static String getEmailBCCList(String groupId) {
        String emails = '';
        if(String.isBlank(groupId)) return emails;
        Design__c[] design= DesignHelper.FindAllByGroupIDAndStatus
            (groupId, DesignHelper.STATUS_CONFIRMED, true);  
        
        for(Design__c des: design){
            if(des.Contact__r.Email != null && !emails.contains(des.Contact__r.Email)){
                emails += des.Contact__r.Email + '; ';
            }
            if (des.Contact2__r != null && des.Contact2__r.Email != null && !emails.contains(des.Contact2__r.Email)                   {
             emails += des.Contact2__r.Email + '; ';
}

        } 
        return emails; 
    }
}

 
Tai SchullerTai Schuller
Hey Kristen,

Looks like the query you are looking for is in the DesignHelper class, in the FindAllByGroupIDAndStatus method. Within that method is where you can add Contact2__r.Email field.

Hope this helps!
Kristen LundKristen Lund
Thanks @Alain Cabon and @Tal Schuller! Alain - your solution still throws the same SOQL error. Tal - I found the location in my DesignHelper class, but am not able to throw in a second Boolean or combine it with the first contact:
  
public static List<Design__c> FindAllByGroupIDAndStatus(ID groupId, String status, Boolean includeContact, Boolean includeContact2){

I'm beginning to think this may just be above my head!
 
Tai SchullerTai Schuller
I believe we are almost there. Would you be able to post the body of FindAllByGroupIDAndStatus? I expect that we'll have to add the field to a soql query within that method.
Kristen LundKristen Lund
Thanks, @Tal! Sure, I wasn't sture what was all needed, sorry I didn't include the full method to being with. Working as:
  
public static List<Design__c> FindAllByGroupIDAndStatus(ID groupId, String status, Boolean includeContact){
        if(groupId==null || status==null) return new List<Design__c>();
        String selection = String.join(Helper.getFields(Schema.SObjectType.Design__c), ',');
        String queryString = 'SELECT ' +selection;
        if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            queryString += contactSelection;            
        }
       queryString +='   FROM Design__c  '
                      +'  WHERE Status__c = :status  '
                    +'    AND Group__c = :groupId  '
                    +'   ORDER BY Booked_On__c';
        return Database.query(queryString);
    }

 
Tai SchullerTai Schuller
Neat! I like the way this query is constructed. One way to accomplish our goal is to modify the query to bring in all fields for Contact2__r 
public static List<Design__c> FindAllByGroupIDAndStatus(ID groupId, String status, Boolean includeContact){
        if(groupId==null || status==null) return new List<Design__c>();
        String selection = String.join(Helper.getFields(Schema.SObjectType.Design__c), ',');
        String queryString = 'SELECT ' +selection;
        if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            contact2Selection = contactSelection.replaceALL(',Contact', ',Contact2__r');
            queryString += contactSelection;
            qyeryString += (',' + contact2Selection);            
        }
       queryString +='   FROM Design__c  '
                      +'  WHERE Status__c = :status  '
                    +'    AND Group__c = :groupId  '
                    +'   ORDER BY Booked_On__c';
        return Database.query(queryString);
    }

This should allow you to reference the Contact2__r.Email field in your getEmailBCCList method.
Kristen LundKristen Lund
I can't take credit for the query - but thank you! I tried as you've written and reiceved the error "Variable does not exist: contact2Selection". So I tried to duplicate the String as listed in line 6, but with Contact2 instead, which also didn't work: 
if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            queryString += contactSelection;            
            String contact2Selection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contact2Selection = contact2Selection.replaceALL(',Contact', ',Contact2__r');
		    queryString += (',' + contact2Selection); 
        }
       queryString +='   FROM Design__c  '
                   +'    WHERE Status__c = :status  '
                   +'    AND Group__c = :groupId  '
                   +'    ORDER BY Booked_On__c';
       return Database.query(queryString);
I get a {faultcode:'soapenv:Client', faultstring:'System.QueryExecption: unepected token: ',' at the return line.

I realize these are usually extra spaces, but I can't seem to find any of those either. I really apprecaite the help on this!
 
Tai SchullerTai Schuller
Ahh yes I see the problem. On line 5 you are prepending a comma, but then line 07 is prepending a comma again. This should work: 
if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            queryString += contactSelection;            
            String contact2Selection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contact2Selection = contact2Selection.replaceALL(',Contact', ',Contact2__r');
		    queryString += contact2Selection; 
        }
       queryString +='   FROM Design__c  '
                   +'    WHERE Status__c = :status  '
                   +'    AND Group__c = :groupId  '
                   +'    ORDER BY Booked_On__c';
       return Database.query(queryString);

Although I don't like that as much because we are calling Helper.getFieldsWithPrefix twice needlessly. Something like this should work but also a bit more efficient:
  
public static List<Design__c> FindAllByGroupIDAndStatus(ID groupId, String status, Boolean includeContact){
        if(groupId==null || status==null) return new List<Design__c>();
        String selection = String.join(Helper.getFields(Schema.SObjectType.Design__c), ',');
        String queryString = 'SELECT ' +selection;
        if(includeContact){
            String contactSelection = ',' + String.join(Helper.getFieldsWithPrefix(Schema.SObjectType.Contact, true), ',');
            contactSelection = contactSelection.replaceALL(',Contact', ',Contact__r');
            String contact2Selection = contactSelection.replaceALL(',Contact__r', ',Contact2__r');
            queryString += contactSelection;
            qyeryString += contact2Selection;            
        }
       queryString +='   FROM Design__c  '
                      +'  WHERE Status__c = :status  '
                    +'    AND Group__c = :groupId  '
                    +'   ORDER BY Booked_On__c';
        return Database.query(queryString);
    }

 
This was selected as the best answer
Kristen LundKristen Lund
Amazing! It works - woohoo! I did end up doing the second example as I too didn't like calling the Helper twice. You have been a huge help today, Tal! This would have taken a lot of trial and error on my own. Thank you so much!