+ Start a Discussion
Sunny Solanki 11Sunny Solanki 11 

SOQL 101 issue due to query in for loop. Need help

I have the following trigger which is poorly written and causing during data load and record update for certain records. I am trying to fix the query issue at list for now. But I am not expert like you guys so need help. 

Following Code trying to update but getting- Unexpected token issue. 
List <Investor_Position__c> listIvPost = [Select Id,Investor_Position_Account__c,Is_Active__c, Current_Commitments_Functional__c, Source_of_Capital__c,
                                                Investment_Vehicle__r.Id, Investment_Vehicle__r.Number_of_Current_Investors__c, Investment_Vehicle__r.Number_of_Historical_Investors__c
                                                from Investor_Position__c where Investment_Vehicle__r.Id IN  InvestmentVehicleIds];
            for(Id id : InvestmentVehicleIds)
            {
                for( Investor_Position__c obj :listIvPost )
                {
                    
                    if(obj.Is_Active__c == TRUE && obj.Source_of_Capital__c != null)
                    {



Original Code: 
trigger InvestorPositionTrigger on Investor_Position__c (after insert, after update, after delete) {

Set<Id> numofhistinvestors = new Set<Id>(); 
Set<Id> numofcurrentinvestors = new Set<Id>(); 
Set<Id> InvestmentVehicleIds = new Set<Id>(); 

List<Investment_Vehicle__c> lstUpdateInvVehicle = new List<Investment_Vehicle__c>();

if(trigger.isafter)
{
    if(trigger.isinsert || trigger.isupdate)
    {
        
            for(Investor_Position__c inv : Trigger.New)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }   
     if(trigger.isdelete)
    {
        
            for(Investor_Position__c inv : Trigger.old)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }       
    
            for(Id id : InvestmentVehicleIds)
            {
                for( Investor_Position__c obj : [Select Id,Investor_Position_Account__c,Is_Active__c, Current_Commitments_Functional__c, Source_of_Capital__c,
                                                Investment_Vehicle__r.Id, Investment_Vehicle__r.Number_of_Current_Investors__c, Investment_Vehicle__r.Number_of_Historical_Investors__c
                                                from Investor_Position__c where Investment_Vehicle__r.Id =:id])
                {
                    
                    if(obj.Is_Active__c == TRUE && obj.Source_of_Capital__c != null)
                    {                       
                    
                        if(obj.Current_Commitments_Functional__c > 0 && (obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner')))
                        {
                            numofcurrentinvestors.add(obj.Investor_Position_Account__c);  
                        }
                        if(obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner'))
                        {
                            numofhistinvestors.add(obj.Investor_Position_Account__c); 
                        }
                    }                   
                }
                
                Investment_Vehicle__c invveh = new Investment_Vehicle__c();
                invveh.Id = id;
                invveh.Number_of_Historical_Investors__c = numofhistinvestors.size();
                invveh.Number_of_Current_Investors__c = numofcurrentinvestors.size();
                
                lstUpdateInvVehicle.add(invveh);
                numofcurrentinvestors.clear();
                numofhistinvestors.clear();
            }  
            
            try
            {
                    if(lstUpdateInvVehicle.size() > 0)
                    {
                        update lstUpdateInvVehicle; 
                    }
                    
            }
            catch(exception ex)
            {
                 for (Investor_Position__c obj : trigger.new) 
            {         
                obj.addError(ex.getmessage());        
            } 
            }      
        
    
}

	if(Trigger.isInsert){
		ConfigurableRollup.rollup(trigger.new);
	}
	if(Trigger.isUpdate){
		system.debug('when is update------');
		ConfigurableRollup.rollup(trigger.new, Trigger.OldMap);
	}
	if(Trigger.isDelete){
		ConfigurableRollup.rollup(trigger.old);
	}
}

 
Best Answer chosen by Sunny Solanki 11
PawanKumarPawanKumar
Please try below code.

trigger InvestorPositionTrigger on Investor_Position__c (after insert, after update, after delete) {

Set<Id> numofhistinvestors = new Set<Id>(); 
Set<Id> numofcurrentinvestors = new Set<Id>(); 
Set<Id> InvestmentVehicleIds = new Set<Id>(); 

List<Investment_Vehicle__c> lstUpdateInvVehicle = new List<Investment_Vehicle__c>();

if(trigger.isafter)
{
    if(trigger.isinsert || trigger.isupdate)
    {
        
            for(Investor_Position__c inv : Trigger.New)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }   
     if(trigger.isdelete)
    {
        
            for(Investor_Position__c inv : Trigger.old)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }       
    
            Map<String,Integer> numofcurrentinvestorsMap = new Map<String,String>();
            Map<String,Integer> numofhistinvestorsMap = new Map<String,String>();


                for( Investor_Position__c obj : [Select Id,Investor_Position_Account__c,Is_Active__c, Current_Commitments_Functional__c, Source_of_Capital__c,
                                                Investment_Vehicle__r.Id, Investment_Vehicle__r.Number_of_Current_Investors__c, Investment_Vehicle__r.Number_of_Historical_Investors__c
                                                from Investor_Position__c where Investment_Vehicle__r.Id IN:InvestmentVehicleIds])
                {
                    
                    if(obj.Is_Active__c == TRUE && obj.Source_of_Capital__c != null)
                    {                       
                    
                        if(obj.Current_Commitments_Functional__c > 0 && (obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner')))
                        {
                            if(numofcurrentinvestorsMap!=null && numofcurrentinvestorsMap.containsKey(obj​.Investment_Vehicle__r.Id)){
                                numofcurrentinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,numofcurrentinvestorsMap.get(obj​.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofcurrentinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,1);
                            }

                            //numofcurrentinvestors.add(obj.Investor_Position_Account__c);  
                        }
                        if(obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner'))
                        {
                            if(numofhistinvestorsMap!=null && numofhistinvestorsMap.containsKey(obj​.Investment_Vehicle__r.Id)){
                                numofhistinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,numofhistinvestorsMap.get(obj​.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofhistinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,1);
                            }

                            //numofhistinvestors.add(obj.Investor_Position_Account__c); 
                        }
                    }                   
                }
                
            for(Id id : InvestmentVehicleIds)
            {    
                Investment_Vehicle__c invveh = new Investment_Vehicle__c();
                invveh.Id = id;
                if(numofhistinvestorsMap.containsKey(id)){
                    invveh.Number_of_Historical_Investors__c = numofhistinvestorsMap.get(id);
                }
                
                if(numofcurrentinvestorsMap.containsKey(id)){
                    invveh.Number_of_Current_Investors__c = numofcurrentinvestorsMap.get(id);
                }
                
                lstUpdateInvVehicle.add(invveh);
                //numofcurrentinvestors.clear();
                //numofhistinvestors.clear();
            }  

            
            try
            {
                    if(lstUpdateInvVehicle.size() > 0)
                    {
                        update lstUpdateInvVehicle; 
                    }
                    
            }
            catch(exception ex)
            {
                 for (Investor_Position__c obj : trigger.new) 
            {         
                obj.addError(ex.getmessage());        
            } 
            }      
        
    
}

    if(Trigger.isInsert){
        ConfigurableRollup.rollup(trigger.new);
    }
    if(Trigger.isUpdate){
        system.debug('when is update------');
        ConfigurableRollup.rollup(trigger.new, Trigger.OldMap);
    }
    if(Trigger.isDelete){
        ConfigurableRollup.rollup(trigger.old);
    }
}

--------------------------------------
Please mark it best if it helps you. Thanks.
 

All Answers

pankul guptapankul gupta
Hi Sunny,

Try to define your try-catch block outside the if(trigger.isafter) block, i.e, close the section if(trigger.isafter) by putting a curly brace } at line number 58 instead of clocing it at line number 76.
And then define the try-catch block after that.

Please let me know if it works.
Sunny Solanki 11Sunny Solanki 11
Hi Pankul,
Sorry, it won't help me in SOQL 101 issue. I did try and no change in result. Still facing SOQL 101 error. 
PawanKumarPawanKumar
Please try below code.

trigger InvestorPositionTrigger on Investor_Position__c (after insert, after update, after delete) {

Set<Id> numofhistinvestors = new Set<Id>(); 
Set<Id> numofcurrentinvestors = new Set<Id>(); 
Set<Id> InvestmentVehicleIds = new Set<Id>(); 

List<Investment_Vehicle__c> lstUpdateInvVehicle = new List<Investment_Vehicle__c>();

if(trigger.isafter)
{
    if(trigger.isinsert || trigger.isupdate)
    {
        
            for(Investor_Position__c inv : Trigger.New)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }   
     if(trigger.isdelete)
    {
        
            for(Investor_Position__c inv : Trigger.old)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }       
    
            Map<String,Integer> numofcurrentinvestorsMap = new Map<String,String>();
            Map<String,Integer> numofhistinvestorsMap = new Map<String,String>();


                for( Investor_Position__c obj : [Select Id,Investor_Position_Account__c,Is_Active__c, Current_Commitments_Functional__c, Source_of_Capital__c,
                                                Investment_Vehicle__r.Id, Investment_Vehicle__r.Number_of_Current_Investors__c, Investment_Vehicle__r.Number_of_Historical_Investors__c
                                                from Investor_Position__c where Investment_Vehicle__r.Id IN:InvestmentVehicleIds])
                {
                    
                    if(obj.Is_Active__c == TRUE && obj.Source_of_Capital__c != null)
                    {                       
                    
                        if(obj.Current_Commitments_Functional__c > 0 && (obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner')))
                        {
                            if(numofcurrentinvestorsMap!=null && numofcurrentinvestorsMap.containsKey(obj​.Investment_Vehicle__r.Id)){
                                numofcurrentinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,numofcurrentinvestorsMap.get(obj​.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofcurrentinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,1);
                            }

                            //numofcurrentinvestors.add(obj.Investor_Position_Account__c);  
                        }
                        if(obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner'))
                        {
                            if(numofhistinvestorsMap!=null && numofhistinvestorsMap.containsKey(obj​.Investment_Vehicle__r.Id)){
                                numofhistinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,numofhistinvestorsMap.get(obj​.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofhistinvestorsMap.put(
obj​.Investment_Vehicle__r.Id,1);
                            }

                            //numofhistinvestors.add(obj.Investor_Position_Account__c); 
                        }
                    }                   
                }
                
            for(Id id : InvestmentVehicleIds)
            {    
                Investment_Vehicle__c invveh = new Investment_Vehicle__c();
                invveh.Id = id;
                if(numofhistinvestorsMap.containsKey(id)){
                    invveh.Number_of_Historical_Investors__c = numofhistinvestorsMap.get(id);
                }
                
                if(numofcurrentinvestorsMap.containsKey(id)){
                    invveh.Number_of_Current_Investors__c = numofcurrentinvestorsMap.get(id);
                }
                
                lstUpdateInvVehicle.add(invveh);
                //numofcurrentinvestors.clear();
                //numofhistinvestors.clear();
            }  

            
            try
            {
                    if(lstUpdateInvVehicle.size() > 0)
                    {
                        update lstUpdateInvVehicle; 
                    }
                    
            }
            catch(exception ex)
            {
                 for (Investor_Position__c obj : trigger.new) 
            {         
                obj.addError(ex.getmessage());        
            } 
            }      
        
    
}

    if(Trigger.isInsert){
        ConfigurableRollup.rollup(trigger.new);
    }
    if(Trigger.isUpdate){
        system.debug('when is update------');
        ConfigurableRollup.rollup(trigger.new, Trigger.OldMap);
    }
    if(Trigger.isDelete){
        ConfigurableRollup.rollup(trigger.old);
    }
}

--------------------------------------
Please mark it best if it helps you. Thanks.
 
This was selected as the best answer
PawanKumarPawanKumar
Please let me know if it's working for you. Thanks.
Sunny Solanki 11Sunny Solanki 11

found error saving the code:  

Variable does not exist: obj  for the highlighted code. 

any help? 
 

PawanKumarPawanKumar
Please find the corrected code. some typo error was there.

trigger InvestorPositionTrigger on Investor_Position__c (after insert, after update, after delete) {

Set<Id> numofhistinvestors = new Set<Id>(); 
Set<Id> numofcurrentinvestors = new Set<Id>(); 
Set<Id> InvestmentVehicleIds = new Set<Id>(); 

List<Investment_Vehicle__c> lstUpdateInvVehicle = new List<Investment_Vehicle__c>();

if(trigger.isafter)
{
    if(trigger.isinsert || trigger.isupdate)
    {
        
            for(Investor_Position__c inv : Trigger.New)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }   
     if(trigger.isdelete)
    {
        
            for(Investor_Position__c inv : Trigger.old)
            {
                InvestmentVehicleIds.add(inv.Investment_Vehicle__c);
            }    
    }       
    
            Map<String,Integer> numofcurrentinvestorsMap = new Map<String,String>();
            Map<String,Integer> numofhistinvestorsMap = new Map<String,String>();

                for( Investor_Position__c obj : [Select Id,Investor_Position_Account__c,Is_Active__c, Current_Commitments_Functional__c, Source_of_Capital__c,
                                                Investment_Vehicle__r.Id, Investment_Vehicle__r.Number_of_Current_Investors__c, Investment_Vehicle__r.Number_of_Historical_Investors__c
                                                from Investor_Position__c where Investment_Vehicle__r.Id IN:InvestmentVehicleIds])
                {
                    
                    if(obj.Is_Active__c == TRUE && obj.Source_of_Capital__c != null)
                    {                       
                    
                        if(obj.Current_Commitments_Functional__c > 0 && (obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner')))
                        {
                            if(numofcurrentinvestorsMap!=null && numofcurrentinvestorsMap.containsKey(obj.Investment_Vehicle__r.Id)){
                                numofcurrentinvestorsMap.put(obj.Investment_Vehicle__r.Id,numofcurrentinvestorsMap.get(obj.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofcurrentinvestorsMap.put(obj.Investment_Vehicle__r.Id,1);
                            }
                            //numofcurrentinvestors.add(obj.Investor_Position_Account__c);  
                        }
                        if(obj.Source_of_Capital__c.contains('Limited Partners') || obj.Source_of_Capital__c.contains('Operating Partner'))
                        {
                            if(numofhistinvestorsMap!=null && numofhistinvestorsMap.containsKey(obj.Investment_Vehicle__r.Id)){
                                numofhistinvestorsMap.put(obj.Investment_Vehicle__r.Id,numofhistinvestorsMap.get(obj.Investment_Vehicle__r.Id)+1);
                            }else{
                                numofhistinvestorsMap.put(obj.Investment_Vehicle__r.Id,1);
                            }
                            //numofhistinvestors.add(obj.Investor_Position_Account__c); 
                        }
                    }                   
                }
                
            for(Id id : InvestmentVehicleIds)
            {    
                Investment_Vehicle__c invveh = new Investment_Vehicle__c();
                invveh.Id = id;
                if(numofhistinvestorsMap.containsKey(id)){
                    invveh.Number_of_Historical_Investors__c = numofhistinvestorsMap.get(id);
                }
                
                if(numofcurrentinvestorsMap.containsKey(id)){
                    invveh.Number_of_Current_Investors__c = numofcurrentinvestorsMap.get(id);
                }
                
                lstUpdateInvVehicle.add(invveh);
                //numofcurrentinvestors.clear();
                //numofhistinvestors.clear();
            }  
            
            try
            {
                    if(lstUpdateInvVehicle.size() > 0)
                    {
                        update lstUpdateInvVehicle; 
                    }
                    
            }
            catch(exception ex)
            {
                 for (Investor_Position__c obj : trigger.new) 
            {         
                obj.addError(ex.getmessage());        
            } 
            }      
        
    
}

    if(Trigger.isInsert){
        ConfigurableRollup.rollup(trigger.new);
    }
    if(Trigger.isUpdate){
        system.debug('when is update------');
        ConfigurableRollup.rollup(trigger.new, Trigger.OldMap);
    }
    if(Trigger.isDelete){
        ConfigurableRollup.rollup(trigger.old);
    }
}
 
PawanKumarPawanKumar
still getting SOQL error? otherwise please mark it completed. Thanks.
Sunny Solanki 11Sunny Solanki 11
Thank you!!!!  code working fine.