Simon Fell > Its just code > FK Resolution with external Ids

Sunday, September 7, 2008

One of the tedious things about integrating data across systems is mapping keys, e.g. your account master system is pushing data into, both systems have their own beliefs on what the primary key for one of those accounts is. One of the things has supported for a while is the concept of external Ids, these are custom fields in Salesforce that you've said are a primary key in some other system. By setting this up, you can delegate all of the key match drudgery to the infrastructure. Most people know about the upsert function, which allows the caller to create or update a row based on an external Id value, rather than the primary key. But wait, order in the next 5 minutes and we'll throw in the ability to resolve foreign keys as well, this much less understood feature allows you while calling create/update/upsert, to use external Ids to resolve foreign keys as well. Keeping with our account master example, you may have a system that needs to create Cases in Salesforce, and it only know's the account Master accounts Id, and not the salesforce account Id. To use this, rather than passing the FK itself, you populate the relationship element, and populate its externalId field. Is harder to explain than to show an example, e.g. this C# code will create a new case, related to an account, where we only know one of the accounts external identifiers.

static void createCase(sf.SforceService s, String accExtId, String caseSubject) {
            sf.Case c = new sf.Case();
            c.Subject = caseSubject;
            sf.Account a = new sf.Account();
            a.extId__c = accExtId;
            c.Account = a;

            sf.SaveResult sr = s.create(new sf.sObject[] { c })[0];
            if (sr.success)
                Console.WriteLine("New case created with Id {0}",;
                Console.WriteLine("Error creating case : {0} {1}", sr.errors[0].statusCode, sr.errors[0].message);

Note that rather than populating the AccountId field on case with the Account's Id, we populate an Account with its extId__c value instead. Because you can have multiple external ids on a particular object, this nested object is used to tell which particular external Id field you're using. If you're not a C# fan, then the raw soap request looks like this

		<create xmlns="">
			<sObjects xmlns:q1="" xsi:type="q1:Case">
				<q1:Subject>test case</q1:Subject>