Blog
Contact UsYou can follow Reid Carlberg on Twitter or email him at rcarlberg@modelmetrics.com.
One of the absolute best features of the Salesforce.com/Force.com API is the ability to handle lookup relationships gracefully using external IDs. You can easily have one object with an external ID and then add another object with a lookup relationship to the first without having to know the ID Salesforce.com assigned it. You’ve probably seen this in action in the Apex Data Loader. It saves a huge amount of time.*
I recently had to implement this on my own outside of an existing tool. I had a hard time finding any useful documentation in this area. This write up aims to fill that gap.
Let me start by describing the objects we’re working with. I’m using custom objects, but this can work with standard objects as well. Let’s call them ObjectA and ObjectB. ObjectB has a one-to-many relationship with ObjectA. ObjectA has an external ID field (unique, case insensitive) called “ExternalIdOnObjectA”. ObjectB as a lookup field to ObjectA. The table for ObjectA is populated. We’re upserting a group of ObjectB.
The goal is to create something that says, in effect, “when adding this ObjectB, determine the value to store in the field LookupToObjectAFromObjectB using this value, which you can lookup on ObjectA using the field ExternalIdOnObjectA.” The XML looks something like this:
<…other fields…>
<LookupToObjectAOnObjectB__r>
<type xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">ObjectA__c</type>
<Id xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:nil="true" xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<ExternalIdOnObjectA__c>CC001</ ExternalIdOnObjectA __c>
</ LookupToObjectAOnObjectB __r>
<…other fields…>
A couple of things worth noting:
* The field on ObjectB we’re calling out is the “__r” version and not the “__c”. If you knew the Salesforce.com ID, you would set it to the “__c” version and be done.
* Note the content of the LookupToObjectAOnObjectB tag includes standard information you would see as part of a regular SObject: Type, indicating that it goes to ObjectA, and ID which in this case is empty since we don’t know it.
* Before closing, we define the ExternalIdOnObjectA field value.
That’s it. So how do we do this in Java? Setting a field value in Java is normally just a matter of adding a MessageElement to the SObject.
MessageElementBuilder.getMessageElement(FieldName, FieldValue)
And this is no different. By the way, “MessageElementBuilder” here is just a little utility class I have that handles – wait for it – building message elements. It’s the same kind of thing you see in the sample code from
By now you’ve probably figured out where we’re going. The next step is to build an SObject for the reference first and then set that SObject as the FieldValue for the message element.
SObject foreignKeyToResolve = new SObject();
ret.setType(“ObjectA__c”);
ret.set_any(new MessageElement[] {
MessageElementBuilder.getMessageElement
("ExternalIdOnObjectA__c", "CC001") });
SObject objectBInstance = new SObject();
ret.setType(“ObjectB__c”);
ret.set_any(new MessageElement[] {
MessageElementBuilder.getMessageElement
("LookupToObjectAOnObjectB__r", foreignKeyToResolve) });
And that’s all there is to it. Hopefully this saves you some time down the road. FWIW, I was working in Java on the Cloud Converter when I wrote this. However, you should be able to use this technique with any of the toolkits — .NET, PHP, etc.
Good luck!
* In fact, upsert with foreign key resolution saves so much time that I’ve refused to use integration toolkits that don’t support it out of the box. The good news is that the two major vendors who I have had the joy of talking with about this issue have responded quickly.
