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
Travis16Travis16 

SOQL Child Record List

I am writing a trigger that will Query Child records of a custom object and return 1 value. As there should only be 1 child record we need to pull back the email address of the assigned Drafter. Once we have the Drafters email we will use that in process builder to send out emails when a change is made if it affects the Department. I think there is something wrong with my SOQL as I am getting an error around the expected ; and got o.id. I am not sure what I did wrong. a fresh set of Eyes would help. Thanks 

Trigger (Not Complete)
trigger updateDrafterEmail on Order__c (before insert) {
    String drafterEmail;
    for (Order__c o : Trigger.new){
        //Get all the Drafting records in a list where drafting is on the order
        List<Drafting> myDrafting = new List<Drafting> {
            myDrafting = [SELECT Id, 
                                 SAP_Order__c,
                                 Drafting_Kingspan__c,
                                 Drafting_Email__c, 
                            WHERE Id
                            In 
                            (SELECT Drafter__c, 
                                     Drafter_Email__c 
                             FROM    Drafting__r 
                             WHERE   Drafter__c != null ) 
                             Limit 1;
        }
        //check if list is empty if not we will update the order with the Drafters email
        if(myDrafting > 0 && o.Drafter_Email__c != null){


        }
    }
}
 
Best Answer chosen by Travis16
Prateek Singh SengarPrateek Singh Sengar
Hi Travis,
The syntax for child query is  
SELECT Name,
  (
    SELECT LastName
    FROM Contacts
  )
FROM Account
In the above query Account is the parent obejct and contacts are child object.

In your query you do not have From portion for your parent object 
myDrafting = [SELECT Id, 
                                   SAP_Order__c,
                                   Drafting_Kingspan__c,
                                   Drafting_Email__c, 
//FROM Clause Missing
                                   WHERE Id
//Id shoudl be compared with SFDC id of record. Since you are using __r in 2nd query i believe you are trying to get child records
                                   In 
                                   (SELECT Drafter__c, 
                                                  Drafter_Email__c 
                                    FROM    Drafting__r 
                                    WHERE   Drafter__c != null ) 
                                   Limit 1;

Hope this helps.

All Answers

Prateek Singh SengarPrateek Singh Sengar
Hi Travis,
The syntax for child query is  
SELECT Name,
  (
    SELECT LastName
    FROM Contacts
  )
FROM Account
In the above query Account is the parent obejct and contacts are child object.

In your query you do not have From portion for your parent object 
myDrafting = [SELECT Id, 
                                   SAP_Order__c,
                                   Drafting_Kingspan__c,
                                   Drafting_Email__c, 
//FROM Clause Missing
                                   WHERE Id
//Id shoudl be compared with SFDC id of record. Since you are using __r in 2nd query i believe you are trying to get child records
                                   In 
                                   (SELECT Drafter__c, 
                                                  Drafter_Email__c 
                                    FROM    Drafting__r 
                                    WHERE   Drafter__c != null ) 
                                   Limit 1;

Hope this helps.
This was selected as the best answer
Travis16Travis16
Thansk Prateek I have been trying to get the query to work so that is why the FROM statement is missing. If I understand you answer I should use something like below. 

myDrafting = [SELECT Id
                     FROM = Order__r
                     WHERE Id 
                      IN  (SELECT Drafter__c, Drafter_Email__c, Order__c
                             FROM Drafting__r
                             WHERE Drafting__c != null)
                             LIMIT 1;]
Prateek Singh SengarPrateek Singh Sengar
Hi Travis,
If Order is you parent object and drafting is your child object then your query should be
  
[SELECT Id, (SELECT Drafter__c, Drafter_Email__c, Order__c
                      FROM    Drafting__r
                      limit 1)
  FROM    Order__c WHERE Id IN =: IDVARIABLE]

Please note that in your queyr ID IN (SUB QUERY) wont work because ID is expecting reference of Order__c object and your subquery is returning Drafting__c object.

 
Travis16Travis16
Sorry for the confusion but in the Child Query Order__c is the Id of the Parent Record that I need to compare to so Can I state the following 

FROM Order__c WHERE Id IN =: Order__c
 
Travis16Travis16
Thanks you I believe that I figured it out with the Correct Syntax. 

myDrafting = [SELECT Id,
                          (SELECT Drafter_Email__c,Drafter__c, Order__c
                          FROM Drafting__r 
                          LIMIT 1)
                          FROM Order__c WHERE Id IN : Order__c]
Travis16Travis16
PARENT OBJECT - Order__c (Custom Order Object)
          Id is the only field on need
CHILD OBJECT - Drafting__c (Custom Object with Email address)
          Drafter_Email__c and Order__c (Which is my Order__c Id)

For my Order within my FOR loop I need to pull back 1 Drafting Record to get an email address for the Drafter that is assigned to this record. With that email address I need to update my Parent Obejct Order__c with the Email address. 

I hope this helps answer your question.