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
Vishalg89Vishalg89 

Getting the list of those leads which have no open activity using soql

Hi,

 

I am having a situation in which I need to get the list of those leads which have no open activity associated.

Please provide a solution asap.

 

Thanks

Vishal Gupta

prakash_sfdcprakash_sfdc

Hi,
List<Task> temp = [SELECT ID,WhoId FROM Task WHERE Status!='Open' AND WhatId=NULL];
system.debug('**'+temp);

"WhoId" will contain the ID of the Lead. You can loop through the temp list and get the list of Leads which contain Activities which are not in Open State, Leads containing No activities won't be fetched.

Hope it helps.
Vishalg89Vishalg89

Thanks..

But here you hit a query on Task, but Events or Meeting Requests can also be the the Open Activity, so what we should do in that case?
sfdcfoxsfdcfox

Are you looking for all leads through the system, or some specific subset?

 

You could conceptually do this:

 

Set<Id> leadsWithNoActivity = Trigger.newMap.keySet().clone();
for(Task record:[SELECT Id,WhoId FROM Task WHERE IsClosed = FALSE AND WhoId IN :leadsWithNoActivity]) {
	leadsWithNoActivity.remove(record.WhoId);
}
for(Event record:[SELECT Id,WhoID FROM Event WHERE ActivityDate >= TODAY]) {
	leadsWithNoActivity.remove(record.WhoId);
}
for(EventRelation record:[SELECT Id,WhoID FROM EventRelation WHERE Event.ActivityDate >= TODAY]) {
	leadsWithNoActivity.remove(record.WhoId);
}

You will not be able to just query ALL leads, then find all those without events/tasks. The problem is that you cannot use anti-joins from activities (e.g. SELECT Id FROM Lead WHERE Id NOT IN (SELECT WhoID From Task...)).

 

As long as you have less than a few thousand leads in mind, you can query against them such as the example above. Note that there are also other queries that would satisfy this design.

 

However, if one made a trigger to update the lead with the task/event furthest out that is not closed, you could then easily query for this value:

 

SELECT Id FROM Lead WHERE LastFutureActivity__c < TODAY OR LastFutureActivity__c = NULL

 
Vishalg89Vishalg89

Hi,

what about this:

 

  

List<Lead> lstLeads = [SELECT Id, OwnerId, (SELECT ActivityDate,Description,Subject, status FROM OpenActivities)
FROM Lead ];

system.debug('::Total Leads::'+ lstLeads.size());
List<Lead> leads = new List<Lead>();
for(Lead l : lstLeads)
{
// check if there is any open activity is associated with lead
if(l.OpenActivities.size() > 0)
{
System.debug('::OpenActivities::'+l.OpenActivities);
}
else
{

System.debug('::Leads to be assigned::'+l);

//adding the changed lead to list
leads.add(l);
}
}

List<Lead> lstLeads = [SELECT Id, OwnerId, (SELECT ActivityDate,Description,Subject, status FROM OpenActivities)
FROM Lead ];

system.debug('::Total Leads::'+ lstLeads.size());

for(Lead l : lstLeads)
{
// check if there is any open activity is associated with lead
if(l.OpenActivities.size() > 0)
{
System.debug('::OpenActivities::'+l.OpenActivities);
}
else
{

System.debug('::Leads to be assigned::'+l);

//adding the changed lead to list
leads.add(l);
}
}

 

 
sfdcfoxsfdcfox
Except for the fact that you still need to limit your query, that code basically works. What I mean by limiting the query is that you cannot query the entire database of leads. You will get poor performance and/or your query will fail when there are too many leads. If you want to process the entire database, you will instead need to use a batch apex code class.
Vishalg89Vishalg89

thanks for the suggestion.. but I am aready using certain filters for the getting the leads.
Vishalg89Vishalg89

can you suggest, hw do I optimize this by using limit or batch apex?
sfdcfoxsfdcfox
So long as you're filtering the leads you're looking for, then there's no problem with your approach.