function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
cathy369cathy369 

Select in Trigger querying too many records - can i create a "key" on a field in an object?

i'm trying to update an opportunity record based on a custom field, order number, that is unique, but defined as a formula field.

 

i have a trigger that creates a keyset and then does a for loop selecting records that match the order number, so it potentially accesses all records... i'm approaching governor limits due to the number of opportunity records...

 

is there a way to define a field as a "key" so not all records will have to be accessed?  will a unique id field work?

 

thanks very much for any input.

Saikishore Reddy AengareddySaikishore Reddy Aengareddy

can you post your code?
cathy369cathy369

sure... here's the code... i'm no apex expert, and inherited this, but have made some changes.... thanks much for any help... 

 

oh, a little more info... the field SF_Order_Number_Ref__c, is a formula field that is just set to the value of another field, SF_Order_Number, which is an auto number field... perhaps i should use just the autonumber field??

 

_________________________________________________________________________________________

 

trigger mergei5Opps on Opportunity (after insert) {

    Map<Double, Opportunity> oppsWithSFOrderNum = new Map<Double, Opportunity>();
    Map<Id, Opportunity> oppsToDelete = new Map<Id, Opportunity>();
    
    for (Opportunity o : System.Trigger.new) {
        if (o.i5_SF_Order_Num__c > 0 && o.Is_i5_Record__c && o.i5_Line_Type__c == null) {
            oppsWithSFOrderNum.put(o.i5_SF_Order_Num__c, o);
        }
    }
    
    if (oppsWithSFOrderNum.size() != 0){
       List<Opportunity> updatedOpps = new List<Opportunity>();
    
       for (Opportunity o2 : [SELECT id, Name, i5_Invoice_Date__c, SF_Order_Number_Ref__c, i5_Amount__c, i5_Backorder_Qty__c, i5_Customer_No__c, i5_Dist_Code__c, i5_Invoice_Number__c, i5_Line_Type__c, i5_Order_Number__c, i5_Order_Qty__c, i5_SF_Order_Num__c, i5_Ship_Qty__c, i5_Stock_Description__c, i5_Unit_Price__c, i5_Unique_Id__c
                              FROM opportunity WHERE SF_Order_Number_Ref__c in :oppsWithSFOrderNum.keySet()]) {

          Opportunity i5Opportunity = oppsWithSFOrderNum.get(o2.SF_Order_Number_Ref__c);

          if  (o2.i5_Invoice_Number__c == null || o2.i5_Amount__c == 0) {
             o2.i5_Amount__c = i5Opportunity.i5_Amount__c;
             o2.i5_Unit_Price__c = i5Opportunity.i5_Unit_Price__c;
             o2.i5_Invoice_Date__c = i5Opportunity.i5_Invoice_Date__c;
             o2.i5_Backorder_Qty__c = i5Opportunity.i5_Backorder_Qty__c;
             o2.i5_Customer_No__c = i5Opportunity.i5_Customer_No__c;
             o2.i5_Dist_Code__c  = i5Opportunity.i5_Dist_Code__c;
             o2.i5_Invoice_Number__c = i5Opportunity.i5_Invoice_Number__c;
             o2.i5_Line_Type__c = i5Opportunity.i5_Line_Type__c;
             o2.i5_Order_Number__c = i5Opportunity.i5_Order_Number__c;
             o2.i5_Order_Qty__c = i5Opportunity.i5_Order_Qty__c;
             o2.i5_Ship_Qty__c = i5Opportunity.i5_Ship_Qty__c;
             o2.i5_Stock_Description__c = i5Opportunity.i5_Stock_Description__c;
             o2.i5_Stock_Code__c = i5Opportunity.i5_Stock_Code__c;
             o2.i5_SF_Order_Num__c = i5Opportunity.i5_SF_Order_Num__c;

          }
          oppsToDelete.put(i5Opportunity.Id, i5Opportunity);
        
          updatedOpps.add(o2);
        
          o2.Clone_Check__c = true;   
       }

       update updatedOpps;
    
       List<Opportunity> deletedOpps = new List<Opportunity>();
       for (Opportunity od : [SELECT id, Name
                              FROM opportunity WHERE Id in :oppsToDelete.keySet()]) {
        
          deletedOpps.add(od);
                        
       }
       delete deletedOpps;
    }
}