Update records in Salesforce in mass

Recently I was tasked with a side project to clean up some of our data in Salesforce. The first major task I had was to update thousands of Opportunity records. Given that Salesforce will only allow you to update 100 records at a time I needed to find a way around this. Here is a neat trick to make quick automated work of this problem.

For this example I need to copy the data from a field called Invoice to a field called Accounting. I only want to grab Opportunities where there is data in the Invoice field and I only want to update the Accounting field if there is nothing in it yet. Basically I do not want to clobber existing data.

My example will use APEX code and insert and update triggers to repeat the query over and over.

Step 1:
Create a custom object in SF called updateData (this can be called ANYTHING you want). This object should have 1 number (integer) field called “number”. Technically this is not needed, but my code uses this so I can see how many records were updated. Once you have this created, map it to a Tab so you can find it.

Step 2:
Create an APEX trigger. Yes you will need to use APEX code for this and that means you will need to use the Force IDE editor which is basically just a hacked version of Eclipse. You can NOT create, edit, or do anything with APEX without it. Your trigger should look like this:

trigger updateDate on updateData__c (before insert, before update) {

  if (Trigger.isBefore && (Trigger.isInsert || Trigger.isUpdate)) {
    updateData__c[] udata = Trigger.new;

    // Lets grab all of the opportunities that meet our requirements
    // Since we can only update a 100 at a time lets limit our results
    // once again, I only want records where the Invoice field has 
    // something in it and the Accounting field is empty.
    Opportunity[] allOpps = [
      select Id, Name, Accounting__c, Invoice__c
      from Opportunity 
      where Invoice__c <> '' and Accounting__c = ''
      limit 100
    ];

    integer count=0;
    for (Opportunity o:allOpps) {
      // Lets first check to make sure the field is empty, 
      // we do not want to clobber any data.
      if (o.Accounting__c == NULL) {
        o.Accounting__c = Invoice__c;
        update o;
        count++;
      }  
    }
    // This just updates the number field with the 
    // number of records that we actually updated
    for (updateData__c d:udata) {
      d.number__c = count;
    }
  }
}

Step 3:
You will need to create a unit test for this. This unit test just needs to create a single updateData record. So it will look like:

@isTest
private class Test_updateData {

  static testMethod void myUnitTest() {
    updateData__c uData = new updateData__c();
    uData.number__c = 1;
    insert uData;
  }
}

Step 4
At this point you can create a new updateData record and press save. It will update 100 Opportunities. If you edit the record and press save, it will update another 100 records. Now if you are like me and you have thousands of records to do, you need to make use of some web browser automation.

Step 5:
Install the iMacros plugin for Firefox.

Step 6:
Record yourself, with iMacros, editing the record that you created in Step 4. When you are done, you can stop the recording and edit the actual script. It should look something like this (I added the wait seconds=5 at the end to just add an extra delay):

VERSION BUILD=7110203 RECORDER=FX
TAB T=1
URL GOTO=https://xxxxxxx.salesforce.com/yyyyyyyyyyyyyyyyyyy
TAG POS=1 TYPE=INPUT:BUTTON ATTR=NAME:edit&&VALUE:Edit
TAG POS=1 TYPE=INPUT:TEXT FORM=NAME:editPage ATTR=ID:zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz CONTENT=
TAG POS=2 TYPE=INPUT:SUBMIT FORM=ID:editPage ATTR=NAME:save&&VALUE:Save
WAIT SECONDS=5

Step 7:
Now set iMacros to play as a loop for as many times as you need and sit back and watch all of your data get updated. NOTE: you can always use the “sForce Explorer” (a Saleforce QUI SQL tool) to query your data and see how many records you will be needing to touch.