You need to sign in to do that
Don't have an account?
Smike25
Stuck trying to bulkifying code from prior developer
Hello Everyone,
I've been trying to work on code that was left to our ORG by a developer who is no longer supporting us. Not being too familiar with Apex, this is proving to be a daunting task for us and blocking us from implementing the triggers/classes that we've been working on. I know it's a lengthy bit of code, but if someone could offer some assistance we'd be really grateful. Currently we're getting the SQOL 101 errors with this code:
I've been trying to work on code that was left to our ORG by a developer who is no longer supporting us. Not being too familiar with Apex, this is proving to be a daunting task for us and blocking us from implementing the triggers/classes that we've been working on. I know it's a lengthy bit of code, but if someone could offer some assistance we'd be really grateful. Currently we're getting the SQOL 101 errors with this code:
trigger UpdateTruckInventoryByOpportunity_Trgr on Opportunity (before update,before insert,after update, after insert, after delete, after undelete) { List<Truck_Inventory__c> lstTruck2Update = new List<Truck_Inventory__c>(); Set<String> rt2bypass = new Set<String>{'Used Truck Quote','Accessory Order'}; Map<Id,RecordType> usedTruckQuoteRT = new Map<Id,RecordType>([Select SobjectType,Name,Id From RecordType Where SobjectType ='Opportunity' and Name in :rt2bypass]); RecordType usedRT = [Select SobjectType,Name,Id From RecordType Where SobjectType ='Truck_Inventory__c' and Name = 'Used' LIMIT 1]; Map<Id,Truck_Inventory__c> mapNotUsedTruckInv = new Map<Id,Truck_Inventory__c>([Select Id,RecordTypeId From Truck_Inventory__c Where RecordTypeId =:usedRT.Id]); Set<String> setTruckInventories = new Set<String>(); if(Trigger.isAfter){ if(Trigger.isUpdate){ for(Opportunity opp:Trigger.new){ if(usedTruckQuoteRT.get(opp.RecordTypeId)==null){ if(opp.Truck_Inventory__c!=null){ if(opp.StageName == 'Lost Deal - Truck Built' || opp.StageName == 'Lost Deal - Truck Not Built'){ lstTruck2Update.add(new Truck_Inventory__c(Id=opp.Truck_Inventory__c,Sold__c=false, Related_Quote_SO__c=null, Customer__c = null)); }else if(opp.StageName == 'Sales Order - Pending' || opp.StageName == 'Sales Order - Complete'){ lstTruck2Update.add(new Truck_Inventory__c(Id = opp.Truck_Inventory__c,Customer__c = opp.AccountId, Sold__c=true,Related_Quote_SO__c=opp.Id)); }else{ lstTruck2Update.add(new Truck_Inventory__c(Id=opp.Truck_Inventory__c, Sold__c=true,Related_Quote_SO__c=opp.Id)); } } Opportunity oldOpp = Trigger.oldmap.get(opp.Id); if(oldOpp.Truck_Inventory__c!=null && oldOpp.Truck_Inventory__c != opp.Truck_Inventory__c){ lstTruck2Update.add(new Truck_Inventory__c(Id=oldOpp.Truck_Inventory__c,Sold__c=false, Related_Quote_SO__c=null, Customer__c = null)); } } } }else if(Trigger.isInsert || Trigger.isUndelete){ for(Opportunity opp:Trigger.new){ if(usedTruckQuoteRT.get(opp.RecordTypeId)==null) { if(opp.Truck_Inventory__c!=null){ lstTruck2Update.add(new Truck_Inventory__c(Id=opp.Truck_Inventory__c, Sold__c=true,Related_Quote_SO__c=opp.Id)); } } } }else if(Trigger.isDelete){ for(Opportunity opp:Trigger.old){ if(usedTruckQuoteRT.get(opp.RecordTypeId)==null){ if(opp.Truck_Inventory__c!=null){ lstTruck2Update.add(new Truck_Inventory__c(id=opp.Truck_Inventory__c,Sold__c=false, Related_Quote_SO__c=null)); } } } } if(!lstTruck2Update.isEmpty()){ update lstTruck2Update; } } else{ for(Opportunity opp:Trigger.new){ if(usedTruckQuoteRT.get(opp.RecordTypeId)==null) { if(opp.Truck_Inventory__c!=null){ if(!mapNotUsedTruckInv.containsKey(opp.Truck_Inventory__c)){ setTruckInventories.add(opp.Truck_Inventory__c); } } } } if(!setTruckInventories.isEmpty()){ Map<String,String> mTruckInParent = new Map<String,String>(); for(Truck_Inventory__c ti :[Select Truck__c, Id From Truck_Inventory__c where Id in :setTruckInventories]){ mTruckInParent.put(ti.Id, ti.Truck__c); } for(Opportunity opp:Trigger.new){ if(usedTruckQuoteRT.get(opp.RecordTypeId)==null) { opp.Truck__c = mTruckInParent.get(opp.Truck_Inventory__c); } } } } }
Consider a helper class such as the one below (which is based on your trigger). The UsedTruckQuoteRTMap is a static property, which once populated remains available for the duration of your transaction. The GetUsedTruckQuoteRTMap() method populates this map the first time it's called, but each time it's called after that it will simply return the map. The same pattern can be applied to the UsedRT value in your trigger also, as is shown in the helper class code. Once you have this class in place, you can replace some lines of code in your trigger, which will reduce your SOQL query count if the trigger fires more than once in a transaction.
Lines 4 and 5 in your trigger above can be replaced with the following, and Line 7 can be replaced with this line, Without knowing more about your system, I could add that this same lazy initialization pattern can be used to replace the query in line 8 also.
This is one approach that can be taken to reduce the number of SOQL queries executed in a single transaction. It may not get you past your current limit error, but you may find that there are other triggers querying for stable records such as record types or profile ids, which could also be improved by this pattern.
The thing about these "too many SOQL" errors is that the query that causes the error may not be the problem at all. It may be that it simply had the misfortune of being the 101st query to execute, like the straw that broke the camel's back.
If you can capture a debug log of the execution and search for all SOQL statements, you might find that certain ones are being called repeatedly. It may be that better query management is needed, or that you are needlessly entering a loop and the fix would be to avoid the loop altogether.
I hope this gives you some insight and another tool to work with. I look forward to hearing how it goes!
All Answers
Can you paste the full line error and what line number you are hitting the error?! I'm going to quickly go make and make sense of the code and will get back to you.
Consider a helper class such as the one below (which is based on your trigger). The UsedTruckQuoteRTMap is a static property, which once populated remains available for the duration of your transaction. The GetUsedTruckQuoteRTMap() method populates this map the first time it's called, but each time it's called after that it will simply return the map. The same pattern can be applied to the UsedRT value in your trigger also, as is shown in the helper class code. Once you have this class in place, you can replace some lines of code in your trigger, which will reduce your SOQL query count if the trigger fires more than once in a transaction.
Lines 4 and 5 in your trigger above can be replaced with the following, and Line 7 can be replaced with this line, Without knowing more about your system, I could add that this same lazy initialization pattern can be used to replace the query in line 8 also.
This is one approach that can be taken to reduce the number of SOQL queries executed in a single transaction. It may not get you past your current limit error, but you may find that there are other triggers querying for stable records such as record types or profile ids, which could also be improved by this pattern.
The thing about these "too many SOQL" errors is that the query that causes the error may not be the problem at all. It may be that it simply had the misfortune of being the 101st query to execute, like the straw that broke the camel's back.
If you can capture a debug log of the execution and search for all SOQL statements, you might find that certain ones are being called repeatedly. It may be that better query management is needed, or that you are needlessly entering a loop and the fix would be to avoid the loop altogether.
I hope this gives you some insight and another tool to work with. I look forward to hearing how it goes!
ericmonte,
Error is coming at line 18, column 1. If you want me to flush out what this trigger is suppose to accomplish let me know! And thank you for taking a look!
bjpiggins,
First, thank you for taking the time to write out some updated code for us to try. As someone completely new to Apex coding, this looks like a daunting task, but i'll go over what you posted and look up online what I can find out about implenting this. It's been a major set back for us losing our support on this, so thank you. I've got the debug log handy if you'd like/want to review it if it would help you understand any better about what is going on.
When I go to save the RecordTypeHelper class, I get a compile error: "Compile Error: unexpected token: 'return' at line 25 column 8"
Truck_Inventory__c is our custom object where we house our actualy inventory. This gets placed inside our opportunities and depletes our available to sell inventory. We have 3 record types for Truck Inventory... "Stock" (012i0000001IXAo), "Custom" (012i0000001IXHg) and "Used" (012i0000001IXAt).
I would have assumed that I would break down line 8 above by doing what you did to lines 4&5, but didn't we already account for the "Used" record type with
Not quite sure where to proceed
So that is the current RecordTypeHelper class based on the above. I'm trying to replace the line 8 in the trigger:
Im assuming it would be replaced similarly as " Map<Id,RecordType> usedTruckQuoteRT = RecordTypeHelper.GetUsedTruckQuoteRTMap();"
As a side note... I'm not sure why the map would be called "mapNotUsedTruckInv" when the query returns Truck Inventory records where the RecordType is the "Used" record type Id. It seems to be an inaccurate name, but the method will return the same map as was found in the existing code.
"UpdateTruckInventoryByOpportunity_Trgr: execution of BeforeUpdate caused by: System.TypeException: Cannot call test methods in non-test context External entry point Trigger.UpdateTruckInventoryByOpportunity_Trgr: line 15, column 1"
class: