We know all too well that in many cases organizations have way above 8000 Account records after couple of years of using
Salesforce CRM and this number in even higher when we talk about Contacts, Opportunities, Tasks, etc.
Many
Salesforce partners have provided solutions to mass update/insert data into your objects, but more often you are required to examine data and make sure about its
integrity and soundness.
Limitations:
Based on governing limitation provided by force.com platform:
- You can only retrieve 1000 records at a time
- You can only update/insert/delete 100 records at a time
Luckily, there is something that you can do about this on your own. By doing a little bit more extra programming, use of
visualforce components and apex Triggers you can overcome this problem.
The solution won't be as fast, but it will work!
Let's explore our example here: Let's say we need to check all of our Account records and make sure Parent child relationship between our Accounts are correctly in place.
In order to enforce that let's say we have added Dun & Bradstreet ID and Parent Dun & Bradstreet ID numbers as custom fields to our Account object.
Therefore, if the D&B Parent ID has a value but the Parent Account field is empty we have a broken link. I should point out here that the above is just an example, however you may have any type of logic to validate individual objects records, checking Accounts agaist Opportunities, Quotes against Invoices, etc.
Here is the idea, I have over 12,000 records to process and this is well above the governing limits. So if I could divide the task into smaller number of records and process each at a time then I could iterate through the records and fix them one by one without hitting into any limits.
So, I added an integer flag custom field to Account object to mark those I fix and move on through the further records in the next iterations.
I employ an actionPoller that runs every 10 seconds that triggers my operation of processing my Account records in batches of 50. The actionPoller is deactivate in the begining and can become activated via a button "Start". The same button will be able stop the process.
I follow this logic, at first I select the first 50 accounts which do not have parent but do have Parent D&B number and I will try to locate the Account Id of the parent via "Parent D&B number" if not sucessful I mark the Account Heirarchy status as "Broken Link" (for reporting purposes), if the parent was not found I mark the Account Heirarchy Status as "Parent Missing" and if everything was ok then I link up the Account to its parent.
Here the how the page code looks like:
<apex:page controller="AccountHierarchyBuilderCon" tabStyle="Account">
<apex:sectionHeader title="Account Hierarchy Builder"></apex:sectionHeader>
<apex:form >
<apex:pageBlock title="{!StatusText}" id="pageBlock">
<apex:pageBlockButtons >
<apex:commandButton value="{!buttonCaption}" action="{!TaggleActionPoller}" id="theButton"></apex:commandButton>
<apex:commandButton value="Launch Reports" action="{!LaunchReports}" id="ButtonReports" disabled="{!actionPollerEnabled}"></apex:commandButton>
<apex:commandButton value="Clean up" action="{!UpdateAccounts}" id="secButton" disabled="{!actionPollerEnabled}" onclick="return window.confirm('Removing status values will prepare this tool to re-examine invalid hierarchies. Are you sure about this?');"></apex:commandButton>
<apex:commandButton value="Clean up All" action="{!UpdateAccountsAll}" id="CleanAllButton" disabled="{!actionPollerEnabled}" onclick="return window.confirm('Removing all status values will prepare this tool to start over. Are you sure about this?');"></apex:commandButton>
</apex:pageBlockButtons>
<apex:actionPoller enabled="{!actionPollerEnabled}" interval="10" action="{!RunBatch}" rerender="pageBlock" status="status"></apex:actionPoller>
<apex:actionStatus id="status" startText="Working on the next batch..."></apex:actionStatus>
<br/>
<apex:pageBlockSection columns="1" id="resultsBlock">
<apex:pageMessages />
<div style="background-color:white;display:{!IF(invalidAccountSize=0,'none','block')};"> List of Invalid Accounts:</div>
<apex:pageBlockTable value="{!invalidAccounts}" var="a" title="Current Invalid Records" rendered="{!NOT(invalidAccountSize=0)}">
<apex:column value="{!a.Id}" ></apex:column>
<apex:column value="{!a.Name}" ></apex:column>
<apex:column value="{!a.parent_D_B_number__c}"></apex:column>
<apex:column value="{!a.Hierarchy_Status__c}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlockSection>
</apex:pageBlock>
</apex:form>
</apex:page>
As you observed in the code, I have added a pageBlockTable component to show the invalid Accounts (Broken Links or missing parents) to the user between the intervals.
Here is the controller's code:
public class AccountHierarchyBuilderCon {
private Boolean pisWorking;
private Boolean pActionPollerEnabled;
private Integer ptotalRecords;
private Integer ptotalProcessed;
private List
InvalidAccounts;
private String pbuttonCaption;
private Integer pTotalModified;
private Boolean pIsTesting = false;
public AccountHierarchyBuilderCon()
{
InvalidAccounts = new List();
pisWorking = false;
pbuttonCaption = 'Start';
pActionPollerEnabled = false;
ptotalProcessed = 0;
pTotalModified = 0;
ptotalRecords = -1;
}
public Boolean getIsTesting()
{
return pIsTesting;
}
public void setIsTesting(Boolean Value)
{
pIsTesting = Value;
}
public boolean getIsWorking()
{
return pisWorking;
}
public String getTotalRecords()
{
if (ptotalRecords == -1)
{
if (pIsTesting == true)
ptotalRecords = 10000;
else
ptotalRecords = [select count() from Account WHERE (Hierarchy_Status__c = '' OR Hierarchy_Status__c = null) AND (parent_D_B_number__c != null) limit 10000];
}
if (ptotalRecords == 10000)
return '10,000 or more';
else
return ptotalRecords+ '';
}
public Integer getTotalProcessed()
{
return ptotalProcessed;
}
public Boolean getActionPollerEnabled()
{
return pActionPollerEnabled;
}
public void setActionPollerEnabled(Boolean Value)
{
pActionPollerEnabled = Value;
}
public String getStatusText()
{
return ptotalProcessed + ' Account(s) out of ' + getTotalRecords() + ' are processed.';
}
public String getButtonCaption()
{
return pbuttonCaption;
}
public Integer getActionPollerInterval()
{
return 10;
}
public List getInvalidAccounts()
{
return InvalidAccounts;
}
public Integer getInvalidAccountSize()
{
return InvalidAccounts.size();
}
public PageReference TaggleActionPoller()
{
if (pActionPollerEnabled)
{
pActionPollerEnabled = false;
pbuttonCaption = 'Start';
}
else
{
pActionPollerEnabled = true;
pbuttonCaption = 'Stop';
}
return null;
}
public PageReference RunBatch()
{
if (pisWorking)
return null;
else
{
pisWorking = true;
}
try
{
UpdateAllAccounts();
}
catch(Exception ex)
{
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.FATAL,ex.getMessage()));
TaggleActionPoller();
}
//end of the procedure
pisWorking = false;
return null;
}
public PageReference UpdateAllAccounts()
{
Integer sucessCount = 0;
InvalidAccounts = new List();
List Accounts = [select id, Name, parent_D_B_number__c, ParentId, Hierarchy_Status__c from Account WHERE (Hierarchy_Status__c = '' OR Hierarchy_Status__c = null) AND (parent_D_B_number__c != null) limit 50];
for(Account a : Accounts)
{
String ParentDBNo = (a.parent_D_B_number__c == null a.parent_D_B_number__c == '')? 'dummy_unknown' : a.parent_D_B_number__c;
List parents = null;
try
{
parents = [Select id, Name From Account where (D_B_Number__c = :ParentDBNo AND id != :a.id)];
}
catch(Exception e)
{}
Account parent = null;
if (parents == null parents.size() <= 0) { a.Hierarchy_Status__c = 'Broken Link'; InvalidAccounts.add(a); } else if (Parents.size() > 1)
{
a.Hierarchy_Status__c = 'Duplicated Parent';
InvalidAccounts.add(a);
}
else
{
parent = parents[0];
}
if (parent != null && parent.id != a.id)
{
a.ParentId = parent.id;
a.Hierarchy_Status__c = 'Updated';
sucessCount++;
}
try
{
update a;
}
catch(Exception ex)
{
a.Hierarchy_Status__c = 'Update Failed';
try
{
a.ParentId = null;
update a;
}
catch(Exception ex2)
{
//do nothing
}
InvalidAccounts.add(a);
}
ptotalProcessed += 1;
}//end for
String msg = '';
if (Accounts.size() == 0)
{
msg = 'All Records are up to date.';
TaggleActionPoller();
}
else
{
msg = ' A total of ' + sucessCount + ' Account(s) updated and ' + InvalidAccounts.size() + ' failed to get updated. This list will updated in '+ getActionPollerInterval() + ' seconds.';
}
ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.INFO, msg));
return null;
}
public PageReference UpdateAccounts()
{
//updates only invalid ones
updateAccountHierarchy('Updated','');
return null;
}
public PageReference UpdateAccountsAll() {
//updates all accounts
updateAccountHierarchy('','');
return null;
}
private void updateAccountHierarchy(String Find, String updateTo)
{
if (pIsTesting == true)
{
//do nothing. We are testing!
}
else
{
//this code cleans up all accounts Hierarchy_Status__c
for(Account[] accs : [Select id, Hierarchy_Status__c from Account where Hierarchy_Status__c != :Find limit 10000])
{
for(Account a : accs)
{
a.Hierarchy_Status__c = updateTo;
}
update accs;
}
Integer Count = [Select count() from Account where Hierarchy_Status__c != :Find limit 10000];
if (count > 0)
updateAccountHierarchy(Find,updateTo);
}
}
public PageReference LaunchReports()
{
PageReference ref = new PageReference('/apex/AccountHierarchyReports');
ref.setRedirect(true);
return ref;
}
}
The controller also forwards the user to another visualforce page which user will be able to see the report (all invalid accounts). This is something that you can implement easily on you own.
Now the last touch-ups. Our page iterates through all Accounts and mark them once and for all. Therefore, we will face two problems:
- What if we wanted to re-run the process?
This can be taken care of if you write a method to remove the flags from all or some of the records based on what makes sense to you.
- What if I fixed one of the invalid Accounts, how the can the system know or the flag to be set to OK?
The answer is a Trigger on the Account object.
trigger HierarchyStatusReferesh on Account (before update) {
// this trigger removes the previous value of "Hierarchy_Status__c" field
// if the account's parent_duns_number__c or Ultimate_Parent_D_B_Number__c are changed.
// this trigger work in conjunction with Account Hierarchy Builder tool.
Map entries = new Map(Trigger.old);
for(Account newAcc: Trigger.new)
{
Account acc = entries.get(newAcc.id);
if (Acc.parent_D_B_number__c != newAcc.parent_D_B_number__c)
newAcc.Hierarchy_Status__c = '';
}
}
