2

We have a problem in our org whereby we're using 122% of our Salesforce File storage, looking at what we have stored and where, almost all of that is within the Attachment object.

Record Type - Record Count - Storage - Percent

Attachments - 14,453,216 - 1.1 TB - 100%

I have a requirement to bring that percentage down and one way we can do that is to remove all unnecessary word documents. We have a parcer that, when we upload attachments (specifically Resumes), the document gets the name Resume_ appended to it and a .pdf is also created which is then used by a Visualforce page embedded within the Contact page layout, making the Word document vesion redundant.

I figured we could create a lot of space by first removing all word documents that start with Resume_ (we might have other documents that we shouldn't delete, this is why I need to specifically look for this). I've written the script below that should do just that:

List<Id> contactIds = new List<Id>();
List<Attachment> attachmentList = new List<Attachment>();
List<Attachment> toDelete = new List<Attachment>();

for (Contact c : [SELECT Id FROM Contact WHERE Id = '0032500000M1uXO' LIMIT 200]) {
  contactIds.add(c.Id);
}

System.debug('List of Contact Ids: ' + contactIds);

for (Attachment a : [SELECT Id, Name, CreatedDate, ContentType FROM Attachment WHERE ParentId IN :contactIds And ContentType = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' ORDER BY CreatedDate DESC]) {
  attachmentList.add(a);
}

System.debug('List of Attachments: ' + attachmentList);

for (Attachment a : attachmentList) {
  if (a.Name.startsWithIgnoreCase('Resume')) {
    toDelete.add(a);
  }
}

System.debug('These will be deleted:' + toDelete);

//DELETE toDelete

In a nutshell, what I'm doing here is:

  • Looping through and getting a list of Contact Ids
  • Looping through that list of Contact Ids to get a list of Attachments
  • Looping through that list of attachments, checking if they start with Resume and adding them to a toDelete list
  • That list will then get deleted (commented out, for now)

Based off of the test contact I made and the resume uploaded, the following appears to be the ContentType for Word documents:

application/vnd.openxmlformats-officedocument.wordprocessingml.document

This seems to retrieve what I need to delete based off of the System.debug() I'm printing, but I do have a couple of questions in what I'm doing here:

  1. Is the code good? I'm slightly unfamiliar with bulkification. I feel like I've done this in a good way, but not the best way.
  2. We have about 2,100,000+ Contact records, is there anything that should concern me with this? Performance issues for example?
  3. Because we have such a large quantity of Contacts and even more attachments, what would be the best way of running this script? As a batch, perhaps?
  4. Will the content type: application/vnd.openxmlformats-officedocument.wordprocessingml.document remove both .doc and .docx files, or is there a better way of getting these?
  5. If I do run this as a batch class, won't it re-query the same Contact records?

Advice here would be greatly appreciated!

  • Your code seems to be fine. The only thing is you need to go with Batch as you have large no. Of records. – Himanshu Jan 4 '16 at 12:27
  • @Himanshu It's just occurred to me, if I do run this as a batch class, won't it re-query the same Contact records? I've updated my question to include this. – Dan Jones Jan 4 '16 at 12:52
  • why it will do? why you have hard coded the id here when you want to process all contact reocrds ? "SELECT Id FROM Contact WHERE Id = '0032500000M1uXO' LIMIT 200" – Himanshu Jan 4 '16 at 13:16
  • @Himanshu Sorry, I should have explained that I'm testing this within a Sandbox and this is just a contact I'm using to test the script. I'm not entirely sure how Batch classes work so I need to do some research into them, but don't they just rerun and if they do, will they not re-query the same records when they run again? – Dan Jones Jan 4 '16 at 13:19
  • 1
    ok I understood. no it will not query same record again. Batch consist 3 method, 1. Start (It query all records defined as criteria. in your case it will be "select id from contact") 2. execute (it process all records queried using Start method in batch mode, 200 records at a time.) 3. Finish (used if any post processing required after whole batch complete successfully such as email of batch completion etc.) makes sense ? – Himanshu Jan 4 '16 at 13:22
3

You can try it in batch

global class ExampleBatchClass implements Database.Batchable<sObject>{

    global ExampleBatchClass(){
        // Batch Constructor
    }

    // Start Method
    global Database.QueryLocator start(Database.BatchableContext BC){
        return Database.getQueryLocator('SELECT ID FROM Contact);
    }

    // Execute Logic
    global void execute(Database.BatchableContext BC, List<contact>scope){
        // Logic to be Executed batch wise 
        for (Attachment a : [SELECT Id, Name, CreatedDate, ContentType FROM Attachment WHERE ParentId IN :scope And ContentType = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' ORDER BY CreatedDate DESC]) {
            attachmentList.add(a);
        }

        System.debug('List of Attachments: ' + attachmentList);

        for (Attachment a : attachmentList) {
            if (a.Name.startsWithIgnoreCase('Resume')) {
                toDelete.add(a);
            }
        }

        System.debug('These will be deleted:' + toDelete);

        //DELETE toDelete
    }

    global void finish(Database.BatchableContext BC){
        // Logic to be Executed at finish
        // may be an email
    }
}

Now execute your batch as

ExampleBatchClass b = new ExampleBatchClass(); 
//Parameters of ExecuteBatch(context,BatchSize)
database.executebatch(b,1);

It will process one record of contact at a time. Note: Also in your code you are giving id and limit 200 don't understand the reason.

for (Contact c : [SELECT Id FROM Contact WHERE Id = '0032500000M1uXO' LIMIT 200]) {
    contactIds.add(c.Id);
}

And for your concern batch query record only one time so it will not call same record again. Hope this code help you or let us know if you have any other doubts.

  • 1
    Thank you Tushar. I should have explained that that initial query was for testing, specifically with that Id. I also am a bit unfamiliar with Batch classes. I assumed they ran in batches of a certain amount. In this instance, I went with 200 based on nothing. :P I'll give this a go! Much appreciated. – Dan Jones Jan 4 '16 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.