Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Patrick ConnerPatrick Conner 

Batch Class or Trigger to Delete all but most the recent task

Hey,

I'm trying to figure out how to create a trigger to delete all but the most recent task. Is it possible to order and remove all but most recent?

My actual use-case is this:

For tasks with subject "AAA" on Account "B1", If any task exists with created date < 30 days, purge all tasks with created date >/_ 30 days.
For tasks with subject "AAA" on Account "B1", If no task exists with created date < 30  days, keep most recent task, purge all others.

I'd do the same for tasks with subject not equal to "AAA". Meaning if no task exists with created date <30 days, an account may show the most recent task with subject "AAA" and most recent task with subject "BBB", with older tasks of both subjects purged.

I'm having trouble finding much on this niche use-case, and I'm unsure if it's possible to order tasks by ID or creation date and delete all but the most recent from there.

Thank you very much for your help.

Patrick
Hemant_SoniHemant_Soni
Hi patric,
In your trigger filter task With this query which is give you recent task.And for date condition you can user date litrals of salesforce.
Date litrals:- https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm
Filter Query - select id,subject,createdDate  from task order by createdDate DESC
If you need more help or Support in salesforce you can contact me on my email id  "soni.sonihemant.hemant@gmail.com".
Thanks
Hemant
Patrick ConnerPatrick Conner
Thanks for your reply Hemant! 

I know how to create a class to delete tasks after a specific time (according to a date litral like createdDate not equal to last 30 days).

However, I'm not sure how to delete tasks after a specific time ONLY if tasks exist within that specified time frame.

Further, I don't know how to delete all but the most recent task, if NO tasks exist in a specific time frame.

I noticed you mentioned the need to order by createdDate DESC, so it seems like it may be possible to delete all but the first of the resulting DESC list? 

Any additional thoughts would be greatly appreciated. Thank you again!

Patrick
Hemant_SoniHemant_Soni
Hi Patrick Conner,
Correct me if i am wrong
What i under stand with your requirement you need to delete Task after a specific time.
1.If task exist : for example : if created date of task is grater then LAST_N_DAYS:30 and and less then TODAY
2.If Task not Exist : if created date of Task is grater then LAST_N_DAYS:30
Thanks
Hemant
Patrick ConnerPatrick Conner
Thanks again for your help Hemant, I'm sorry if I'm not explaining clearly. I think your outline is close. I need the following:

Delete all tasks after 30 days
1. *If task exists where createdDate equals LAST_N_DAYS:30*

Delete all tasks after 30 days EXCEPT most recent task
2. *If no task exists where createdDate equals LAST_N_DAYS:30*

I hope this helps.

Best,

Patrick
Suraj PSuraj P
Task[] tasksWithin30Days = [Select Id from task where createddate = Last_n_days:30];
if(tasksWithin30Days.size()==0){
Id mostRecentTaskId = [Select Id from Task order by createddate desc limit 1].Id;
delete [Select Id from Task where Id != :mostRecentTaskId];
}else{
delete [Select Id from Task where Id not in :tasksWithin30Days];
}

 
Patrick ConnerPatrick Conner
Thanks guys!

Suraj, thanks so much! Your code makes perfect sense logically and I feel we are very close. I need whatID to be taken into account however, so that the most recent task kept is the most recent task assigned to each whatID (not the most recent task out of all tasks). Is this possible? 

Thank you again.

Patrick
Suraj PSuraj P
Add a custom field on Task that concatenates WhatId and Createddate. Let's say you call it WhatId_createddate__c. Use the following code. I haven't really tested it, so please do test it out in the sandbox. This may not work a 100% of the time, if there are Tasks that have the exact Createddate, so it is possible that you may see 2 Tasks that have the same timestamp not being deleted, theoretically. But the chances for that are less. Also, I am not sure if there would be any impact of timezone differences in typecasting string representation of datetime to datetime as I have done below. Please test it out, specifically.
  
AggregateResult[] tasksWithin30Days = [Select whatId from task where createddate = Last_n_days:30 group by whatid];
AggregateResult[] noTask30Days = [Select whatId,max(createddate) maxdate from task group by  whatid having max(createddate) < Last_n_days:30 ];

Id[] hasTaskWithin30Days = new Id[]{};
Id[] noTasksIn30Days = new Id[]{};
String[] whatIdDateTimeList = new String[]{};

for(AggregateResult ar: tasksWithin30Days ){
hasTaskWithin30Days.add((Id)ar.get('whatId'));
}
for(AggregateResult ar: noTask30Days){
noTasksIn30Days.add((Id)ar.get('whatId'));
whatIdDateTimeList.add(''+ar.get('whatId')+ar.get('maxdate'));
}

delete [ Select Id from Task where whatId in :hasTaskWithin30Days and createddate< last_n_days:30];
delete [Select Id from Task where whatId in :noTasksin30Days and whatId_createddate__c not in :whatIdDateTimeList];

 
Patrick ConnerPatrick Conner
Hey Suraj,

I'm still having trouble. I created an activity custom formula field for WhatId_createddate__c with the following value: CASESAFEID(WhatId)& SUBSTITUTE(SUBSTITUTE(TEXT(CreatedDate)," ","T"),"Z",".000+0000"). This matches what I see when I use developer console for query testing (WhatId ex. 0011200001HaO4GAAV, max(createddate) ex. 2016-09-07T16:53:53.000+0000)

However, It looks like the script still deletes all tasks older than 30 days. (Doesn't even keep most recent out of ALL tasks). Is there something wrong with my WhatId_createddate__c matching to whatIdDateTimeList? I got the same result with CASESAFEID as without, so makes me think it might be the issue?

Again, I can't thank you enough for your help.

Patrick
Patrick ConnerPatrick Conner
Sorry I haven't replied in some time, I have only a limited amount of time in my day to work on this particular task. I ran debug in the class to give me the values in whatIdDateTimeList. Those values are formatted as: 0011200001D1oqhAAB2016-09-15 15:01:27. I modified WhatId_createddate formula field to CASESAFEID(WhatId) & (LEFT(TEXT(CreatedDate),LEN(TEXT(CreatedDate))-1)), re-ran and it looks to have worked. I have a little bit more testing to do when I have some time, but I think this might work. Assuming so, I'll mark your last answer as Best Answer Suraj, thank you again.
Suraj PSuraj P
Hi Patrick,
Sorry, I have been tied up and couldn't reply. Did the proposed solution work for you? If so, I'd appreciate if you could mark my answer as "Best Answer". Thanks.
Patrick ConnerPatrick Conner
Hey Suraj. Thanks again so much for your help on this. The code does work, however I get an error due to returning too many results: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch.

Is there some way around this? Using database.stateful or looping through list sets or something? Of course I'll mark your answer as Best Answer. Thank you again.
Suraj PSuraj P
Maybe try this: 
Id[] hasTaskWithin30Days = new Id[]{};
Id[] noTasksIn30Days = new Id[]{};
String[] whatIdDateTimeList = new String[]{};

for(AggregateResult ar: [Select whatId from task where createddate = Last_n_days:30 group by whatid]){
hasTaskWithin30Days.add((Id)ar.get('whatId'));
}
for(AggregateResult ar: [Select whatId,max(createddate) maxdate from task group by whatid having max(createddate) < Last_n_days:30 ]){
noTasksIn30Days.add((Id)ar.get('whatId'));
whatIdDateTimeList.add(''+ar.get('whatId')+ar.get('maxdate'));
}

delete [ Select Id from Task where whatId in :hasTaskWithin30Days and createddate< last_n_days:30];
delete [Select Id from Task where whatId in :noTasksin30Days and whatId_createddate__c not in :whatIdDateTimeList];
This should take care of the "queryMore" issue. However, if you still encounter limit issues, Batch class may be the way to go.