Simon Fell > Its just code

Monday, September 08, 2008

Apex and FKs

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 07, 2008

FK Resolution with external Ids

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>

Thursday, August 28, 2008

binary data + meta data

I see Joe is busy standardizing a way to bundle binary data with atom metadata, looks a lot like SwA, just switch out the soap envelope for an atom envelope. Amazon's S3 uses DIME for their SOAP API (good for them, I always liked DIME much better than SwA or MTOM), and their REST api uses HTTP extension headers to carry any metadata. Is there anything else? beside bas64, which is fine for smallish stuff fails in the face of larger binary data (the more things change the more they stay the same), should i be brushing up my mime parsing skills. ? is WebDav interop any better than it used to be?

Wednesday, August 13, 2008

bad week to be in the email business

Continued problems with MobileMe, the recent Gmail outage, and now NetIdentity are still trying to pick up the pieces from a multiday outage.

Sunday, August 10, 2008

Finance

There's going to be no MS Money 2009 release, and my Money 2006 "subscription" is about to expire, to add insult to injury, the link the app to "upgrade" doesn't do anything. I looked at Mac alternatives a while back and was disappointed. There's new versions of MoneyDance and iBank out, so I guess i'll have to take another look at those. Of course, the curse of being a programmer is I'm tempted to just go build my own, all I really need is an easy way to import data, and to get some reports out, nothing too fancy (even in Money half of the useful feature's I'd like to use like asset allocation tracking don't work anyway).

Saturday, July 26, 2008

Rega P25 for sale [SOLD]

Upgrade lust strikes, and I've decided to sell my turntable, a Rega P25 with cherry surround complete with a Dynavector 10x4 MKII Moving Coil cartridge. Vinyl is having something of a renaissance right now, and this is a great way for you to join in. Everything is still in mint condition and i have the manual to go with both the TT & cartridge, new the pair cost over $1600, I'm asking $850 (buyer collects from San Francisco). drop me a note if you're interested. (Stereophile P25 review, Soundstage review of the 10x4).

Sunday, July 13, 2008

Taking Interop services down

The web services interop registration service I setup many moons ago has faithfully been chugging along, but i think its time has come to an end, i'll be taking it down sometime next week.

Updates

I released a new version of SoqlX a few days ago, this updates it to the v13.0 API, and adds the ability to save the query results as a CSV file. And i just posted a new release of PocketHTTP, this adds a change to not send the full URL when proxying over SSL. PocketSOAP and PocketXML-RPC both now are a few revs behind pocketHTTP. I'll be updating the installers for those 2 to bring them up to date sometime in the next week.

Thursday, July 10, 2008

Salesforce Mobile for iPhone

Hits the streets today, even making the front page of AppStore. Finally, time for some sleep ;)

Thursday, June 26, 2008

sfical.py

Sometimes full blown 2 way sync is just overkill, for some people just having a read only copy of their salesforce.com calendar in iCal is all they want, fortunately this is pretty straightforward, iCal supports subscribing to iCalendar feeds, and conveniently your Mac includes a copy of Apache httpd, so can easily run perl, python etc in response to a request. Throw in a library for accessing the Salesforce.com API, and you have all the ingredients needed. I put this together in Python using Beatbox, it makes API calls to login and to query events, and to generate an iCalendar formated response, You then subscribe to the url on your local Mac in iCal to show the data in iCal with the rest of your calendars.

• Download the code, this has the 2 files for beatbox, and the third is the iCalendar generator.
• Unzip this and drop the 3 files into your /Library/WebServer/CGI-Executables folder.
• From System preferences -> Sharing make sure that Web Sharing is turned on.
• Quickly test it in safari, goto http://localhost/cgi-bin/sfical.py?un=user@foo.bom&pwd=SomePassword if everything works, you should see something that starts like
  

  BEGIN:VCALENDAR
  VERSION:2.0
  PRODID:-//PocketSoap//pyCal 1.0//EN
  X-WR-CALNAME:Salesforce calendar : Simon Fell
  METHOD:PUBLISH
  BEGIN:VEVENT

• Goto iCal, goto the calendar menu and pick subscribe, paste the URL from safari into the URL box, and then in the resulting dialog, pick how often you want it to auto-refresh (I'd suggest no more often than once a hour).

That's it!, you're good to go, you should have all your upcoming events now showing in iCal.

SF3 v0.6

SF³ Is now available, if you're on a older version, you'll get prompted to upgrade next time you restart it. This adds a new option to get a prompt before applying any changes to Salesforce.com, useful for spotting when a sync is going to change/delete more data than you were expecting.

Also included is support for multi-day all day events, a new feature added to Salesforce.com in the summer '08 release, now these will get correctly mapped between Salesforce and iCal.

Sunday, June 08, 2008

WWDC Coffee options

There's no shortage of bad coffee choices next to Moscone, but if you can drag yourself away from all the conference action and walk one block, (mint plaza, mission & 5th) you can avail yourself of some of the best coffee in the bay area, or indeed the west coast. The Blue Bottle cafe opened earlier this year in Mint Plaza, go check it out, you won't be sorry.

Thursday, June 05, 2008

WWDC

Unless you've been living under a very large rock, you know its WWDC next week and everyone is hopped up on predictions of OSX 10.6 and 3G iPhones with GPS & espresso machines built in, and of course the release versions of iPhone 2.0 and the SDK. Besides all the iPhone news everyone is waiting for, I'd like to see Apple step up to the plate on virtualization, and let us run OSX virtualized, its a royal pain in the ass to support OSX apps because the only way to test on multiple versions of the OS is to maintain a bunch of different installs of the OS and boot between them, how 1990's. And it not even like you can get away with just a 10.4 and a 10.5 install, there's enough differences between 10.5.0, 10.5.1, 10.5.2 and 10.5.3 that you'd really want to test on at least some of them.

who.type

Quick Salesforce.com API tip, most of the API requires you to know both the ID of a record, and its type (e.g to update it, or to retrieve its field values). In most cases it pretty easy to get that, but where most people get tripped up is on polymorphic foreign keys (e.g. whoId on task), the trick is to remember the thing at the end of the relationship can tell you its type, so instead of using this query, then being left trying to work out what type the whoId is,

select id, whoId from task

instead use SOQL-R, and use this query, now you will know what type the whoId is without any further shenanigans (like calling describeSObject on everything and matching the keyprefix which is horribly slow way to do it)

select id, who.id, who.Type from task

The follow on is that it then becomes easy to run queries that find all tasks ascociated with contacts, it's just

select id, who.id, who.Type from task where who.Type='Contact'

Saturday, May 03, 2008

Metadata API (more)

I discovered a new Salesforce related blog today, and noticed that they spread the seeming popular meme that the metadata API doesn't work on standard objects, so just clarify that that's wrong, here's a .NET example that adds a new custom field to the standard Account object.

using System;
using System.Collections.Generic;
using System.Text;

namespace metadata
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.Length != 2)
            {
                Console.WriteLine("useage: metadataDemo username password");
                return;
            }
            MetadataCreator mc = new MetadataCreator(args[0], args[1]);
            mc.Create();
        }
    }

    class MetadataCreator
    {
        private metaforce.MetadataService ms;
        private sforce.SforceService ss;

        public MetadataCreator(String username, String password)
        {
            ss = new sforce.SforceService();
            sforce.LoginResult lr = ss.login(username, password);
            
            ss.Url = lr.serverUrl;
            ss.SessionHeaderValue = new sforce.SessionHeader();
            ss.SessionHeaderValue.sessionId = lr.sessionId;

            ms = new metaforce.MetadataService();
            ms.Url = lr.metadataServerUrl;
            ms.SessionHeaderValue = new metaforce.SessionHeader();
            ms.SessionHeaderValue.sessionId = lr.sessionId;
        }

        public void Create()
        {
            metaforce.CustomField cf = new metaforce.CustomField();
            cf.description = "Favorite fruit";
            cf.fullName = "Account.favFruit__c";
            cf.label = "Fav Fruit";
            cf.type = metaforce.FieldType.Text;
            cf.length = 25;
            cf.lengthSpecified = true;

            metaforce.AsyncResult r = ms.create(new metaforce.Metadata[] { cf })[0];
            while(!r.done) {
                System.Threading.Thread.Sleep(r.secondsToWait * 1000);
                r = ms.checkStatus(new string[] { r.id })[0];
            }
            if (r.state == metaforce.AsyncRequestState.Error) 
                Console.WriteLine("Error : {0} {1}", r.statusCode, r.message);
            else {
                Console.WriteLine("Done, added new field to Account");
            }
        }
    }
}