Problem

Recently I needed to migrate opportunities from our legacy system into Salesforce.  Being no stranger to the data loader, this is normally not a problem.  The tricky part was that the opportunity needed to be linked to an external ID field on the account instead of the account ID, which cannot be accomplished with the data loader.

Solution

1. Run a report that contains the external id field (Enrollment Owner ID) and Account ID (filtering out where enrollment IDs are not present).

Export the report.

2.  Load my Salesforce Find and Replace Template (Excel 2007 required – may work w/2003 & compatibility pack).

Paste in the results of the above report into the left two columns, starting in the yellow section (in this case, the order of the fields needed to be reversed).

Then paste in the External IDs that you are looking to match in the clear column on the right.  Select the Salesforce ID formula (E5) and double click the bottom right plus sign to extend the formula throughout the entire worksheet.

Column E will now output the corresponding ID from column B, when fields D and A match.  The matching algorithm will only find one match, so it is best to have a duplicate free list of external IDs.

You may need to convert the fields to numeric or text values depending on your external ID.  For this function to work correctly for me, I had to convert these to numbers:

3.  Take the matched Salesforce IDs paste them into a new column in your import file.

A quick paste into notepad, select all, cut, and into the worksheet it goes (to remove excel formatting), nicely matched.

Credit to Mr Excel for the formula magic.

Update

Before I got the chance to finish this post, I needed to have this template match a data field as well.  I used this additional field and a formula to add a picklist selection to a multipicklist field with existing data (without this formula, the data loader would override the existing selections).

In case you’re curious, the formula in H4 is =CONCATENATE(G4,"; Weekly Mailing").

Click here to download the template.

Disclaimer: use at your own risk.  Be very careful playing with the data loader.  Spot check your data and make sure to do a test run of a few records before proceeding with a large data set.

Describe the function of an External ID

• Each object can have a maximum of 3 external ID fields.
• The external ID can be a number, text, or email field.
• An external ID can be used to prevent duplicate entries when importing data.
• An external ID makes it much easier to update data in Salesforce from an external system.
• Additionally external fields are searched from the side bar.  Here is a good link in the boards for more info.
• See also “Custom Field Attributes”.

List and describe the different tools and use cases for data migration

See “Accessing the Import Wizards” & “Importing Overview”.

Personal Edition can import contacts and accounts.  Pro+ can import custom objects, leads, solutions, etc.

There are several different tools for data migration:

1. Data import wizards- a) user can import their own contacts and accounts, b) system administrator can use wizards to import data for entire company
2. Data loader – wide variety of uses, requires use of salesforce id for each record
3. Outlook/Lotus Notes sync – users can manually associate their contacts to an existing salesforce.com org
4. Microsoft Office connector – excel can be used to upload data.  Should not be referenced on the test, but something you should know exists.

1. Import My Accounts & Contacts is limited to records that you own, and is limited to a maximum of 500 records.  This wizard is designed specifically for users to upload their own records.  Can accept records exported from Outlook, Act, or any other CSV file.
2. The various import wizards under administrative setup require administrative privileges and can import up to 50,000 records.  These wizards can be used to update data without the record ID.  Matching type depends on the object- for instance, contact can be matched via email address for full name.
3. The data load is the least user-friendly of the group.  It requires the salesforce record ID for each transaction (excluding additions).

Update existing data via import

Perform an import using the data import wizard.  When prompted put check the box to overwrite account info.  This will actually overwrite contact and account information.  The contact is matched based upon the initial criteria specified (full name, sf id, or email).

This is how contacts and accounts manage updates using data import.  The other objects prompt at the beginning of the wizard to insert or update:

Define when to use the Data Loader

See “When to use the Data Loader”.  The key limitations of web-based importing are a) max of 50k records, b) not all objects are supported, and c) no scheduling.  Mass deletion of custom objects for instance is not supported.  Mass transfer applies to accounts, leads, and custom objects.

List the functions of the Data Loader

• Insert
• Update
• Upsert (update, if the record does not exist, then insert)
• Delete
• Export

Mass Update Records

Many mass updates can be performed through the web interface- transfer accounts, delete records (standard objects), reassign teams, and update addresses for instance.  Data import wizards also have an option to update existing information as discussed above.  Those options not available on through web tools must be performed through the data loader.  This requires matching the Salesforce ID of the record and appending the data to be updated to a CSV and then running the update.

Mass Delete Records

Most record types can be mass deleted through the web interface.  Custom objects cannot be mass deleted through the web interface; the data loader must be used.  To do so, run a report with the record ID listed.  Export that to CSV and load that into the data loader to perform the deletion.

Describe the capabilities of the Recycle Bin

The recycle bin holds records for 30 days before they are automatically deleted permanently.  You can undelete a record and all of the associated records will accompany (if you undelete a contact, all of the activities will get undeleted as well.  Recycle bin storage (in records) = Storage size in MB x 250.  (e.g. 1gig storage = 250,000 records max recycle bin).

Request and download a weekly export

Enterprise and Unlimited only.  Cost @ Professional.

Setup –> Data Management –> Data Export.  Schedule.  Every week you will get a zip file containing your entire company’s Salesforce data.

Explain how Salesforce allocates storage

Storage is split into two sections:

Data Storage: the data stored in each record- activities, accounts, contacts etc.

File Storage:  file attachments, documents