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
Sajjan T SSajjan T S 

Query Cases created before 18 months with related files (not attachments)

I am trying to write a batch apex to schedule and delete cases that were created before 18 months along with the related files. Can someone help me with formulating the query, please?
Best Answer chosen by Sajjan T S
Sajjan T SSajjan T S
The working batch class code below:

Global class OldCaseDeleter implements Database.Batchable<SObject> {
    Public List<SObject> allRecords;
   Global Database.QueryLocator start(Database.BatchableContext BC) {
       String query = 'SELECT Id FROM Case WHERE CreatedDate = today and Exempt_From_Deletion__c = false';
       System.debug('query -> ' + query);
       return Database.getQueryLocator(query);
   }
   Global void execute(Database.BatchableContext BC, List<SObject> records) {
       List<Case> caseList = (List<Case>)records;
       Set<Id> caseID = new Set<Id>();
       for(Case c: caseList) {
           caseId.add(c.Id);
       }
       List<ContentDocumentLink> files = [SELECT ContentDocumentId, LinkedEntityId
                                          From ContentDocumentLink
                                          WHERE LinkedEntityId IN :caseId];
       Set<Id> conId = new Set<Id>();
       for(ContentDocumentLink cd : files) {
           conId.add(cd.ContentDocumentId);
       }
       List<ContentDocument> files1 = [SELECT Id, Title
                               FROM ContentDocument
                               WHERE Id IN : conId];
           allRecords = new List<SObject>();
        allrecords.addAll((List<SObject>)files1);
        allrecords.addAll((List<Case>)caseList);
        Delete allrecords;
   }
   Global void finish(Database.BatchableContext BC) {
   }
}

 

All Answers

Khan AnasKhan Anas (Salesforce Developers) 
Hi Sajjan,

Greetings to you!

You can use 'SELECT Id,CreatedDate FROM Case WHERE CreatedDate < Last_N_Months:18' query which will Returns Id's for all Case records created BEFORE the last 18 months

Please try the below code, I have tested in my org and it is working fine. Kindly modify the code as per your requirement.

Batch Apex: 
global class OldDataDeleter implements Database.Batchable<sObject> {

    global Database.QueryLocator start(Database.BatchableContext BC) {
        String query = 'SELECT Id,CreatedDate FROM Case WHERE CreatedDate < Last_N_Months:18';
        System.debug('query -> ' + query);
        return Database.getQueryLocator(query);
    }

    global void execute(Database.BatchableContext BC, List<Case> records) {
        DELETE records;
    }

    global void finish(Database.BatchableContext BC){
    }
}

Now, We have the batch class ready and it has to be in a schedulable context in-order to schedule the batch

Scheduled Apex: 
global class scheduledBatchable implements Schedulable{

     global void execute(SchedulableContext sc) {
          // Implement any logic to be scheduled

          // We now call the batch class to be scheduled
          OldDataDeleter b = new OldDataDeleter(); 
          
          //Parameters of ExecuteBatch(context,BatchSize)
          database.executebatch(b,100);
     }
}

Finally, schedule the batch class by executing anonymous code from either developer console or apex, the minimum is 1 hour:

// Cron EXP for hourly schedule:  
String CRON_EXP = '0 0 * * * ?'; 
SheduledBatchable sch = new scheduledBatchable(); system.schedule('Hourly Example Batch Schedule job', CRON_EXP, sch);

I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future. It will help to keep this community clean.

Thanks and Regards,
Khan Anas
Sajjan T SSajjan T S
Hi Khan,

Thanks for your response.

The issue with this query (SELECT Id,CreatedDate FROM Case WHERE CreatedDate < Last_N_Months:18') is that I would be able to get only Cases and not related Files. In the case of Attachments, it will be deleted if the related Case is deleted. However, with Files, it would still remain even when the corresponding Case is deleted. So, I need a query to SELECT all the cases along with the related files and delete them altogether. 

Hope I'm making sense.

Cheers,
Sajjan
Khan AnasKhan Anas (Salesforce Developers) 
Hi Sajjan,

You can fetch the files related to Cases using ContentDocumentLink by filtering through LinkedEntityId, where LinkedEntityId is Case Id. Then delete Cases and Files together.

Try like this: 
global class OldDataDeleter implements Database.Batchable<sObject> {
    
    public List<SObject> allRecords;
    global Database.QueryLocator start(Database.BatchableContext BC) {
        String query = 'SELECT Id FROM Case WHERE CreatedDate < Last_N_Months:18';
        System.debug('query -> ' + query);
        return Database.getQueryLocator(query);
    }
    
    global void execute(Database.BatchableContext BC, List<sObject> records) {
        List<Case> caseList = (List<Case>)records;
        Set<Id> caseId = new Set<Id>();
        for(Case c: caseList){
            caseId.add(c.Id);
        }
        
        List<ContentDocumentLink> files = [SELECT ContentDocumentId, LinkedEntityId  
                                           FROM ContentDocumentLink 
                                           WHERE LinkedEntityId IN :caseId];  
        
        allRecords = new List<SObject>();
        records.addAll((List<SObject>)records);
        records.addAll((List<SObject>)files);
        
        DELETE allRecords;
    }
    
    global void finish(Database.BatchableContext BC){
    }
}

Regards,
Khan Anas
Sajjan T SSajjan T S
Hi Khan,

This helps.

Thank you so much :-)

Warm Regards,
Sajjan
Sajjan T SSajjan T S
Hi Khan,

I am getting this error - 15:31:20:273 USER_DEBUG [16]|DEBUG|Delete failed. First exception on row 1 with id 06A6F00000HpdHMUAZ; first error: ENTITY_IS_DELETED, entity is deleted: []

The problem is when I run this query  - [SELECT ContentDocumentId, LinkedEntityId FROM ContentDocumentLink WHERE LinkedEntityId IN :caseId], this is returned -{"ContentDocumentId":"0696F00000EBQkyQAH","LinkedEntityId":"5006F00002HJAMhQAP","Id":"06A6F00000HpdHMUAZ"}

But the case and the related files aren't getting deleted.   

Can you please help me with this?

Warm Regards,
Sajjan
Khan AnasKhan Anas (Salesforce Developers) 
You can use Database.delete(recordToDelete, allOrNone). 
allOrNone-> If you specify false for this parameter and a record fails, the remainder of the DML operation can still succeed. This method returns a result object that can be used to verify which records succeeded, which failed, and why.

Also, use FOR UPDATE clause in SOQL query: https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/langCon_apex_locking_statements.htm

However, I suggest you to use Iterable in a batch to delete the records. Please refer to the below links which might help you.

https://salesforce.stackexchange.com/questions/81284/delete-records-from-multiple-objects-via-apex-batch-class
Sajjan T SSajjan T S
The working batch class code below:

Global class OldCaseDeleter implements Database.Batchable<SObject> {
    Public List<SObject> allRecords;
   Global Database.QueryLocator start(Database.BatchableContext BC) {
       String query = 'SELECT Id FROM Case WHERE CreatedDate = today and Exempt_From_Deletion__c = false';
       System.debug('query -> ' + query);
       return Database.getQueryLocator(query);
   }
   Global void execute(Database.BatchableContext BC, List<SObject> records) {
       List<Case> caseList = (List<Case>)records;
       Set<Id> caseID = new Set<Id>();
       for(Case c: caseList) {
           caseId.add(c.Id);
       }
       List<ContentDocumentLink> files = [SELECT ContentDocumentId, LinkedEntityId
                                          From ContentDocumentLink
                                          WHERE LinkedEntityId IN :caseId];
       Set<Id> conId = new Set<Id>();
       for(ContentDocumentLink cd : files) {
           conId.add(cd.ContentDocumentId);
       }
       List<ContentDocument> files1 = [SELECT Id, Title
                               FROM ContentDocument
                               WHERE Id IN : conId];
           allRecords = new List<SObject>();
        allrecords.addAll((List<SObject>)files1);
        allrecords.addAll((List<Case>)caseList);
        Delete allrecords;
   }
   Global void finish(Database.BatchableContext BC) {
   }
}

 
This was selected as the best answer