Cascade Insert with External Id Fields   Leave a comment

http://blogs.developerforce.com/engineering/2012/02/cascade-insert-with-external-id-fields.html

 

The problem

If you’re like me, you’ve got a whole bunch of external data in the form of Grandparent -> Parent -> Child. Those are all one-to-many relationships. You’d like to import this data into Salesforce — into three corresponding custom objects, in fact. You’d like to represent those relationships with with some master-detail fields, and you’d really like to be economical with your insert statements because you live in mortal terror of governor limits.

The useless solution

The naive method of accomplishing this is about as attractive as a centipede with pox. Insert the Grandparent row, by itself. Retrieve the id, and use it to populate Parent__c.Grandparent__c. Insert all the parents, retrieve the ids, and then use those ids to populate Child__c.Parent__c. In a world where an insert call can process up to 200 records at a time, wasting an insert on a single record is a reprehensible offense. Also, this approach must be done synchronously, and that’s not such a great option when you’re talking about tens of thousands of records.

The much better solution

Let’s talk about external ids for a second. You can read up on them here, but the basic gist is that you can create a custom field on an object in Salesforce and set it to be an external id (and if you do this, you probably also want to set it to require unique values). Setting the external id attribute tells Salesforce that this field is used as an identifier in some other, non-Salesforce system. This lets you do some extremely powerful things, like call upsert on a whole bunch of data without Salesforce ids.

It also lets you denote foreign key relationships. So even if I don’t know the Salesforce id of my Grandparent, I can do something like this:

Grandparent__c g = new Grandparent__c();
g.External_Id__c = ‘myExtId’;

Parent__c parent = new Parent__c();
parent.Grandparent__r = g;

insert(parent);

See that? I don’t need the id of the Grandparent object. I just need its external id. When I insert the parent into Salesforce, the foreign key to Grandparent will be populated with the correct Grandparent row. Pretty neat, huh?

Note the __r on the foreign key field in the example. That suffix denotes a relationship field which can be populated by an object, rather than a normal __c vield which takes a single value.

The last piece of the cascade insert puzzle is something I’m going to call mixed save, because I don’t think it has an official name (hello, marketing!). Mixed save is a feature of our API that allows you to include multiple types of objects in an insert or update call (but not upsert!). So let’s look at that last example again. For that to work, the Grandparent row already has to exist in Salesforce, right?

Maybe not.

Grandparent__c g = new Grandparent__c();
g.name = ‘gp’;
g.External_Id__c = ‘myExtId’;

Grandparent__c nestedObject = new Grandparent__c();
nestedObject.External_Id__c = ‘myExtId’;

Parent__c parent = new Parent__c();
parent.Grandparent__r = nestedObject;

insert([g, parent]);

Pay special attention to the nestedObject record above. It represents the same data record as g and yet we’re creating a separate object. This is necessary. It’s tempting to say parent.Grandparent__r = g; but it won’t work. Trust me. I spent hours on this so that you don’t have to. You can find more information on mixed save by scrolling down to the “Creating Records for Different Object Types” section of this page.

Well that’s pretty neat. I’m inserting a record and its child at the same time in the same DML call and Salesforce will automatically fill in the right foreign key values. So now, going back to my original problem, I can create some external id fields on my Grandparent__c and Parent__c objects, make sure the corresponding fields are populated in my existing data, split everything into batches of 200, and insert to my heart’s content.

Posted 2012年06月22日 by gw8310 in salesforce

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s

%d 博主赞过: