Bulk API & Foreign Key Resolution on Lookup Fields

Question

We are designing an Integration leveraging Bulk API & Enterprise WSDL (The client code is handled by the source system). The target Force.com Object has few Lookup Fields. Wanted to understand how to automatically resolve the Foreign Key.

For instance we have a Master Object "Geo Area" with values like 'APAC', 'EALA' etc.... Another Force.com Object called "SAP OM" which is the actual Object into which the integration upserts data has a lookup Field called "Client Geo Area" which looks up to "Geo Area" Master Object. The source system will only push values like 'APAC' or 'EALA' etc via the integration which we need to store into the lookup Field. However the Lookup Field expects the Row Id of the Master Values and not the actual Values.

How do we resolve this?

In the Bulk API guide under "Relationship Fields for Custom Objects" section it is mentioned that relationship name could be used to resolve Foreign Keys. Has anyone used this?

Answer 1

I've not used this API myself, but have used the underlying approach of resolving foreign keys using External Id fields. It is a feature that can be used in other Salesforce API's as well.

Your on the correct path with Relationship Fields in Records, and the sub-section, Relationship Fields for Custom Objects. What perhaps is not that clear is that you will have to designate an existing field or create one on your objects to get this to work, that have the External Id attribute enabled on them.

Then ensure that whatever value the external system does know about (such as the Geo Area code) is populated into this field as records are created (or existing records are updated with it). Then when using the Bulk API and you want to relate child records provided by your external system you can use these values instead of having to determine the Id's. Basically Salesforce will do the resolution for you.

The relationship name is basically the API name of the existing lookup field suffixed with __r instread of __c. Again the help topics above do a reasonable job of explaining all of this.

Custom objects use custom fields to track relationships between objects. Use the relationship name, which ends in __r (underscore-underscore-r), to represent a relationship between two custom objects. You can add a reference to a related object by using an indexed field. A custom field is indexed if its External ID field is selected.

Answer 2

First you will need to query all "Geo Area" records and store them in a map <Name, ID> in your source system. Then before you push your "SAP OM" records to SF, you will need to set the "Geo Area" lookup field to the ID of the corresponding name retrieved from the map that you have at this point. Depending on the source system language by map I mean 2 dimensional array.

Answer 3

If the source system cannot or will not push the values in with the real Salesforce ids then you will need to create an intermediate layer in the API to handle the translation. The easiest would probably be to create a new web service that is capable of accepting the data provided by your source system and performing the lookups to get the real Salesforce ids.

Note that if you pursue this method you will need to ensure it is bulk friendly.

Answer 4

I think the BULK API works the same way as the normal dataloader in this context. If so, you need to have a external Id field (e.g myexternalId__c) on the master object ("Geo Area" ) that contains the values you are matching against e.g. 'APAC', 'EALA'. Then your SAP OM data load file needs to include a reference to that external id field - e.g. Geo_Area_r.myexternalId_c, and that contains values that match the Geo Area object.

So something like

Name, Geo Area__r.myexternalId__c
My New Value, APAC

When you load the data, the api should recognise the External Id reference, and create the lookup for you. You can't use a standard field as an external id, so you'll need to create a new custom fields that is setup as an external id.