0

I created a trigger to call a apex code (Before insert, Before update). The process is basically initiated when a referral record is created, i used some fields from referrals to do a look up for a ID on another object address and address has million rows. There are multiple criteria's to bring the correct address loop up Id.

The code breaks even if i insert one record. Currently, when i have limit the search to one row, the apex code given me the write answer but as soon as i remove that static value, the apex code gives me an error for governor limit -101

Can someone please help me? I am a new to apex and I am unable to understand how to make the iteration efficient.

Code:

public class CmaReferrals { 
    public static void run () { 
        updateUsingAddress(); 
    }

    public static void updateUsingAddress() {        
        Referral__c [] listRef = [ select ID, street_name__c, street_number__C, street_type__c, Apartment_Unit_Number__c, 
                            City__c, Zip_Postal_Code__c from Referral__C 
                                where
                                STREET_NAME__c = 'lisgar' //(static value) (as soon as i remove this line, the query breaks)
                                and ADDREsS__C = null
                                and STREET_NAME__c !=  null
                                and street_number__c!= Null
                                and (Referral_Type__c = 'Marketing Alliance'
                                or  Referral_Type__c = 'New Construction'      
                                or  Referral_Type__c = 'Placeholder 3') ];  
    
        List<referral__c> refListToUpdate = new List<referral__c>(); 
        
        for(Referral__c row:listRef)
        {
            if (row.city__C != null && row.Zip_Postal_Code__c!= null)
            {
                address__c [] addressId = [ SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c  FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and City__c =  :row.City__c
                                            and Postal_Zip_Code__c= :row.Zip_Postal_Code__c
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                if (addressId == null || addressId.size() < 1)
                {
                    address__c [] addressIdPost = [ SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                                    WHERE Street_name__c = :row.Street_Name__c  
                                                    AND Street_number__c = :row.Street_Number__c 
                                                    and Street_type__C = :row.Street_Type__c
                                                    and ApartNum__c = :row.Apartment_Unit_Number__c
                                                    and City__c =  :row.City__c
                                                    and (ADDR_COMPANY__c Like '2%'
                                                    or   ADDR_COMPANY__c Like '1%')
                                                    and SAM_KEY__c Like '2%'];
                    if(addressIdPost != null && addressIdPost.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressIdPost[0].id, 
                                                            City__c = addressIdPost[0].City__c, 
                                                            Zip_Postal_Code__c = addressIdPost[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressIdPost[0].SAM_Key__c ));
                    }   
                    
                } Else
                {
                    if(addressId != null && addressId.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id, 
                                                            City__c = addressId[0].City__c, 
                                                            Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressId[0].SAM_Key__c ));
                    }
                }      
            } Else if(row.city__C != null)
            {
                address__c [] addressId = [SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c
                                            FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and city__C =  :row.City__c 
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                
                if (addressId == null || addressId.size()<1)
                {
                    address__c [] addressIdCity = [ SELECT Id,  City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                                    WHERE Street_name__c = :row.Street_Name__c  
                                                    and Street_number__c = :row.Street_Number__c 
                                                    and Street_type__C = :row.Street_Type__c 
                                                    and city__C =  :row.City__c
                                                    and ApartNum__c = :row.Apartment_Unit_Number__c
                                                    and (ADDR_COMPANY__c Like '2%'
                                                    or   ADDR_COMPANY__c Like '1%')
                                                    and SAM_KEY__c Like '2%']; 
                    if(addressIdCity != null && addressIdCity.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressIdCity[0].id,
                                                            City__c = addressIdCity[0].City__c, 
                                                            Zip_Postal_Code__c = addressIdCity[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressIdCity[0].SAM_Key__c ));
                    }   
                } Else 
                {
                    if(addressId != null && addressId.size() > 0)
                    {
                        refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id, 
                                                            City__c = addressId[0].City__c, 
                                                            Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                            SAM_Key__c = addressId[0].SAM_Key__c ));
                    } 
                }   
        } Else
        { 
                address__c [] addressId = [SELECT Id, City__c, Postal_Zip_Code__c, SAM_KEY__c FROM  ADDRESS__C 
                                            WHERE Street_name__c = :row.Street_Name__c  
                                            and Street_number__c = :row.Street_Number__c 
                                            and Street_type__C = :row.Street_Type__c
                                            and ApartNum__c = :row.Apartment_Unit_Number__c
                                            and (ADDR_COMPANY__c Like '2%'
                                            or   ADDR_COMPANY__c Like '1%')
                                            and SAM_KEY__c Like '2%'];
                if(addressId != null && addressId.size() > 0)
                {
                    refListToUpdate.add(new referral__c(Id = row.Id, address__C = addressId[0].id,
                                                        City__c = addressId[0].City__c, 
                                                        Zip_Postal_Code__c = addressId[0].Postal_Zip_Code__c,
                                                        SAM_Key__c = addressId[0].SAM_Key__c ));
                }                              
        }
    }
    
    update refListToUpdate;
}

Trigger:

trigger CmaReferralTriggers on Referral__c (after insert, after update) { 
    CmaReferrals.run();
}
3
  • Correction : Trigger (after insert, After update) – Niraj Bahl Feb 22 at 9:34
  • You have a number of SOQL queries built within the for(Referral__c row:listRef) loop. Take some time to read through Apex Design Best Practices where you will find tips and tricks to improve your coding which will mitigate risk of hitting governor limits. – TSmith Feb 22 at 9:43
  • Thank you for your reply Tsmith. I have gone through this article but still do not understand on how to iterate outside for loop. This is my very first code in apex, and I was able to write such a complex code reading examples. – Niraj Bahl Feb 22 at 9:48
0

My 2 cents: You are looking to associate address record to referral record based on the city and postal code of the incoming referral data.

In most of your SOQL's you are using city,postal code or static values like below to

query
and (ADDR_COMPANY__c Like '2%'or   ADDR_COMPANY__c Like '1%')
and SAM_KEY__c Like '2%'
  1. You can try to create Map of records with the same Postal code and city combination and combine them and try to associate address records for them using same SOQL
  2. Use limit 1 when you query on SOQL since you are only taking the first record

If this is a frequent bulk insert requirement for referrals then i would look at batch classes and use batch size to meet this requirement.

The current way of doing it is not so efficient in my opinion.

If you still insist doing it this way and the above suggestions doesn't help. An another way to do it is to create chunks of 50 records and trigger queueable so that the same 200 records is split into 4 chunks and you will get more asynchronous limits to meet your requirement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.