Populating a Developer Sandbox can be a painstaking task, but using a data loader along with external ids can help simplify the process and save you a little time. The first thing you will need to decide is what data you will need to work with in your Sandbox. Since the amount of storage is far less in this type of Sandbox, you will most likely not be able to copy everything over. In the following example, we will use Jitterbit to move over Accounts and their Contacts that have open Opportunities. You may need to further filter your criteria if you have a lot of open Opportunities but this example should at least get you started.
The first thing we’ll do is create all of the queries, let’s start with the Account object.
- Open Jitterbit Data Loader
- Double click on New Query
- Select your Production Login and click Next
- Select the Account object and click Next
- Check all of the fields you would like to copy over
- Manually enter the WHERE clause to only include Accounts that have Ids IN the Opportunity Subquery and click Next
Java1SELECT Id, Name FROM Account WHERE Id IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
- Enter the Name of the file e.g. AccountExtract, leaving the remaining defaults and click Next
- Leave the defaults and click Next again
- Click Finish then click on Run Query
Most of the steps for selecting the Contacts will be the same with a few differences.
- Repeat steps 1 – 3
- In Step 4, Select the Contact object, click Next and Select the desired fields
- Manually enter the WHERE clause to only include Contacts that are related to Accounts that have Ids IN the Opportunity Subquery and click Next
Java1SELECT Id, AccountId, Name FROM Contact WHERE AccountId IN (SELECT AccountId FROM Opportunity WHERE IsClosed = false)
- Repeat steps 7 – 9, with using an appropriate name for the file, e.g. ContactExtract
Once again the steps will be very similar for selecting the Opportunities:
- Repeat steps 1 – 3
- In Step 4, Select the Opportunity object, click Next and Select the desired fields
- Since we are working directly on the Opportunity object you will not need to use a semi-join and can have the WHERE clause just include Opportunities that are not closed
Java1SELECT Id, AccountId, Name FROM Opportunity WHERE IsClosed = false
- Repeat steps 7 – 9, with using an appropriate name for the file, e.g. OpportunityExtract
Now that we have all of the data in hand, we’ll need to add some external ids to our objects in the Sandbox. Since Summer ’14 has increased the number of allowed external ids per object, it may further simplify things to have these fields already in the Production org but for some that can be confusing so I typically just add them after a Sandbox refresh.
- Login to your Developer Sandbox
- Go to Setup -> Customize -> Accounts -> Fields
- Click on New Field
- Select Text then Next
- Enter ProductionId for the field label
- Enter 18 for the length
- Check Unique and External Id, leave all remaining defaults and click Next
- Select the desired field-level security options and click Next
- Select the desired layouts to add the field to and click Save
Repeat the above steps for both the Contact and Opportunity objects. Once all of the external ids have been added we can begin the import process.
- Return to Jitterbit
- Double click on New Upsert
- IMPORTANT – make sure you select your Sandbox Login and click Next
- Select the Account object from Step 1 and the newly created ProductionId from Step 2 and click Next
- Browse for the AccountExtract file created above and click Next
- A popup with a subset of the data will open, click Continue
- Leave the defaults and click Next
- Leave the defaults again and click Map & Finish
- Click Auto Map to align all matching fields
- Double click the Id on the right hand side and delete the Id between the <trans> tags then click OK
- Repeat step 10 for all fields that are lookups to other objects (User lookups are ok)
- Drag the Id from the left hand side and drag it over to the ProductionId on the right hand side
- Click Finish then click Run Upsert
The steps will be repeated again for both the Contact and Opportunity object with the following three additional steps.
- Once you have reached step 10, you will want to make sure to clear the mapping for the AccountId
- Click the plus sign to expand the Account folder on the top right hand side to reveal the ProductionId
- Drag the AccountId from the left up to the ProductionId field under the Account folder
Now, whenever we need to update the data from Production to Sandbox, we can simply extract the updated data, keep the fields and file names the same as our original extract and just re-run the saved Upserts.