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
amyhamyh 

Table Lookup with Variable within Trigger

Hello,

I'm very close to having my trigger fully functional, but I need to do a simple table
lookup for the record I am inserting. Could you please help me with the syntax.

Working Hardcoded:

ID myID = null;
        
    for (SFDC_520_QuoteLine__c obj : Trigger.new) {
        myID = obj.Product2__c ;
        Product2 p = [(select name ) from Product2 where ProductCode = 'My Product Code']; //:myid


Non Working with Variable

ID myID = null;
        
    for (SFDC_520_QuoteLine__c obj : Trigger.new) {
        myID = obj.Product2__c ;
        Product2 p = [(select name ) from Product2 where ProductCode = :myID];

I don't really get an error message except that my Test now fails when it attempts the insert.

Thank You/

Full Code:

trigger CreateCaseAfterShippingConfirmsOrder on SFDC_520_QuoteLine__c
(after insert, after update)

{ 
    ID myID = null;
        
    for (SFDC_520_QuoteLine__c obj : Trigger.new) {
        myID = obj.Product2__c ;
        Product2 p = [select name  from Product2 where ProductCode = :myID]; //:myid
       
        if (obj.Shipping_Confirms_Order__c == True) {
              Case newCase = new Case(
             
                Subject = 'New Data Production Project',
                Description = p.name + ' ' + obj.Description__c,
                Status = 'Not Started',
                Product__c = 'Custom Data',
                Priority = 'Sev-4 (when resources allow)',
                Origin = 'Web'
                );
                insert newCase;
        }
    }          
}
Drew1815Drew1815
I am not entirely sure what the error message is in your test method, but I do some opportunities to streamline your code and make it more scalable. I don't guarantee this code compiles since I don't have your custom object defined in my Developer Edition org, but please see some of the code below. I commented some of the changes that I think will help your solution.

Code:
trigger CreateCaseAfterShippingConfirmsOrder on SFDC_520_QuoteLine__c(after insert, after update){ 
    //ID myID = null;
    
    
    List<Id> myIDs = new List<Id>{}; //use this to store all the Product2 Ids to query for
        
    for (SFDC_520_QuoteLine__c obj : Trigger.new) {
        myID = obj.Product2__c ;
    }
    
    
    //Need to move the SOQL Query out of the FOR LOOP otherwise you'll hit a governor limit in some scenarios
    Map<Id,Product2> productMap = new Map<Id,Product2>([select name  from Product2 where ProductCode = :myIDs]); //:myid
    
    
      
    //Now loop through records again
    for (SFDC_520_QuoteLine__c obj : Trigger.new) {
       
     if (obj.Shipping_Confirms_Order__c == True) {
               Case newCase = new Case(
              
                 Subject = 'New Data Production Project',
                 
                 //Use the Map to get the Product2 Name out of the Map generated by the SOQL query above
                 Description = productMap.get(obj.Product2__c).Name + ' ' + obj.Description__c,
                 Status = 'Not Started',
                 Product__c = 'Custom Data',
                 Priority = 'Sev-4 (when resources allow)',
                 Origin = 'Web'
                 );
                 insert newCase;
         }
     } 
 }          
}

 
amyhamyh
Drew,

Thank you so much for your time! The code looks much better, and scalable too (this will help me with
future triggers for sure).

I am getting an error on this line:

 for (SFDC_520_QuoteLine__c obj : Trigger.new)
        {
            myIDs = obj.Product2__c ;
        }

Error: Illegal assignment from ID to LIST:Id

In my custom object which is a Sales Order Line Item, I'm trying to use the field Product2__c which is a Data Type: Lookup
I was assuming that this was the unique id link to the product from the product table, but maybe I don't understand
the data types well enough.

Any thoughts?

thanks!
Drew1815Drew1815
Yeah, sorry, that was a weak typo in my sample code back to you.
You need to "add" each Product2 id to the list object.

 for (SFDC_520_QuoteLine__c obj : Trigger.new)
        {
            myIDs.add(obj.Product2__c);
        }


Thanks,
Drew
amyhamyh
Thanks Drew,

If I could just ask you one more follow up.

This line:
Map<Id,Product2> productMap = new Map<Id,Product2>([select name  from Product2 where ProductCode = :myIDs]);

throws the error:
Save error: Invalid bind expression type of LIST:Id for column of type String

where this does work:
Map<Id,Product2> productMap = new Map<Id,Product2>([select name  from Product2 where ProductCode = 'MyTestProduct']);

Would I do some kind of convert?


thanks!!
Drew1815Drew1815
Sorry, another typo. I needed to use the IN clause in the Where statement (not the = clause). This compiled for me:

Map<Id,Product2> productMap = new Map<Id,Product2>([select name  from Product2 where ProductCode IN :myIDs]);
amyhamyh
Thank you very much Drew.

Now I'm on to trying to write a test script for this.

Thanks for your help!!