+ Start a Discussion
Peter KayePeter Kaye 

Set up Sandbox - Problem with Related Item Id's

The days when you got a full copy of the production database as a sandbox without paying seem to have gone.

I am using the Developer Pro Sandbox in an Enterprise Edition.  When created it seems to contain all the custom objects from production but without any data.  I can import a sub-set of data using Data Loader but some of the objects have look-ups to other objects.  Since you cannot import Id values how can I import data with related ID's without editing the import file - i.e replacing the old ID values with those which apply in the Sandbox ?

I hope I am missing something really obvious as editing data which is supposed to be represetnative of live data does not seem to me a good idea - quite apart from the time involved.  Thanks.
 
Best Answer chosen by Peter Kaye
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Peter Kaye,

As UC Innovation mentioned that is not a easy way to achieve what you are looking for. There are basically three options you should consider depending on your expertise and time.
  1. Doing it manually - Depending on the amount of records it will be really annoying.
  2. Using an app such as the one UC Innovation provides.
  3. Going hardcore and creating an external field that will stores master ids info. Let me explain this third option:

Let's consider the following scenario:

Objects:
Object A - Master
Object B - Child

Object A and B have a master/detail relationship and you want to import its data to your sandbox environment without losing their relationship.

1 - Creating a text field on Object A and setting it as external Id:

On object A (Master object), we will create an text field, in the example below, named Old Id setting it as an external Id. (Do not forget to give the proper permission to it).

User-added image

2 - Importing master data:

When importing Object A data to your sandbox you will copy its original Ids to Old Id field. So you will have to manipulate your CSV as shown below:

User-added image

Note that Old_Id__c field now contains records original Ids.

3 - Importing child data:

Here is when the magic happens. When importing data using Apex Data Loader or any similar option you will have the option to select external Id fields by selecting to Upsert your records instead of Insert or Update options.

Upsert basically inserts records that have not been created and updates existing Data.

The CSV file of object B will remain unchanged and all you have to do is to select to Upsert your records selecting the Object A external field Old Id.

User-added image

The second step (2b) of data loader's upsert process will allow you to select the external Id:

User-added image
;
Finally when mapping your CSV file you will select Object A reference that ends with the Old_Id__c field Api:

User-added image

When importing data loader will look at Old_Id__c field to match all reference from your Object B CSV, allowing it to keep all relationships intact.

Believe me it is easier then it looks!


Hope to have helped!


Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.

All Answers

UC InnovationUC Innovation
You can achieve what you want by mapping them manually which can sometimes take weeks to map all of the lookup fields properly however we have a product which is used for data migration and seems to fit your needs. You can try out Cloud Explorer for Salesforce (http://cloudexplorer.ucinnovation.com/). You can move parent and child records it will do the mapping for you in a simple easy to use drag and drop interface.
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Peter Kaye,

As UC Innovation mentioned that is not a easy way to achieve what you are looking for. There are basically three options you should consider depending on your expertise and time.
  1. Doing it manually - Depending on the amount of records it will be really annoying.
  2. Using an app such as the one UC Innovation provides.
  3. Going hardcore and creating an external field that will stores master ids info. Let me explain this third option:

Let's consider the following scenario:

Objects:
Object A - Master
Object B - Child

Object A and B have a master/detail relationship and you want to import its data to your sandbox environment without losing their relationship.

1 - Creating a text field on Object A and setting it as external Id:

On object A (Master object), we will create an text field, in the example below, named Old Id setting it as an external Id. (Do not forget to give the proper permission to it).

User-added image

2 - Importing master data:

When importing Object A data to your sandbox you will copy its original Ids to Old Id field. So you will have to manipulate your CSV as shown below:

User-added image

Note that Old_Id__c field now contains records original Ids.

3 - Importing child data:

Here is when the magic happens. When importing data using Apex Data Loader or any similar option you will have the option to select external Id fields by selecting to Upsert your records instead of Insert or Update options.

Upsert basically inserts records that have not been created and updates existing Data.

The CSV file of object B will remain unchanged and all you have to do is to select to Upsert your records selecting the Object A external field Old Id.

User-added image

The second step (2b) of data loader's upsert process will allow you to select the external Id:

User-added image
;
Finally when mapping your CSV file you will select Object A reference that ends with the Old_Id__c field Api:

User-added image

When importing data loader will look at Old_Id__c field to match all reference from your Object B CSV, allowing it to keep all relationships intact.

Believe me it is easier then it looks!


Hope to have helped!


Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
This was selected as the best answer
Peter KayePeter Kaye
Thanks so much for this input.

What puzzles me is why it should be so difficult to set up a test environment.  My last work in this area was 2 years ago and I seem to remember using a subscription-included sandbox that gave me a full copy of the production db.  I have just inspected another sandbox - Partial Copy - and this doesn't have any data in it either !   (The Developer Pro box was data empty ).  So, unless you pay, is it impossible to get any sandbox loaded with data  - or are their some other sandbox types that I have missed.

You mention "Master Detail Relatioonship" - actually the fields involved are LookUp's.  Does this make any difference ?  Whilst you can only have 1 master detail field on any one custom object  you can, and we do, have multiple look-up fields making your well described approach just that bit more involved.  I like the sound of the UC Innvoation product but that's almost $1K !
UC InnovationUC Innovation
Hi Peter,

I believe the type of sandbox you require is called a Full Sandbox which requires the purchase of a Full Sandbox License but it sounds like you do have a Partial Copy Sandbox license. You can create templates so that your PArtial Copy Sandbox contains the records necessary for testing In my experience these will have the same Id as in Production and will be tied together appropriately if you configure your template to do so. More about the specific types of sandboxes can be found here (https://help.salesforce.com/apex/HTViewHelpDoc?id=create_test_instance.htm).

As for our product. It essentially does all of the mapping work for you. All you do is drag and drop and maybe go get some coffee if your data is large enough. We do offer a free 30-Day no commitment trial which sounds like more than enough time for you to accomplish this task. Once you use it chances are you will not want to use data loader again.

Hope this helps!

AM
Peter KayePeter Kaye
Thanks AM - I was wondering how you specify what data you want - templates !   I am sure your product once used would become addictive - but I suspect the £1K cost is outside the client's current year budget.  Yes,  I could fill my sandboxes via the 30 Day trial and then walk away, but I have a few scriples here if the probability of purchase is low  !  The clients sandbox needs are linked to how much Apex code developement is involved and this is currently not known.  Thanks again to all for input on this.