+ Start a Discussion
Alex Waddell 17Alex Waddell 17 

Adding a where clause breaks my Trigger. HELP!

Hello,

I am writing a trigger to update Correspondence records that exist within a certain Pay Period and Year. This information is collected in the Batch record that fires my trigger

The problem I am having is that my Set <Correspondence> SOQL query throws 20+ errors whenever I add a where clause

User-added image
trigger ACH_Email_Update_Trigger_Batch on Batch__c (before update) {
    for(Batch__c b : trigger.New){
        String PayPeriod = b.Pay_Period__c;
        String Year = b.Year__c;
    }
    
    Set <Correspondence__c> MyCorr = New  Set <Correspondence__c> 
        ([Select id, year__c, Pay_Period__c From Correspondence__c Where Pay_Period__c = year]);
      
}
Any ideas?
 
Best Answer chosen by Alex Waddell 17
Raj VakatiRaj Vakati
Use this
trigger ACH_Email_Update_Trigger_Batch on Batch__c (before update) {
	
	Set<String>  PayPeriod = new Set<String>() ; 
	Set<String>  Year = new Set<String>() 
    for(Batch__c b : trigger.New){
		PayPeriod.add(b.Pay_Period__c) ; 
        Year.add(b.Year__c);
    }
    
    Map <Id ,Correspondence__c> MyCorr = New  Map <Id , Correspondence__c> 
        ([Select id, year__c, Pay_Period__c From Correspondence__c Where Pay_Period__c IN :year]);
      
}

 

All Answers

Raj VakatiRaj Vakati
Use this
trigger ACH_Email_Update_Trigger_Batch on Batch__c (before update) {
	
	Set<String>  PayPeriod = new Set<String>() ; 
	Set<String>  Year = new Set<String>() 
    for(Batch__c b : trigger.New){
		PayPeriod.add(b.Pay_Period__c) ; 
        Year.add(b.Year__c);
    }
    
    Map <Id ,Correspondence__c> MyCorr = New  Map <Id , Correspondence__c> 
        ([Select id, year__c, Pay_Period__c From Correspondence__c Where Pay_Period__c IN :year]);
      
}

 
This was selected as the best answer
Bryan Leaman 6Bryan Leaman 6
I believe there are 2 issues: First, you're defining year within the for-loop and accessing it outside the for-loop. Second, you need to put a colon in front of a host variable inside a SOQL statement.

Also, if the Pay_Period__c and Year__c are always identical for the batch, then you do not need to loop over them.

Try:
trigger ACH_Email_Update_Trigger_Batch on Batch__c (before udpate) { 
    String PayPeriod; 
    String Year; 

    if (Trigger.new.size()>0) { 
        PayPeriod = Trigger.new[0].Pay_Period__c; 
        Year = Trigger.new[0].Year__c; 
    } 

    Set<Correspondence__c> MyCorr = new Set<Correspondence__c>([ 
        select Id, Year__c, Pay_Period__c 
        from Correspondence__c 
        where Pay_Period__c=:PayPeriod and Year__c=:year 
    ]); 

}

 
Bryan Leaman 6Bryan Leaman 6
Raj is correct if there can be multiple pay periods and years in a single batch, except that I suspect your sample code was cut off and that you meant to query on both pay_period__c and year__c in the soql where clause.
Alex Waddell 17Alex Waddell 17
There can only be one pay period in each instance

Would your original code be the correct one to go with then? or will Raj's code work regardless?
Raj VakatiRaj Vakati
Code which i have will  work regardless 
Alex Waddell 17Alex Waddell 17
Thank you both!