Simon Fell > Its just code > September 2008

Monday, September 8, 2008

Just like i described yesterday for the API, you can do exactly the same foreign key resolution using external Ids in Apex code, e.g.

Case c = new Case(subject='Apex FKs');
Account a = new Account(extId__c='00001');
c.account = a;
insert c;

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 Salesforce.com, both systems have their own beliefs on what the primary key for one of those accounts is. One of the things Salesforce.com 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 Salesforce.com 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 Saleforce.com 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}", sr.id);
            else
                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 Salesforce.com 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 Salesforce.com which particular external Id field you're using. If you're not a C# fan, then the raw soap request looks like this

	<soap:Body>
		<create xmlns="urn:enterprise.soap.sforce.com">
			<sObjects xmlns:q1="urn:sobject.enterprise.soap.sforce.com" xsi:type="q1:Case">
				<q1:Account>
					<q1:extId__c>00001</q1:extId__c>
				</q1:Account>
				<q1:Subject>test case</q1:Subject>
			</sObjects>
		</create>
	</soap:Body>