You need to sign in to do that
Don't have an account?
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!
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
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()); }
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...