+ Start a Discussion
Kyle_at_boxKyle_at_box 

Field value mapping (Trouble with Apex code; too many soql queries)

Hi All,

 

I'm relatively new to writing in Apex, so forgive me if it looks like I'm making rookie mistakes - I probably am making rookie mistakes :)

 

Myself and a colleague are trying to perform a cleanup of some old data.  We have a field called "Lead Source," which is a picklist with around 8 or so values.  The powers that be have come up with 7 or so different values that make more sense than the initial 8, so our task is to map the old values to their respective new ones.  Altogether, there are around 40,000 records that need to be adjusted.  

 

We've decided to approach this by writing a simple apex class in our sandbox:

 

 

public class leadSourceMap {
    public VFConnect(ApexPages.StandardController controller) { }
        public static void changeSource(){
        Account[] acclist = [SELECT Id,Lead_Source__c FROM account WHERE account.Lead_Source__c =  'Direct'];
        for(account a:acclist){
        if (a.Lead_Source__c == 'Direct') {
         a.Lead_Source__c = 'Rep Needs To Ask';
         }
         try { update a; } catch ( System.DmlException e) {
 
            system.assert(e.getMessage().contains('irst error: FIELD_CUSTOM_VALIDATION_EXCEPTION, A lead with this email address already exists'),
                e.getMessage());   
                
        }
    }

}
}

 

We couldn't figure out how to make this run, so we decided to build a simple visualforce page that just called the changesource method when any account was accessed:

 

 

 

<apex:page standardController="Account"
 extensions="VFConnect"
 action="{!changeSource}"
>
  <apex:sectionHeader title="Auto-Running Apex Code"/>
  <apex:outputPanel >
      You tried calling Apex Code from a button.  If you see this page, something went wrong.  You should have
      been redirected back to the record you clicked the button from.
  </apex:outputPanel>
</apex:page>

 To our great joy, this script worked when we tested it on a batch of 25 accounts that we had loaded into my sandbox.  Unfortunately, when I tried it on about 500 accounts, it returned this error:

 

Content cannot be displayed: Assertion Failed: Update failed. First exception on row 0 with id 001P000000HMwuFIAT; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, EDP1.ActivityReset: execution of AfterUpdatecaused by: System.LimitException: EDP1:Too many SOQL queries: 101(EDP1): []

 

It looks like we're querying the database for each action, but I meant to write our apex such that it queried once and then pulled from that array to go through and make the appropriate changes.  Any help on both the SOQL question and on how we might be able to architect this better would be greatly appreciated!

 

Thank you all!

 

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
mohimohi

pls try this update outside forloop

Hope this will help

list<account>act=new list<account>();

for(account a:acclist)

{

if (a.Lead_Source__c == 'Direct')

{

a.Lead_Source__c = 'Rep Needs To Ask';

act.add(a);

}

}

try { update act; } catch ( System.DmlException e) { system.assert(e.getMessage().contains('irst error: FIELD_CUSTOM_VALIDATION_EXCEPTION, A lead with this email address already exists'), e.getMessage()); }

All Answers

mohimohi

pls try this update outside forloop

Hope this will help

list<account>act=new list<account>();

for(account a:acclist)

{

if (a.Lead_Source__c == 'Direct')

{

a.Lead_Source__c = 'Rep Needs To Ask';

act.add(a);

}

}

try { update act; } catch ( System.DmlException e) { system.assert(e.getMessage().contains('irst error: FIELD_CUSTOM_VALIDATION_EXCEPTION, A lead with this email address already exists'), e.getMessage()); }

This was selected as the best answer
Kyle_at_boxKyle_at_box

That was a great help, thank you!  Aside from keeping the update out of the for loop, the trick was to update across the entire array, not individually on every account within the array...

sieb4mesieb4me
hi just curious, if there are so many accounts in array and you update entire array, wont it cause heapsize error?