function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
irlrobinsirlrobins 

Find all childless accounts

Hey all,

 

I'm trying to find out a way to determine which accounts records out of my 120K+ accounts have no child objects (contacts, cases, opps, etc).

 

I've tried using apex to determine this but I keep hitting governor limits.

 

Anyone got any suggestions?

Best Answer chosen by Admin (Salesforce Developers) 
Rahul SharmaRahul Sharma

Instead of using count() - aggregate query,

use a subquery, it would be better. since count() will count all the records and each count is equal to number of query row retrived. governor limit on number of row retrived by soql can be hit if there are huge records in your database.

 

In below example, it keep checking if it has each of child or not. if none of child are present, Process it (Make boolean field as true).

At a time batch can contain max 200 records, so collect the account ids in set and you can use pseude code like:

for( Account a : [Select Id, (Select Id from contacts limit 1), (Select Id from Opportunities limit 1), ... , from Account where id in :setAccountId])
{
if(a.contacts.isEmpty())
{}
else if(a.Opportunities.isEmpty())
{}
.......
else
{LstAccountwithNoChilds.add()}
}

Just a Suggestion,

Hope it helps.

All Answers

Ispita_NavatarIspita_Navatar

Hi,

If the parent and child objects are related via master-detail relationship then you can use "Summary Fields". Otherwise (in case lookup relationships) you can have fields in parent object which will hold count of child object  filled by triggers on child objects.

Also alternatively if its a one time exercise, then you can export the relevant data, and using excel fubctions like countif() and aggregate function also get the desired results.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 
Rahul SharmaRahul Sharma

You can create a batch apex class to find such accounts with no child records.

Use a boolean field in Account as "isChildExists", set it true for accounts with no child records.

Run the batch and now you can use below query to find the required accounts.

Select Id, Name from Account where isCHildExists = true

Hope it helps.
raseshtcsraseshtcs

Create roll up summay fileds for each child on the account storing the sum of the childs present. Then query for the accounts where all the roll up summary fields are 0.
grigri9grigri9

There are two ways to go here: a batch job or a visualforce page that is running with the readonly parameter (only works if you are not running dml on those accounts).

 

Your query should look something like the following:

 

[select id, (select id from Contacts limit 1), (select id from Opportunities limit 1) ... from Account]

 

 If you want to get fancy you can even use schema.describe to get the list of children related to the account and generate this query dynamically. One caveat is that you can only have somewhere around 20 sub-queries in one query so if you have more than 20 child objects you will need to break this up somehow.

 

 
irlrobinsirlrobins
Rahul Sharma wrote:

You can create a batch apex class to find such accounts with no child records.

 

I think this would be the best approach, given the large amount of records to be assessed. I haven't used batch apex before though so I need to spend some time examing it and determine how to accomplish this.

 

In peusdo code:

for each account record
   for each child object
      select count()from child object where parent = account id
         if count != 0
            break;
      add account to list
   end for loop
end for loop

 Of course this would break the SOQL query limit, so i need to adapt to get it to work in batch form
irlrobinsirlrobins
raseshtcs wrote:

Create roll up summay fileds for each child on the account storing the sum of the childs present. Then query for the accounts where all the roll up summary fields are 0.

This would only work if all children were in a master detail relationship
Rahul SharmaRahul Sharma

Instead of using count() - aggregate query,

use a subquery, it would be better. since count() will count all the records and each count is equal to number of query row retrived. governor limit on number of row retrived by soql can be hit if there are huge records in your database.

 

In below example, it keep checking if it has each of child or not. if none of child are present, Process it (Make boolean field as true).

At a time batch can contain max 200 records, so collect the account ids in set and you can use pseude code like:

for( Account a : [Select Id, (Select Id from contacts limit 1), (Select Id from Opportunities limit 1), ... , from Account where id in :setAccountId])
{
if(a.contacts.isEmpty())
{}
else if(a.Opportunities.isEmpty())
{}
.......
else
{LstAccountwithNoChilds.add()}
}

Just a Suggestion,

Hope it helps.

This was selected as the best answer
irlrobinsirlrobins

I've created a Batch Apex class as follows:

global class DataCleanUtil implements 
    Database.Batchable<sObject>, Database.Stateful{

   global final String Query;
   global integer count;
  
   global DataCleanUtil(String q){Query=q;
     count = 0;
   }

   global Database.QueryLocator start(Database.BatchableContext BC){
      return Database.getQueryLocator(query);
   }
   
	global void execute(Database.BatchableContext BC,List<sObject> scope){
		for(sObject s : scope){
			Account a = (Account)s;
			if(!a.ActivityHistories.isEmpty()){
			}
         	else if(!a.Admin_Requests__r.isEmpty()){
         	}
			else if(!a.ActivityHistories.isEmpty()){
         	}
			else if(!a.Attachments.isEmpty()){
         	}
			else if(!a.Cases.isEmpty()){
			}
			else if(!a.Contacts.isEmpty()){
			}
			else if(!a.Events.isEmpty()){
			}
			else if(!a.NotesAndAttachments.isEmpty()){
			}
			else if(!a.BANs__r.isEmpty()){
			}
			else
				count=count+1;
		}
	}

	global void finish(Database.BatchableContext BC){
		AsyncApexJob a = [SELECT Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems, CreatedBy.Email
      		FROM AsyncApexJob WHERE Id = :BC.getJobId()];
   		// Send an email to the Apex job's submitter notifying of job completion. 
    
   		Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
		String[] toAddresses = new String[] {a.CreatedBy.Email};
		mail.setToAddresses(toAddresses);
		mail.setSubject('Apex Sharing Recalculation ' + a.Status);
		mail.setPlainTextBody('The batch Apex job processed ' + a.TotalJobItems +' batches with '+ a.NumberOfErrors + ' failures. Count was: '+count);
		Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
	}
}

 I call this class in my main apex class like this:

String Query = 'Select a.Id, (Select Id From ActivityHistories limit 1), (Select Id From Admin_Requests__r limit 1), '+
				'(Select Id From Attachments limit 1), (Select Id From Cases limit 1), (Select Id From Contacts limit 1), (Select Id From Events limit 1),'+ 
				'(Select Id From NotesAndAttachments limit 1), (Select Id From bans__r limit 1) from Account a';
			DataCleanUtil dcu=new DataCleanUtil(Query); 
			String jobid = Database.executeBatch(dcu);

 The batch job runs and emails me a result. However I did get some error emails just before this saying:

caused by: System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop

 

Class.DataCleanUtil.execute: line 18, column 8 External entry point

 

Can anyone suggest improvements in my code to to prevent this error occuring?
Rahul SharmaRahul Sharma

Hello irlrobins,

 

I didn't get the error exactly.

but i suggest to move the query in the batch, somewhat like this:

String Query = 'Select a.Id from Account a';
			DataCleanUtil dcu=new DataCleanUtil(Query); 
			String jobid = Database.executeBatch(dcu);

 

global class DataCleanUtil implements 
    Database.Batchable<sObject>, Database.Stateful{

   global final String Query;
   global integer count;
  
   global DataCleanUtil(String q){Query=q;
     count = 0;
   }

   global Database.QueryLocator start(Database.BatchableContext BC){
      return Database.getQueryLocator(query);
   }
   
	global void execute(Database.BatchableContext BC,List<sObject> scope){
		Set<Id> SetAccountId = new Set<Id>();
		for(sObject s : scope){
			Account a = (Account)s;
			SetAccountId.add(a.Id)
		}
		for(Account a : [Select a.Id, (Select Id From ActivityHistories limit 1), (Select Id From Admin_Requests__r limit 1), 
							(Select Id From Attachments limit 1), (Select Id From Cases limit 1), (Select Id From Contacts limit 1), (Select Id From Events limit 1),
							(Select Id From NotesAndAttachments limit 1), (Select Id From bans__r limit 1) from Account a where Id IN: SetAccountId]){
			if(!a.ActivityHistories.isEmpty()){
			}
         	else if(!a.Admin_Requests__r.isEmpty()){
         	}
			else if(!a.ActivityHistories.isEmpty()){
         	}
			else if(!a.Attachments.isEmpty()){
         	}
			else if(!a.Cases.isEmpty()){
			}
			else if(!a.Contacts.isEmpty()){
			}
			else if(!a.Events.isEmpty()){
			}
			else if(!a.NotesAndAttachments.isEmpty()){
			}
			else if(!a.BANs__r.isEmpty()){
			}
			else
				count=count+1;
		}
	}

	global void finish(Database.BatchableContext BC){
		AsyncApexJob a = [SELECT Id, Status, NumberOfErrors, JobItemsProcessed,TotalJobItems, CreatedBy.Email
      		FROM AsyncApexJob WHERE Id = :BC.getJobId()];
   		// Send an email to the Apex job's submitter notifying of job completion. 
    
   		Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
		String[] toAddresses = new String[] {a.CreatedBy.Email};
		mail.setToAddresses(toAddresses);
		mail.setSubject('Apex Sharing Recalculation ' + a.Status);
		mail.setPlainTextBody('The batch Apex job processed ' + a.TotalJobItems +' batches with '+ a.NumberOfErrors + ' failures. Count was: '+count);
		Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
	}
}

 Give it a try, may it help.
irlrobinsirlrobins

Ok bit of a boo boo on my part. When I first executed my batch job I realised that I had forgotten to limit my aggregate query to just one record. I immediately aborted the job and it was listed as aborted in the monitor. But in reality the job was still running and when it started processing I got some errors due to not limiting the results.

 

I corrected my query (adding Limit 1) and ran the job again. Because the first job was listed as aborted I presume the errors were from the second job. This is where I was wrong. The second job ran after the first completed and this time it worked without issue.

 

Many thanks for all the help guys, appreciate it.