Apex Code Development You need to sign in to do that
Don't have an account?
arishi0 Query Exception on trigger code. I cannot find where is the error
I keep getting a System.QueryException: List has no rows for assignment to SObject exception for a trigger I created. I am not sure how to solve the issue, can anyone help????
trigger CalculateTotalBonusPercentageNEW on Sales_Credit_Detail__c (before insert, before update) {
//Declaring Global Variables
map<Id, Commission__c> currentCommissionsMap = new map<Id, Commission__c>();
Id oppOwnerId;
Account OppAcc;
Sales_Goal__c sg;
Boolean test = true;
String probando;
// get all current commissions
for(Commission__c c : [Select Id, Accelerator__c, End_Date__c, Start_Date__c From Commission__c Where Start_Date__c <= :System.today() AND End_Date__c >= :System.today()])
{ currentCommissionsMap.put(c.Id, c);}
for (Sales_Credit_Detail__c scd : trigger.new){
Id scr = scd.Sales_Credit_Recipient__c;
//if trigger is insert then change owner to SCR.
if(Trigger.IsInsert){scd.OwnerId = scr ;}
//Map the SCD's Opportunity to a variable
Opportunity o = [SELECT Id, AccountId,CloseDate,Type,CommPerN__c,StageName,Name,New_Business_Type__c
From Opportunity where ID = :scd.ParentOpportunity__c LIMIT 1];
System.Debug(o);
try{
// get sales goal for SCD Recipient. Check currentCommissionsMap is not null.
if( currentCommissionsMap.isEmpty()){
test = false;
}else{
sg = [select Met_Date__c, User__c, Commission__r.Accelerator__c
From Sales_Goal__c
Where User__c = :scr AND Commission__c IN :currentCommissionsMap.keySet() LIMIT 1];
}
if( sg == Null){test = false;}
//validate if account region and Opp parameters are correct, if so write to total bonus Percentage with extra kicker, else write only Commission Percent New value
if(test == true){
if (sg.Met_Date__c != null && o.CloseDate >= sg.Met_Date__c &&
o.Type == 'New Business' &&
o.StageName == 'Closed Won' &&
(o.New_Business_Type__c.equalsIgnoreCase('New Account') ||
o.New_Business_Type__c.equalsIgnoreCase('Add Prop Existing Acct') ||
o.New_Business_Type__c.equalsIgnoreCase('Add Prod/Srvcs Existing Acct') ||
scd.ParentOpportunity__r.New_Business_Type__c.equalsIgnoreCase('Parent/Chain/Partner Switch'))
){
Decimal accelerator = (sg.Commission__r.Accelerator__c > 0) ? sg.Commission__r.Accelerator__c : 0;
scd.zCommAccel__c = accelerator;
scd.Total_Bonus_Percentage__c = o.CommPerN__c + accelerator;
}
} else {
scd.zCommAccel__c = 0;
scd.Total_Bonus_Percentage__c = o.CommPerN__c;
}
}catch (Exception e){
scd.addError( e + ' Test:' + test + ' /' + ' sg:'+ sg);
// 'There was a problem when saving your Sales Credit Detail record. Please validate that the Opportunity values are correct or that the User has a sales Goal and that the Met Date is less than the Opportunity Close Date.'
}
}
}
System.QueryException: List has no reows for assignment to SObject - This exception is thrown by the system whenever you perform a SOQL statement and store the result inside an SObject and there are 0 rows returned. In your code you are doing this in a few places:
1. If this SOQL finds 0 opportunities, you will get the exception:
//Map the SCD's Opportunity to a variable
Opportunity o = [SELECT Id, AccountId,CloseDate,Type,CommPerN__c,StageName,Name,New_Business_Type__c
From Opportunity where ID = :scd.ParentOpportunity__c LIMIT 1];
System.Debug(o);
2. If this SOQL finds 0 Sale_Goal__c, you will get the exception:
sg = [select Met_Date__c, User__c, Commission__r.Accelerator__c
From Sales_Goal__c
Where User__c = :scr AND Commission__c IN :currentCommissionsMap.keySet() LIMIT 1];
}
if( sg == Null){test = false;}
Also that statement if(sg == null) will never evaulate because if that line returns 0 resutls it throws an exception, it does not return a null object.
Solutions - Obviously validate your queries. I'd also suggest instead of storing the SOQL results in a single SObject, store them in a List<SObject> and perform a count on the list to ensure its not 0. Something like this:
//Map the SCD's Opportunity to a variable
List<Opportunity> opptys = [SELECT Id, AccountId,CloseDate,Type,CommPerN__c,StageName,Name,New_Business_Type__c
From Opportunity where ID = :scd.ParentOpportunity__c];
System.Debug(o);
if(opptys.size() > 0) { o = opptys.get(0);}else{o = null;}
All Answers
System.QueryException: List has no reows for assignment to SObject - This exception is thrown by the system whenever you perform a SOQL statement and store the result inside an SObject and there are 0 rows returned. In your code you are doing this in a few places:
1. If this SOQL finds 0 opportunities, you will get the exception:
//Map the SCD's Opportunity to a variable
Opportunity o = [SELECT Id, AccountId,CloseDate,Type,CommPerN__c,StageName,Name,New_Business_Type__c
From Opportunity where ID = :scd.ParentOpportunity__c LIMIT 1];
System.Debug(o);
2. If this SOQL finds 0 Sale_Goal__c, you will get the exception:
sg = [select Met_Date__c, User__c, Commission__r.Accelerator__c
From Sales_Goal__c
Where User__c = :scr AND Commission__c IN :currentCommissionsMap.keySet() LIMIT 1];
}
if( sg == Null){test = false;}
Also that statement if(sg == null) will never evaulate because if that line returns 0 resutls it throws an exception, it does not return a null object.
Solutions - Obviously validate your queries. I'd also suggest instead of storing the SOQL results in a single SObject, store them in a List<SObject> and perform a count on the list to ensure its not 0. Something like this:
//Map the SCD's Opportunity to a variable
List<Opportunity> opptys = [SELECT Id, AccountId,CloseDate,Type,CommPerN__c,StageName,Name,New_Business_Type__c
From Opportunity where ID = :scd.ParentOpportunity__c];
System.Debug(o);
if(opptys.size() > 0) { o = opptys.get(0);}else{o = null;}
You are right, I have changed the code to assign the resulting Sales Goal record from the query to a list and then evaluating the size, if >0 then I assign it to an instance of the Sales goal object. The opportunities should not return null ever since ALL sales credits are created from the Opportunity and I've made it a required field also.
I have tried it and NO MORE exception, THANK YOU!!!!