To update records in Salesforce in bulk, you will first need to pull them to your Excel spreadsheet. You can do it using either SOQL or Reports. The main showstoppers when updating Salesforce records are validation rules or field constraints (e.g., allowable numbers) that can prevent you from updating. These are usually pretty easy to figure out since Salesforce gives you clear error messages in case something goes wrong. However, if you have a lot of triggers in your system, you may need to go hunting in other objects to find the validation rule that’s preventing your record update. The other thing to watch out for are data values that don’t conform to existing picklist values in SFDC: these and other constraints are set in each of the fields’ definition page in SFDC’s setup area.
In our example here we’ll use XL-Connector to update the stage of all opportunities under one specific account. We’ll use the following query to pull the data:
SELECT Name,Id, StageName FROM Opportunity WHERE Account.Name = ‘AVL GmbH’
Please note that we’re pulling record IDs. A column of record IDs is required to be able to update records in Salesforce.com.
We got the following results back from our sample database:
Now we want to change the stage of all four Opportunities to Closed Won. After the stage has been changed we’ll select the rows of data so that the selection spans across all the records that we want to update:
Then we’ll click Update up in the XL-Connector ribbon.
Because we pulled our data using XL-Connector’s Get Data tool, the system will automatically map the columns so that we don’t have to worry about it. If you used Reports to pull your data, you might need to map your Excel columns to fields in Salesforce.com manually by dragging fields from the top table and dropping them on the corresponding Excel columns in the bottom table.
Now all we need to do is click OK in the Mapping dialog and all our Opportunity records will get updated almost instantly. Note that if you have a ton of records to update, errors slow the process down. There is a tradeoff between speed and error-handling, though: the larger the batch size you select in XL-Connector, the more records will be marked as failing for each error encountered.