Cascade Insert with External Id Fields

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;


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(); = ‘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.

tagged , , , , , , , Bookmark the permalink. Trackbacks are closed, but you can post a comment.
  • Abhinav Gupta

    Great post that revealed something really fresh !

  • Cool…I use Mixed Save feature quite heavily!

  • Very cool, but it’s not exactly clear why parent.Grandparent__r = g won’t work. Can you explain why? It seems really bizarre that it wouldn’t work.

    • Anonymous

      Yes, can someone please explain why Grandparent__r = g doesn’t work? I receive an error about having 2 external ID’s, this solution does seem to work for me but doesn’t seem right, is it possible to get some more explanation? Is this long term solution, what if Salesforce fixes this as I think it is a bug?

  • Anonymous

    Hey Hallie,
    Why the last statement “insert([grandparent, parent])” has object name “grandparent”, it is not defined any where.

    • I think insert([g, parent]); was the intention

      • Anonymous

        Thanks to update..

    • Halloran Parry

      Oops, good catch. Updated.

    • Hallie Parry

      Good catch. Updated.

  • This is an excellent post — makes me want to read through the whole Web Services API documentation to see what other nuggets are in there. The whole intermediate/”nested” object requirement is, indeed, quite strange.

  • Anonymous

    This is very helpful. Out of curiosity, has anyone done this with the Partner API? I’m finding difficulty properly constructing the nestedObject and including it in the loosely-typed sObject one constructs when using the partner wsdl. Any help — particularly c# or java example — would be greatly appreciated!!!

  • Hello , thanks for this magic! But , I have a problem with triggers.
    I m trying to set the look up field with external Id like:
    X__c X=new X__c(X_External__c=’abcd’);[0].X_Lookup__r=X;
    Code above does not work. It does not set the look up even the record with external Id is already existed!
    Any help would be welcome!

  • Andrew Fawcett