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.
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?
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.
var headID = document.getElementsByTagName("head");
var newScript = document.createElement('script');
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)
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.
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.
So this time I have another handy function for interacting with Salesforce from Coldfusion. If you don’t know, I’m a huge fan of working with the Salesforce API with Coldfusion using the library by Tom De Manicor http://www.escapekeys.com/blog/index.cfm/.
I’ve been using it so much, and so heavily that here and there I make some small add ons and other neat stuff. This function in particular is one I really like. It allows you to describe an sObject and store all data about the fields in an easy to reference object. So say on your ColdFusion powered website you want to make a picklist with the same values as a picklist in Salesforce, and not have to hard code them, you can describe the sObject, find the fieldname key, and iterate over the values to build your picklist. Of course describe sObject is kind of an expensive call, so I’d recommend caching the results in application or server scope. I generally do this when my application starts and have a manual refresh when needed (how often does the schema of your object change, honestly?). Here is the function.
<cffunction name="CacheSFObject" access="public" hint="GetInfo About an Object from XML and store in an easy to use object"> <cfargument name="ObjectName" type="string" required="yes"> <cfset var Object = server.OSF.describeObject(arguments.ObjectName)> <cfset var ObjectInfo = Object.RawSoap> <cfset var returnStruct = structnew()> <cftry> <cfloop From="1" To="#arraylen(ObjectInfo.Envelope.Body.describeSobjectResponse.result.fields)#" Index="i"> <cfset ObjectFieldReference = ObjectInfo.Envelope.Body.describeSobjectResponse.result.fields[i]> <cfset returnStruct[ObjectFieldReference.name.xmltext] = structnew()> <cfset returnStruct[ObjectFieldReference.name.xmltext].name=ObjectFieldReference.name.xmltext> <cfset returnStruct[ObjectFieldReference.name.xmltext].type=ObjectFieldReference.type.xmltext> <cfset returnStruct[ObjectFieldReference.name.xmltext].label=ObjectFieldReference.label.xmltext> <cfset returnStruct[ObjectFieldReference.name.xmltext].updateable=ObjectFieldReference.updateable.xmltext> <cfset returnStruct[ObjectFieldReference.name.xmltext].calculated=ObjectFieldReference.calculated.xmltext> <cfif trim(ObjectFieldReference.type.xmltext) EQ "reference"> <cfset returnStruct[ObjectFieldReference.name.xmltext].refto=ObjectFieldReference.referenceto.xmltext> </cfif> <cfif trim(ObjectFieldReference.type.xmltext) EQ "picklist" or trim(ObjectFieldReference.type.xmltext) EQ "multipicklist"> <cfset returnStruct[ObjectFieldReference.name.xmltext].values = arraynew(1)> <cfloop from="1" To="#arraylen(ObjectFieldReference.picklistValues)#" index="j"> <cfset returnStruct[ObjectFieldReference.name.xmltext].values[j] = ObjectFieldReference.picklistValues[j].label.xmlText> </cfloop> </cfif> </cfloop> <cfcatch type="any"> </cfcatch> </cftry> <cfreturn returnStruct> </cffunction>
The function does reference the Salesforce component by Tom De Manicor, so you’ll need to download that and have it set up before my function works. You’ll have to change the object reference near the top as well unless you happen to reference it exactly the same way I do. So then to use it, just call
<cfset server.SalesforceFields.Contact = CacheSFObject('Contact')>
Now if you dump out of the contents of server.SalesforceFields.contact you’ll get something that looks like.
So there ya go, another handy function. Hope someone digs this as much as I do 😉
Just another quick handy ColdFusion function here. This one will allow you to take a fifteen char Salesforce ID, and expand it into an eighteen character one. This is useful since if you want to store Salesforce Ids in ColdFusion, you’ll want a none case sensitive version, which is what the 18 character long one is good for. The 15 char one is case sensitive, while the 18 char one is not. So I always work with the 18 char one, since it’s easier.
<cffunction name="ExpandSFID" hint="I take a 15 char salesforce ID and turn it ino the 18 char one"> <cfargument name="SFID" type="string" required="yes"> <cfset var map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345'> <cfset var = arraynew(1)> <cfoutput> <cftry> <cfif len(arguments.SFID) eq 15> <cfset ChunkTotal = 0> <cfset ChunkTotal = 0> <cfset ChunkTotal = 0> <cfloop from="1" to="3" index="i"> <cfloop from="5" to="1" index="j" step="-1"> <cfset c = mid(arguments.sfid, ((i-1)*5)+j, 1)> <cfif reFind("^[A-Z]$", c) GT 0> <cfset ChunkTotal[i] += 2^(j-1)> </cfif> </cfloop> <cfset arguments.SFID &= mid(map,ChunkTotal[i]+1,1)> </cfloop> </cfif> <cfreturn arguments.SFID> <cfcatch type="any"> <cfreturn "Error Converting Salesforce ID #cfcatch.message# #cfcatch.detail#"> </cfcatch> </cftry> </cfoutput> </cffunction>
Hope this helps!
Due to some recent financial challenges, I have decided to throw my hat in the ring as a freelance developer. Simple flat rates, and no BS. I won’t take jobs I don’t think I can do, and I work fast. Below are the rates (may be negotiable). Right now I’m looking for mostly simple projects, not more than maybe 10 hours at a throw, but I’m certainly open to talking about other opportunities.
Coldfusion development: $25.00
Apex/Visualforce development: $35.00
Web Dev (JS, CSS, HTML): $20.00
Salesforce Setup/Admin: $30.00
If you think you have some work for me, visit my contact page for info about how to get a hold of me. Thanks!
<cfset myStruct = structnew()> <cfset myStruct.keyName = "I won't preserve this key's case!"> <!---- this will be all uppercase. Sad face 😦 -----> <cfoutput>#serializeJson(myStruct)#</cfoutput>
<cfset myStruct = structnew()> <cfset myStruct['keyName'] = "I WILL preserve this key's case!"> <!---- this will preserve case. yay! -----> <cfoutput>#serializeJson(myStruct)#</cfoutput>
so even after sending that structure through serializeJson the case of key name in the second example is preserved. Hope this helps someone, cause I know it was driving me crazy for a while!
I’ve been doing some work with phone systems that read text. One issue I hit is that they tend to want to read numbers as…. well numbers. For example an address like 2324 nowhwere street would come out sounding like ‘two thousand twenty-four nowhere street’. While correct, it’s not exactly what a caller wants to hear. So I threw together this quick function that will take a string and convert any numbers into their word equivalent. Hope this helps someone out there.
<cffunction name="numToText" hint="convert a number into text for the IVR system" returntype="string"> <cfargument name="number" type="string" required="yes" hint="A string that contains numbers to convert"> <cfset var returnString = ""> <cfloop from="1" to="#len(arguments.number)#" index="i"> <cfif isnumeric(mid(arguments.number,i,1))> <cfswitch expression="#mid(arguments.number,i,1)#"> <cfcase value="0"> <cfset appendString = "zero"> </cfcase> <cfcase value="1"> <cfset appendString = "one"> </cfcase> <cfcase value="2"> <cfset appendString = "two"> </cfcase> <cfcase value="3"> <cfset appendString = "three"> </cfcase> <cfcase value="4"> <cfset appendString = "four"> </cfcase> <cfcase value="5"> <cfset appendString = "five"> </cfcase> <cfcase value="6"> <cfset appendString = "six"> </cfcase> <cfcase value="7"> <cfset appendString = "seven"> </cfcase> <cfcase value="8"> <cfset appendString = "eight"> </cfcase> <cfcase value="9"> <cfset appendString = "nine"> </cfcase> </cfswitch> <cfset returnString = returnString & " " & appendString> <cfelse> <cfset appendString = mid(arguments.number,i,1)> <cfset returnString = returnString & appendString> </cfif> </cfloop> <cfreturn returnString> </cffunction>
this is just quick script I threw together for moving data from one MSSQL datasource to another. I am currently using this to help migrate a client website from my server to it’s permanent home. It’s a bit slow, so not useful for tables with more than a few hundred rows, but it’s great for the application I am using it for. Maybe it’ll help someone, maybe not. Either way, couldn’t hurt to post it.
<cfsetting requesttimeout="6000"> <cfset SourceDSN = "DS"> <cfset DestinationDSN = "PsaSmart"> <cfset tableName = "Search"> <cfoutput> <cfquery name="GetSource" datasource="#SourceDSN#"> Select * from psasmart.dbo.#tableName# </cfquery> <cfquery name="ClearExistingRecords" datasource="#DestinationDSN#"> delete from #tableName# </cfquery> <cfquery name="AllowIdInsert" datasource="#DestinationDSN#"> set identity_insert #tableName# on </cfquery> <cfset rowCounter = 0> <cfloop query="GetSource"> <cftry> <cfset rowCounter = rowCounter + 1> <cfset form = structnew()> <cfloop list="#GetSource.columnList#" index="colName"> <cfset form[colname] = getSource[colname][rowCounter]> </cfloop> <cfinsert datasource="#DestinationDSN#" tablename="#tableName#"> Row #rowCounter# - ID #id# Inserted<br /> <cfcatch type="any"> Row #rowCounter# - ID #id# Failed #cfcatch.message# #cfcatch.detail#<br /> </cfcatch> </cftry> <cfflush> </cfloop> </cfoutput> <cfquery name="DisAllowIdInsert" datasource="#DestinationDSN#"> set identity_insert #tableName# off </cfquery> Done!
Hey everyone. Here is a handy chunk of code I use to turn any query into a download-able excel spreadsheet. I like it pretty well so I figured I’d share.
<cfsavecontent variable="report"> <table border="0"> <tr valign="bottom"> <cfloop list="#QUERY.columnList#" index="columnName"> <th align="center">#columnName#</th> </cfloop> </tr> <cfloop from="1" to="#QUERY.recordCount#" index="i"> <tr valign="top"> <cfloop list="#QUERY.columnList#" index="columnName"> <td align="left" class="report"> <cftry> #QUERY[columnName][i]# <cfcatch type="any"> NULL </cfcatch> </cftry> </td> </cfloop> </tr> </cfloop> </table> </cfsavecontent> <cfset Reportname = "Your Data.xls"> <cffile action="write" file="C:\Website\Reports\#Reportname#" output="#report#" nameconflict="overwrite"> <a href="http://YourPage/reports/#reportname#">Download Data</a>