function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
arishi0arishi0 

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.'   
       }
    }
}

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Cory CowgillCory Cowgill

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

Cory CowgillCory Cowgill

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;}

This was selected as the best answer
arishi0arishi0

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!!!!