Apex Code Development You need to sign in to do that
Don't have an account?
Count cumulative child for all account
I have an account hierarchy in my project. I have to count cumulative child for all account and store into Child_count__c in Account. I wrote the following Batch apex, but not getting success. Please let me know any right approach.
Batch Apex code as follows:
global class AccountChild_count implements Database.Batchable<sObject>{
global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator('Select Id, ParentId, Parent.ParentId, Parent.Parent.ParentId,Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.ParentId,Parent.Parent.Parent.Parent.Parent.ParentId from account where ACCT_STATUS__c=\'ACTIVE\' AND COUNTRY_FORM__c=\'USA\' LIMIT 5');
}
global void execute(Database.BatchableContext BC, List<sObject> scope){
try{
Map<ID, String> map_str1 = new Map<Id, String>();
Map<ID, Account> map_str2 = new Map<Id, Account>();
String str_of_Ids;
for(Integer i=0;i<scope.size();i++){
Account acc=(Account)scope.get(i);
str_of_Ids=String.ValueOf(acc.ParentId)+','+acc.Parent.ParentId+','+acc.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.Parent.Parent.ParentId;
map_str1.put(acc.id, str_of_Ids);
map_str2.put(acc.id, acc);
}
List <Account> accList=map_str2.values();
for(Account acc1 :accList){
acc1.Parent_ID_List__c=map_str1.get(acc1.id);
}
update accList;
for(Account acc2:accList){
Integer intNumberOfContacts = [SELECT count()FROM Account WHERE Parent_ID_List__c Like : acc2.ID];
acc2.Child_Count__c=intNumberOfContacts;
}
update accList;
}catch(Exception ex){ }
}
global void finish(Database.BatchableContext BC){
}
}
Hi,
Looks like you have to count child records for your account and store it in some field.
If possible you can achieve this using a simple class which can be invoked using a link or button.
But if you have too many records and if you are corssing the governors then your approach is correct. If you can elaborate in more of a logical point of view what you have to achieve then I will try to help you from my side.
Hi Kulkarni,
yes i have more then 1.5 milions of account records and the account hierarchy is upto 6 level in depth. Please let me know your approach because i have tried all the possible ways from my end. It would be good if you please have a look of my code and logic of Batch Apex which I have implemented and do the correction.
I appriciate you help.
Batch Apex as follows.
global class AccountChild implements Database.Batchable<sObject>{global Database.QueryLocator start(Database.BatchableContext BC){
return Database.getQueryLocator('Select Id, ParentId, Parent.ParentId, Parent.Parent.ParentId,Parent.Parent.Parent.ParentId, Parent.Parent.Parent.Parent.ParentId,Parent.Parent.Parent.Parent.Parent.ParentId from account where COUNTRY_FORM__c=\'USA\'');
}
global void execute(Database.BatchableContext BC, List<sObject> scope){
try{
Map<ID, String> map_str1 = new Map<Id, String>();
Map<ID, Account> map_str2 = new Map<Id, Account>();
String str_of_Ids;
for(Integer i=0;i<scope.size();i++){
Account acc=(Account)scope.get(i);
str_of_Ids=String.ValueOf(acc.ParentId)+','+acc.Parent.ParentId+','+acc.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.Parent.ParentId+','+acc.Parent.Parent.Parent.Parent.Parent.ParentId;
map_str1.put(acc.id, str_of_Ids);
map_str2.put(acc.id, acc);
}
List <Account> accList=map_str2.values();
for(Account acc1 :accList){
acc1.Parent_ID_List__c=map_str1.get(acc1.id);
}
update accList;
}catch(Exception ex){ }
}
global void finish(Database.BatchableContext BC){
}
}
Hi,
First the query that you written to get the Accounts written only Id and ParentId not more information than that. So if you can check your query in "System Log" it will give you information only upto the Parent of current Account.
Looks like that is why other part of your logic is not working. So by writing the Batch Apex you may not be able to solve this problem. I have tried to implement the same thing and got success with it. Check if you can implement it. This is how I have gone about solving it.
1. Create one formula field which will store the count of Parent (Essentially it will be either 0 or 1)
ie. IF(Parent_Count__c <> NULL,1,0)
2. Create one workflow which will do the actual counting. The workflow will be rule based, so you can have a rule in such a way that your workflow will get fired. It depends on you how you want the rule(in your case it looks like u want Country Form = 'US'). Then you can have a Field Update on this Rule which will be like this.
Parent_Count__c + Parent.Parent_Count__c + Parent.Parent.Parent_Count__c + Parent.Parent.Parent.Parent_Count__c + Parent.Parent.Parent.Parent.Parent_Count__c + Parent.Parent.Parent.Parent.Parent.Parent_Count__c
So this formula will calculate Parent Count. I think in your case per Account you can have max 6 Parents.
Also You can make this formula more complicated by accomodating IF(Parent_Count__c <> NULL,1,0) in same formula.
Hope this will help you!!
Hi Kulkarni,
Thanks for your wornderfull approach.
Could you please put more lite on your point 1.
1. Create one formula field which will store the count of Parent (Essentially it will be either 0 or 1)
ie. IF(Parent_Count__c <> NULL,1,0)
how to populate the Parent_Count__c ...also please explain this formula.
Thanks,
Hi,
Sorry, By mistake i put up the wrong formula.
The formula should be like this IF(ParendId <> NULL,1,0)
So that if for account Parent is present then the Parent_Count__c's value will be 1 else 0.
And then in workflow you can use these values for summing up.
Hi Kulkarni,
Thanks for your quick reply.
Actually I have Account hierarchy in my project and the max level of depth is 6 level.
I am showing this hierarchy by flex. So My client requirement is to while showing the hierarchy the top most node should show the cumulative count of child in hierarchy.
According to your approach I am able to count direct child only but not the sibling child.
For example I have
Test1 [cumulative count : 7]
|
|
Test2 --------------------------
| | |
| Test21 Test 22
Test3 ---------------------------
| | |
| Test31 Test32
Test4
For the above hierarchy For the node (Test1) cumulative count should be 7.
But according to your approach Its coming 3.
Thanks,
Hi,
Where is the Screen Shot??
Hi Kulkarni,
Thanks for your quick reply.
Actually I have Account hierarchy in my project and the max level of depth is 6 level.
I am showing this hierarchy by flex. So My client requirement is to while showing the hierarchy the top most node should show the cumulative count of child in hierarchy.
According to your approach I am able to count direct child only but not the sibling child.
For example I have
Test1 [cumulative count : 7]
|
|
Test2 --------------------------
| | |
| Test21 Test 22
Test3 ---------------------------
| | |
| Test31 Test32
Test4
For the above hierarchy For the node (Test1) cumulative count should be 7.
But according to your approach Its coming 3.
Thanks,
Did you ever come to a solution on this?
Thank You