Saturday, 24 March 2012

Create Parent and Child Records in One Insert Call

If you're anything like me, creating parent and child records in Apex is something that has to be done on a pretty regular basis. This blog post is one I've had on the list for a while, but it came up on the discussion boards yesterday (March 2012) so it seemed like as good a time as any to write it up properly.

The simplest way to achieve this is to insert the parent, then set the lookup relationship id on the child to the parent record id, as follows:

Account acc=new Account(Name='Blog Acc1');
insert acc;

Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', AccountId=acc.id);
insert cont;

the downside to this approach is that it is unlikely to scale.  If I need to insert a large number of accounts and contacts, I'll have to manage the relationships myself in some way - probably using wrapper classes to  combine an account and its list of contacts, with multiple iterations to insert the accounts, then populate the lookup fields.  All in all quite a lot of code.

The next avenue I explored was setting the child relationship field to the parent record:

Account acc=new Account(Name='Blog Acc2');
insert acc;

Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=acc);
insert cont;

No dice on this I'm afraid - even though I've inserted the parent account first, the child contact is stored without a parent account.

One of the techniques that I came across while studying for the Technical Architect Certification was to specify the parent object via an external id.  So I created an external id field on my account named Master_Id__c and inserted an account:

Account acc=new Account(Name='Blog Acc3', Master_Id__c='Blog Acc3');
insert acc;

Once the account is in place, I can instantiate the parent record based on the external id and set the relationship field:

Account acc=new Account(Master_Id__c='Blog Acc3');
Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=acc);
insert cont;

Looking better, as I don't have to set ids, but I'm still inserting the parent first and then the child. Perhaps its possible to instantiate a new account and contact and then insert them later:

Account acc=new Account(Name='Blog Acc 4', Master_Id__c='Blog Acc 4');
Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=acc);
insert acc;
insert cont;

Once again, no dice. The same result as the second attempt - the account and contact are inserted, but the relationship is lost. Given that there's very little difference between this and the last attempt, it looks like its the name that is causing the problem. After trying a few permutations, the following code confirmed that this is the case:

Account acc=new Account(Name='Blog Acc 6', Master_Id__c='Blog Acc 6');
insert acc;

Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=new Account(Name='Blog Acc 6', Master_Id__c='Blog Acc 6'));
insert cont;

This throws the following exception - System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, More than 1 field provided in an external foreign key reference in entity: Account: [].
 I've no idea why this exception isn't thrown when I specify a previously instantiated account rather than instantiating as part of the contact record, but there it is.   Given this error message, it seemed possible to create a contact and identify the account by Name, but that didn't work either - a similar exception complaining that Name isn't an external id or indexed field.

 This did guide me to the preferred solution though - simply instantiating a new account as part of the contact and only specifying the external id:

Account acc=new Account(Name='Blog Acc 7', Master_Id__c='Blog Acc 7');
insert acc;

Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=new Account(Master_Id__c='Blog Acc 7'));
insert cont;

Looking a lot better, but there are still two insert statements - luckily insert statements can take a list of generic sobjects to insert, so I can insert both objects in one go. As long as the parent record is inserted first, everything works as expected:
Account acc=new Account(Name='Blog Acc 8', Master_Id__c='Blog Acc 8');
Contact cont=new Contact(FirstName='Bob', LastName='Buzzard', Account=new Account(Master_Id__c='Blog Acc 8'));

insert new List<Sobject>{acc, cont};

20 comments:

  1. Great stuff, Keir. I wasn't aware that this could be done. This is most useful.

    ReplyDelete
  2. Hi Bob,

    I think is the first time I comment on your blog, but note I've been following it for quite long time now. Once again, superb work.

    I'm actually trying to port this to a problem that I have right now in a real development. I need to keep low the number of inserts, so I followed your recipe. But I have one issue:

    I created a routine to add contacts+accounts related, so after all my calculations in other non-related functions are finished, I need to insert this Account+Contact; in order to accomplish that, in the meanwhile the flow of events doesn't get to the insertion point I need to store them in a data structure capable to be inserted.

    Is this possible? So far, I've tried to do it with maps, but inserts doesn't allow that.

    Do you have any idea on how can I solve this?

    ReplyDelete
  3. @magandrez - Can you create a Map that is keyed by account name (or similar) and has a value of a list of generic sobjects? Then you could iterate the keyset of the map and insert the list of records associated with the key.

    ReplyDelete
    Replies
    1. Thanks Bob, you nailed. :)

      Delete
  4. Thanks for the post Bob

    ReplyDelete
  5. Bob, it's amzaing, thanks for your post.

    ReplyDelete
  6. Great post, this should be part of native Apex docs.

    ReplyDelete
  7. Hi Bob,

    I have a different situation where the parent and child both have name as auto sequence fields. e.g. Order Header and order lines.

    Can you suggest a way so that I can insert these with single insert call like in your example.

    ReplyDelete
    Replies
    1. I wouldn't have thought the autonumbering would make any difference - as long as you specify the external id, you should be able to leave the names blank, as the system is filling those in.

      Delete
  8. Very useful technique Bob. Need little help with a different situation. I need to insert child records from a CSV (contains parent id) )and at the same time need to update parent record.

    ReplyDelete
  9. A workaround to the exeception "System.DmlException: Insert failed. First exception on row 0; first error: INVALID_FIELD, More than 1 field provided in an external foreign key reference in entity"

    is to create a temp Account with just the external id field and then linking the contact to it :

    Account acc = new Account(...);
    Contact cont = new Contact(...);

    Account tempAccount = new Account(
    ExternalId__c = 'external_id'
    );

    cont.Account = tempAccount;

    insert acc;
    insert cont;

    ReplyDelete
    Replies
    1. @Tom - That's exactly what I put in the blog post as the solution - not sure why you are posting the same thing here.

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Great Post Bob, this really helped. However when I am trying to add Opportunity Team Members using the same concept, it errors out.

    First of all I create an instance of Opportunity and User using their respective External Id's

    User teamUser = new User(User_External_ID__c =reader.getText());

    Opportunity req = new Opportunity(Opportunity_External_ID__c = reqNumber);

    I then create an instance of Opportunity Team Member

    OpportunityTeamMember teamMember = new OpportunityTeamMember(Opportunity = req, User = teamUser, TeamMemberRole = 'FM');

    Once I do an upsert of for teamMember, it gives me an error saying

    VF_PAGE_MESSAGE OpportunityTeamMember: bad field names on insert/update call: User, Opportunity

    System.DmlException: Upsert failed. First exception on row 0; first error: INVALID_FIELD_FOR_INSERT_UPDATE, OpportunityTeamMember: bad field names on insert/update call: User, Opportunity: [User, Opportunity]

    VARIABLE_ASSIGNMENT [1675]|e|"common.apex.runtime.impl.DmlExecutionException: Upsert failed. First exception on row 0; first error: INVALID_FIELD_FOR_INSERT_UPDATE, OpportunityTeamMember: bad field names on insert/update call: User, Opportunity: [User, Opportunity]"|0x19dcd463


    Not sure what am I missing. I tried all the examples described above. Thank you for your help

    ReplyDelete
  12. Hey Bob,
    I am trying to insert multiple child records to the junction object.
    I have snippet works fine for single but when i try multiple it throws me error field writable.I tried to use your solution but is giving error. can you help me to fix my issue.

    ReplyDelete
  13. Very good post Bob. I am facing a situation similar to it but with a twist. I need to be able to insert the parent, its children and then grandchildren.

    Something like if parent name is A then A has three children

    A1
    A2 and
    A3

    And then grand children

    A1-Child1
    A1-Child2

    A2-Child1
    A2-Child2

    A3-Child1
    A3-Child2
    A3-Child3.

    How would I resolve this.

    ReplyDelete
  14. Great Stuff, thank you Bob :-)!

    ReplyDelete

  15. Hi Keir,

    This blog post has always been a reference for me.
    I come back to it regularly to check for the syntax of case number 8 while upserting lookup references to objects via a foreign key relationship. This has always worked for me : for Contact+Account, OrderLine+Order, OpportunityLineItem+Opportunity, etc.

    Up to today :(

    I am trying to insert/update contacts with a reference to other contacts.
    The use case ?
    I have a list of contacts, all with an email address as an external ID in IDEXT_Email__c. Some are endorsed by others, so Contact will have an EndorsedBy__c lookup to a Contact. As this stuff is coming from another system, we are using upsert with foreign keys : Contact has an EndorsedBy_Email__c field with the email address of the contact who endorsed it; this serves as the key in the foreign relationship to look up IDEXT_Email__c on Contact.
    This is great in practically all cases ... except the following.

    I have a list of 200 accounts, some being endorsed by others ... in the same list.

    We are handling the foreign key wizardry in a Contact trigger.
    I have tried all the possible solutions I can think of, but our problem boils down to the following : from within a trigger (on a list of contacts), you cannot upsert a contact which is already in the list. Unfortunately, the syntax suggested by you in case number 8 does not work if the related-to Contact is not upserted previously, in another transaction : it will not work if the Contact is in the same transaction, event if it is before in the list.

    Any thoughts ?

    Have a good day,
    Rupert (@altius_rup)

    ReplyDelete
    Replies
    1. I don't have any ideas to make it work I'm afraid. I suspect its to do with the trigger semantics, because the upsert has already taken place (at least partly), There may be something around the fact that account/contact (and opportunity/line item, order/line item) are system relationships rather than master/detail or lookup, which confers some additional capability on them.

      Delete