+ Start a Discussion
Glenn DalyGlenn Daly 

Reference a child relationship record in SOQL query

There is a requirement to have a new record created on the Product2 object when the current product2 record meets a certain criteria. These records should then be linked together.
I have used process builder to create a new product2 record in the product2 object when the current product2 record meets a certain criteria. On the new product2 record I have a lookup field that is populated with the historic product2 record Id.
If I want to reference the new product2 record, from the historic product2 record, how would I do this? The lookup field is called Historic_R&D_Record__c and has a child relationship name of "Services".
I need to reference the historic product record in the soql query below.

List<Opportunity> listOpportunity = new List<Opportunity>();
            listOpportunity = [SELECT Id, OwnerId, Name, Owner.FirstName, (SELECT Id, Product2.Name FROM OpportunityLineItems WHERE Product2.Opportunity_Owner_Notification__c = FALSE
                            AND Product2.Isactive = FALSE
                            AND (Product2.RecordType.DeveloperName = 'Unavailable_R_D_Service' OR Product2.RecordType.DeveloperName = 'Unavailable_R_D_Project' ) LIMIT 1)
                           FROM Opportunity WHERE Id In
                           (SELECT OpportunityId FROM OpportunityLineItem WHERE Product2.Opportunity_Owner_Notification__c = FALSE
                            AND Product2.Isactive = FALSE
                            AND (Product2.RecordType.DeveloperName = 'Unavailable_R_D_Service' OR Product2.RecordType.DeveloperName = 'Unavailable_R_D_Project' ))];
Best Answer chosen by Glenn Daly
Zaid DarbarZaid Darbar
I will like to do it via trigger because you are making so many sub queries that it is difficult to do via process builder.

trigger ProdTrg on Prod2 (After insert, After Update) {
    for (Product2 P : trigger.new)
    {
       list <Product2> lstProd = new list <Product2> ();
       if() //put your criteria here like P.fieldname == some value && P.fieldname == someother value and so on
       {
         // if the criteria to create a new product is meet
         Product2 NewProd = new Product2 ();
         NewProd.Name = 'Your Product Name here';
         NewProd.ProductCode = 'Your Product Code here';
         NewProd.Family = 'Your Product Family here';
         NewProd.Historic_R&D_Record__c = P.id;
         lstProd.add(NewProd);
        }
    }
  insert lstProd;
}

Please let me know if this resolved your issue or you have any other confussion.