Oh my god. It's full of code!

While This is Awesome, There Must be a Better Way.

This is half an interesting post about how I solved a fairly complicated problem, and half me looking for a better way to do it. The problem I was solving in theory is fairly simple.

I want to import answers from an external survey software into our Salsforce org.

Pretty simple right? Standard import functionality. Nothing too complex. But now let’s build on this.

1) It should be automated. No manual triggering of the import.
2) It should be imported as it is created. Closer to real time than to ETL.
3) The place the data is coming from has no API. We only have direct ODBC access to it.
4) Since the process must be closer to real time, we should use a ‘push’ style mentality for import. The survey software should push the data into Salesforce as it is created, instead of Salesforce requesting it.
5) The survey software only gives me access to javascript to add functionality. No server side processing of any kind.
6) Solution should be as cloud based as possible. Ideally no code on our servers (we have a cold fusion server we use for a lot of data brokering, but we would like to get rid of it).

Suddenly got harder eh? Right off the bat, point 3 means we are going to need to use some kind of middle-ware (in this case our Coldfusion webserver that can talk to the database). Salesforce has no method to talk directly to a database, as it shouldn’t. Everything is done with API’s these days, nobody directly queries databases anymore. At least they shouldn’t. So my first task was to write an API that Salesforce could access to get it’s data. So I wrote up a simple ColdFusion webservice that queries the survey database, formats the data in a reliable way, and returns a JSON array. While not ideal because it does use our server, at least it’s fairly transparent. It’s just a webservice call that could possibly be replaced in the future if the survey software does release an API in the future.

With the webservice up and running, now I need some Apex code to call out to it, get the data and handle it. So I wrote a nice little Apex method that uses a simple HTTP get with some URL params to invoke my ColdFusion webservice. Now it can get the JSON data. Using the new awesome JSON parser in winter 12 I am able to parse the data into a list of custom objects. Hooray.

So now I need a Salesforce object to store this data right? Actually I am going to want a few. I ended up creating 3 in total. The first object ‘survey’, is just a simple container object with pretty much no fields. The second object, ‘survey entry’ is just an object that will contain all the answer objects for a person in a given survey. It of course has a lookup to the ‘survey’ object, as well as a lookup to a contact, and some other info (when they took the survey, etc). The third object ‘survey answer’ is where the real juicy data is. It has the ID of the question, the text of the question, the persons answer, and a lookup to the survey entry.

So now I modified my Apex class a bit to create the survey answers objects, and relate them to the proper ‘survey entry’ (and create one if one does not exist for this person in this survey yet). Boom, so now all the ‘hard’ work is done. I have a class that can be called that will import the survey data for a person into Salesforce. But wait, how do I actually call this thing? I don’t want this on a scheduler, I want it to get called when new data is available. So I need to make this class itself into a webservice of some kind.

I have a bit of experience with Apex REST so I decided that would be a fitting way to handle this. This class only needs the ID of the survey, and the person for whom it needs to import data. That information is easily included in the URL or in POST fields, so I quickly modified my class to be an Apex REST service. Now it was ready to begin being accessed from the outside world. The question now is, how do I invoke the service itself?

First I used the apigee app console to make sure it was working as required. Apigee handles the oAuth and lets you specify params so testing your Apex REST service couldn’t be easier. Once I had verified that it worked, I needed some method to allow the survey software to invoke it. Problem is of course, if you remember that the survey software only supports JavaScript. JavaScript is still subject to that cross domain security policy BS. Normally you could use the script injection technique to make a callout to a different domain, but I need to set headers and such in the request, as well as making it a post request, so that wasn’t going to fly. On top of that I have would have no idea how to let JavaScript start using oAuth or get a valid session ID. So here is where things get a little murky.

How could I allow a javascript only application invoke my Apex REST service? Looks like I would again have to turn to my ColdFusion middleware box. I wrote another webservice which can invoke the desired Apex method from ColdFusion. You can call Apex REST services using a session ID instead of having to deal with oAuth so I went that route. I already have integration between Salesforce and ColdFusion through use of the awesome CFC library provided at RIA Forge (I actually helped contribute a bit to that). So I just wrote up what basically amounts to be a wrapper. You can invoke it from a simple get request and it will wrap the request with the required headers (authorization, content-length, content-type) and send it off to Salesforce. ColdFusion web services have the awesome feature of being able to be called via a URL, instead of having to use a WSDL or whatever. Come to think of it, they are almost a forerunner for REST, but I digress.

So now I have a URL that when called (with some arguments/params) will invoke my Apex REST service that goes and gets the survey data and imports it. So now I still need to get the survey software to call this URL. Here I use the classic script injection technique to make my cross domain request (because the survey software and my ColdFusion box live on different domains) and it much to my surprise it all worked. If you are curious, the code to do that looks like this.

function loadJSON(url)
var headID = document.getElementsByTagName("head")[0];
var newScript = document.createElement('script');
newScript.type = 'text/javascript';
newScript.src = url;
var survey = '12345';
var token = 'mkwcgvixvxskchn';
var contact = '003GASDFADFAFDAS';
newUrl = 'http://XXXXXXXXXXXXX/webservice.cfc?method=importData&survey='+survey+'&token&='+token+'&contact='+contact;

So in the end, this is the process I came up with.

1) User takes online survey from 3rd party site (lets call it survey.com)
2) survey.com invokes javascript which calls the ColdFusion webservice (which includes survey id and person id in the request)
3) ColdFusion receives the request, and ‘wraps’ it with the needed authorization information to make a valid HTTP request.
4) Salesforce custom Apex REST class receives the request (with survey id and person id still included)
5) Salesforce sends request BACK to a different ColdFusion webservice, which requests the actual question/answer data.
6) ColdFusion receives request. Queries survey database and encodes database info as an array of JSON encoded objects.
7) JSON encoded data is returned to the calling Apex, where it is parsed into custom objects and committed to the database.

Seems kind of obtuse eh? Yet I can’t think of any way to make it leaner. I really would like to eliminate the 2nd and 3rd step and just have the survey software invoke the Apex REST directly somehow, or at least make the call totally cloud based. I suppose I could host a visualforce page that does the same thing, and have the JavaScript call that…

So anyway, here you can see an interesting case study of integrating a lot of different crap using some fairly new methods and techniques. I am totally open to suggestions on how to refine this. Right now there are just so many points of failure that it makes me nervous but again it seems to be about the best I can do. Thoughts and feedback welcome in the comments.

7 responses

  1. Ilya

    Why not do this:

    1) Survey is finished, JS calls CF.
    2) CF fetches the data from the survey via ODBC.
    3) CF pushes data into SF, via an apex webservice class on the SF side.

    You still need a middleman (CF), but you can make it smart and keep track of successes/failures via the created/last modified date in your survey DB. So if someone takes a survey during a SF outage, you will grab all the failed records the next time a survey is taken.

    December 8, 2011 at 4:40 pm

  2. That was actually my first thought, as it is much cleaner. I do like that approach, but there is one caveat. I am trying to build things so that in the future it will be easier to have an entirely force.com hosted solution. Using CF to push the data means that SF isn’t really controlling the action. In the future when our survey software releases an API, it could be possible for Salesforce to query the survey software directly with no middleman needed (provided I can find a way for JS to tell Salesforce to start the import, likely an http request to a special visualforce page that invokes the import class).

    December 8, 2011 at 4:48 pm

  3. Ilya

    True, but if you continue to rely on script injection, you run the risk of future security policy/browser upgrades breaking your code without you realizing that you might be missing survey results.

    You could also, int the future, get SF to fetch the updated survey data as needed (when it is viewed in SF), as opposed to constantly doing updates.

    I would still put in some kind of scheduled monitoring that lets you know that (for example) 50 surveys were completed today and only 45 new objects were created in SF.

    December 8, 2011 at 6:35 pm

  4. I like your thoughts, especially about the import record counter. That is actually something I will likely try and implement over the next few days.

    You raise a valid point about the script injection possibly breaking, however I don’t know of any other way to let the client inform a remote system that the data is ready to be imported when the only language I have access to in the survey platform is javascript. I am actually hoping in the future the cross domain security policy will be reviewed and made a little more lax as web services and API’s become more common. IMHO the policy is fairly archaic. One of the requests from my ‘client’ for this system is that it was as close to real time as possible, so either the client has to inform SF that the data is ready to be imported, or Salesforce has to poll the system periodically and attempt to import. That approach is further complicated by the fact that there isn’t a great way to tell if a survey is complete (their database design isn’t great). I much prefer using a push vs a pull mentality when dealing with integration. Much less wasted overhead.

    When you say data could be imported when it is viewed, that is an interesting possibility. Could you expand on that a bit?

    December 8, 2011 at 7:01 pm

    • Ilya

      Let’s pretend that your survey results are displayed on a VisualForce page.

      When the page is loaded, it can call CF via ajax (or, in the future the survey API), import the most recent survey data and then display it.

      This, obviously, only works if this process is relatively fast and you have an easy way of just getting the updates from the survey DB.

      December 8, 2011 at 7:06 pm

  5. Ah alrighty. The import process takes roughly 5 seconds or so. Each survey consists of only about 20 records per person. So it doesn’t take much to import that. Right now survey results are just accessed via related lists on the contact or project that the survey is related to.

    I would really like to find a way for javascript to be able to kick off the Apex import job directly without using any dirty hacks. If there was a way to do that, I would be moderately satisfied with my solution. Then it’s just a waiting for the survey software to release their API. Then it would a direct client to salesforce to survey software connection with no coldfusion middle man (which is really the biggest goal. We want to have no servers here in our office eventually, hopefully within the next 2 years). There must be a simple clean way for javscript to invoke apex. The best I can think of is a visualforce page that is just a listener that when it receives an http request it invokes the import job with params given in the URL, but that still requires script injection and using a VF page, which is dirty.

    December 8, 2011 at 7:11 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s