I am just wondering if I could write a SOQL query for this:
Find list of
Lead
- That have an open
Task
assigned to specificUser
and - Where the
Lead.CreatedDate
equals theTask.CreatedDate
?
- That have an open
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this communityI am just wondering if I could write a SOQL query for this:
Find list of Lead
Task
assigned to specific User
andLead.CreatedDate
equals the Task.CreatedDate
?If you are willing to add some architecture you can enable this with a simple checkbox on Lead
called something along the lines of Has_Task_Created_Same_Day__c
. Then you would write a Task
trigger or maybe use Process Builder
to build a flow. That trigger or flow would just have to look at records created by your special User
with a WhatId
corresponds to a Lead
, and see if the Lead.CreatedDate
was today.
If you want it now, you can't accomplish what you want with pure SOQL
; you will need to do some post-processing. You can find all relevant Task
records and generate a Map<Id, Set<Date>>
corresponding to theCreatedDate
values (Date
part only). Once you have that you can compare with your Lead
records.
Here is some untested code to give you the gist:
static final User SPECIAL_USER; // lazy load someone via query
public static List<Lead> getSomeLeadSubset()
{
List<Lead> subset = new List<Lead>();
Map<Id, Set<Date>> taskIdToCreatedDates = getTaskCreatedDates(getLeadIdsWithTask());
for (Lead lead : [
SELECT CreatedDate FROM Lead WHERE Id IN :taskIdToCreatedDates.keySet()
]){
Set<Date> taskCreatedDates = new Set<Date>();
if (taskCreatedDates != null && taskCreatedDates.contains(lead.CreatedDate.date()))
subset.add(lead);
}
return subset;
}
public static Set<Id> getLeadIdsWithSpecialUserTask()
{
Set<Id> leadIds = new Set<Id>();
for (AggregateResult aggregate : [
SELECT WhatId FROM Task WHERE What.Type = 'Lead'
AND OwnerId = :SPECIAL_USER.Id GROUP BY WhatId
]){
leadIds.add(aggregate.get('WhatId'));
}
return leadIds;
}
public static Map<Id, Set<Date>> getTaskCreatedDates(Set<Id> leadIds)
{
Map<Id, Set<Date>> taskIdToCreatedDates = new Map<Id, Set<Date>>();
for (Task task : [SELECT WhatId, CreatedDate FROM Task WHERE WhatId IN :leadIds])
{
if (!taskIdToCreatedDates.containsKey(task.WhatId))
taskIdToCreatedDates.put(task.WhatId, new Set<Date>());
taskIdToCreatedDates.get(task.WhatId).add(task.CreatedDate.date());
}
return taskIdToCreatedDates;
}
CreatedDate
fields are actually of typeDatetime
and hence extremely unlikely to collide? – Adrian Larson♦ Feb 6 '16 at 1:05