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.

Coldfusion, Angel.com, Google Maps Directions, and You!

Okay, so even the title is a mouthful, this post is probably going to be insane you are thinking. Well… maybe, but it’s cool stuff. So picture this. You are using Angel.com as an IVR provider. So people call in and talk to a phone machine for data. Now say you want to give directions over the phone. Say you want those directions to be dynamic, and given to the user step by step. So for example you are hosting an event. People pre-register for this event, and have provided their address, which you have stored in a database. Bob Johnson calls in (he has registered and provided his address before) and wants directions from his house to your event center. You might think you’d need a live person to do this. Blasphemy! Have Bob authenticate so we can find his address in the database. Feed that address into an Angel.com variable (if there were a better way to enter addresses over the phone, you wouldn’t even need to pre-register, but because entering data in phones sucks we kind of need their address to already exist somewhere we can get it). Once that variable is in Angel.com, pass it, along with the destination to this tool via URL arguments. This tool will then give step by step directions that the IVR can read aloud back to Bob. He even has the ability to replay each direction, and navigate backward and forward through the steps.

Just copy and paste this and run host it on a ColdFusion server somewhere. It’s ready to be called with all configs just being passed in the URL at runtime.

<cfparam name="url.start" type="string" default="1405+Olive+Ln+N,+Plymouth,+Hennepin,+Minnesota+55447">
<cfparam name="url.end" type="string" default="1111+Cambridge+St.+Hopkins,+MN+55343+(White+Castle)">
<cfparam name="url.stepID" type="integer" default="1">

<!---- The id of the page that calls this webservice in angel.com ---->
<cfparam name="url.thisPage" type="string" default="1">

<!---- the id of the page to go to if this thing errors for some reason --->
<cfparam name="url.failPage" type="string" default="2">

<!---- the id of the page to go when we are all done giving directions ---->
<cfparam name="url.nextPage" type="string" default="3">

<!---- the id of the page to go to if we just can't find directions or a route ---->
<cfparam name="url.reEnterInfoPage" type="string" default="4">

<!---- the id of the page to go to if the person decides they want to talk to a person ---->
<cfparam name="url.transferToCC" type="string" default="5">

<!---- the id of the page to go to if the person wants to hang up---->
<cfparam name="url.disconnectPage" type="string" default="6">

<cfparam name="XMLData" type="string" default="">
<cfparam name="text" type="string" default="">

<!--- Format the directions for sending to google --->
<cfset url.start = replacenocase(url.start, " ", "+")>
<cfset url.end = replacenocase(url.end, " ", "+")>

<cfhttp url="http://maps.google.com/" result="KMLData">
    <cfhttpparam name="saddr" value="#url.start#" type="url">
    <cfhttpparam name="daddr" value="#url.end#" type="url">
    <cfhttpparam name="output" value="kml" type="url">

        <cfset XMLData = xmlParse(KMLData.FileContent)>
        <cfset totalNumberOfSteps = arraylen(XMLData.kml.Document.XmlChildren)-4>
        <cfset text = XMLData.kml.Document.XmlChildren[stepID+3].XmlChildren[1].XmlText>
        <cfset text = text&" . .">
        <!--- Some extra text formatting for reading over the IVR. You can easily add more abbreviations here if there
              are some I forgot --->
        <cfset text = replacenocase(text, " LN ", " Lane ")>
        <cfset text = replacenocase(text, " BLVD ", " Boulevard ")>
        <cfset text = replacenocase(text, " RD ", " Road ")>
        <cfset text = replacenocase(text, " ST ", " Street ")>
        <cfset text = replacenocase(text, " Ave ", " Avenue ")>
        <cfset text = replacenocase(text, " NW ", " North West ")>
        <cfset text = replacenocase(text, " NE ", " North East ")>
        <cfset text = replacenocase(text, " SE ", " South East ")>
        <cfset text = replacenocase(text, " SW ",  "South West")>            
        <cfset text = replacenocase(text, " N ", " North ")>
        <cfset text = replacenocase(text, " E ", " East ")>
        <cfset text = replacenocase(text, " W ", " West ")>
        <cfset text = replacenocase(text, " S ",  "South ")>

        <cfsavecontent variable="PromptMessage">
            <cfif stepID LT totalNumberOfSteps>
                Press 1 for the next direction. Press 2 to repeat this direction. Press 3 to hear the previous direction.
                    You have reached your destination.
                    Press 1 to disconnect. Press 2 to repeat this direction. Press 3 to hear the previous direction.
        <cfif stepID LT totalNumberOfSteps>
            <cfsavecontent variable="XMLLinks">
                <LINK dtmf="1" returnValue="#stepID+1#" destination="#url.thisPage#" />
                <LINK dtmf="2" returnValue="#stepID#" destination="#url.thisPage#" />
                <LINK dtmf="3" returnValue="#stepID-1#" destination="#url.thisPage#" />
                <cfsavecontent variable="XMLLinks">
                    <LINK dtmf="1" returnValue="#url.nextPage#" destination="#url.thisPage#" />
                    <LINK dtmf="2" returnValue="#stepID#" destination="#url.thisPage#" />
                    <LINK dtmf="3" returnValue="#stepID-1#" destination="#url.thisPage#" />
        <cfset counter = 1>
        <cfset VariablesObject[counter] = structnew()>
        <cfset VariablesObject[counter]["Name"] = "totalDirections">
        <cfset VariablesObject[counter]["Value"] = totalNumberOfSteps>


        <cfcatch type="any">
            <cfset promptMessage = "Sorry we couldn't find a route with the information supplied. Press 1 to try a different direction method. Press 2 to disconnect, or press 3 to be transferred to customer care.">
            <cfsavecontent variable="XMLLinks">
                <LINK dtmf="1" returnValue="#reEnterInfoPage#" destination="#failPage#" />
                <LINK dtmf="2" returnValue="#disconnectPage#" destination="#url.thisPage#" />
                <LINK dtmf="3" returnValue="#transferToCC#" destination="#url.thisPage#" />

            <cfset VariablesObject[1] = structnew()>
            <cfset VariablesObject[1]["Name"] = "ErrorType">
            <cfset VariablesObject[1]["Value"] = cfcatch.Type>        

            <cfset VariablesObject[2] = structnew()>
            <cfset VariablesObject[2]["Name"] = "ErrorMessage">
            <cfset VariablesObject[2]["Value"] = cfcatch.Message>

            <cfset VariablesObject[3] = structnew()>
            <cfset VariablesObject[3]["Name"] = "ErrorDetails">
            <cfset VariablesObject[3]["Value"] = cfcatch.Detail>                                    
    <cfset ReturnObject = printQuestionReturnVariables('stepID',PromptMessage,XMLLinks,url.failPage,VariablesObject)>

<cffunction name="printQuestionReturnVariables" access="remote" hint="Print Question Data For Angel IVR with returnable variables">
    <cfargument name="varName" default="none" type="string">
    <cfargument name="promptMessage" default="none" type="string">
    <cfargument name="linkMessage" default="none" type="string">
    <cfargument name="failPage" default="failPagePlaceholder" type="string">
    <!--- This is an array of structures, with keys "name" and "value" --->
    <!--- EX variables[1].Name = Gender --->
    <!--- EX variables[1].Value = Male --->
    <cfargument name="variablesToInclude" default="" type="any" required="no">
    <!--- create, scope and set the loop counter variable used below --->
    <cfset var i = 0>
        <cfsavecontent variable="ReturnMessage">
                <QUESTION var="#ucase(varname)#">
                        <PROMPT type="text">
                    <ERROR_STRATEGY type="nomatch" reprompt="true">
                        <PROMPT type="text"> Sorry I did not get that. </PROMPT>
                        <PROMPT type="text"> I still did not get that. </PROMPT>
                        <PROMPT type="text"> Since I am having so much trouble; please hold while I transfer you to a customer representative who can better serve you. </PROMPT>
                        <GOTO destination="/25" />
                    <ERROR_STRATEGY type="noinput" reprompt="true">
                        <PROMPT type="text"> Sorry I did not get that. </PROMPT>
                        <PROMPT type="text"> I still did not get that. </PROMPT>
                        <PROMPT type="text"> Since I am having so much trouble; please hold while I transfer you to a customer representative who can better serve you. </PROMPT>
                        <GOTO destination="/25" />
                <cfif IsArray(arguments.variablesToInclude)>
                        <cfloop from="1" to="#arraylen(arguments.variablesToInclude)#" index="i">
                                <cfif structkeyexists(arguments.variablesToInclude[i],'Name') and  structkeyexists(arguments.variablesToInclude[i],'value')>
                                    <VAR name="#ucase(arguments.variablesToInclude[i]['Name'])#" value="#arguments.variablesToInclude[i]['Value']#" />
                                <cfcatch type="any">
                                    <cfset ErrorData = structnew()>
                                    <cfset ErrorData.Error = cfcatch>
                                    <cfset ErrorData.Arguments = arguments>
                                    <cfset ErrorData.form = form>
                                    <!--- You might wanna email this data to yourself or something --->

    <cfreturn ReturnMessage>

So really what’s happening here is that the page gets called with some URL variables. Those variables are used to construct a google maps http request. That request actually prints out XML. We take the XML and clean it up a little bit and format it. Then print it off in a nice Angel XML package so it can be read by the system. The page just provides one step at a time, and uses a recursive style setup to just continually give directions until there are no more.

If you just want to use the step by step direction giving, you can of course easily remove all the Angel XML junk and just access the #return# variable and do whatever you like with it. This could easily be adapted to an online direction giver, or for Twilio, SMS, whatever. For now it is ColdFusion based, but I may try and convert it to an Apex class in the not too distant future. Depending on how the user is interacting with this, you could even remove the need to have the address pre stored. You could for example have a dedicated number where users just text their current address and you text them back step by step directions to your office or whatever. Really the sky is the limit here.

Here is a sample to see how it works. The demo has some extra stuff to make it more “person usable” instead of stripped down to be consumed by a computer.
Example of ColdFusion/Google Maps/Angel.com dynamic directions

Anyway, hope you guys think this is cool. It was a lot of fun to write!

Coldfusion describe sObject function

Hey all!

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()>
            <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 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>
        <cfcatch type="any">
    <cfreturn returnStruct>

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.

Sample of contact describe dump

Sample of contact describe dump

So there ya go, another handy function. Hope someone digs this as much as I do 😉

Coldfusion Expand Salesforce object Id

Hey all.
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)>
        <cfif len(arguments.SFID) eq 15>
            <cfset ChunkTotal[1] = 0>
            <cfset ChunkTotal[2] = 0>
            <cfset ChunkTotal[3] = 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)>
                        <cfset arguments.SFID &= mid(map,ChunkTotal[i]+1,1)>      
            <cfreturn arguments.SFID>
        <cfcatch type="any">
            <cfreturn "Error Converting Salesforce ID #cfcatch.message# #cfcatch.detail#">

Hope this helps!

Freelancing Services Available

Hey everyone.
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.

Rates (Hourly)
Coldfusion development: $25.00
Apex/Visualforce development: $35.00
Web Dev (JS, CSS, HTML): $20.00
Salesforce Setup/Admin: $30.00
Consulting: $50.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!

Coldfusion serializeJson preserve case

Hey this is just a quick tip for any of you ColdFusion programmers who may be interacting with javascript while using JSON. ColdFusion being a non case sensative language has an annoying habit of changing the case of your structure key names when being fed through the serializeJson function, or most other functions that translate ColdFusion datastructures. The official statment is that you just need to change your javascript to expect all uppercase key names, but in some cases you cannot do that. There is a workaround however. Instead of declairing your structure keys like

<cfset myStruct = structnew()>
<cfset myStruct.keyName = "I won't preserve this key's case!">
<!---- this will be all uppercase. Sad face 😦 ----->


<cfset myStruct = structnew()>
<cfset myStruct['keyName'] = "I WILL preserve this key's case!">
<!---- this will preserve case. yay! ----->

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!

Coldfusion number to text string function

Hey all,

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 value="1">
                    <cfset appendString = "one">
                <cfcase value="2">
                    <cfset appendString = "two">
                <cfcase value="3">
                    <cfset appendString = "three">
                <cfcase value="4">
                    <cfset appendString = "four">
                <cfcase value="5">
                    <cfset appendString = "five">
                <cfcase value="6">
                    <cfset appendString = "six">
                <cfcase value="7">
                    <cfset appendString = "seven">
                 <cfcase value="8">
                    <cfset appendString = "eight">
                <cfcase value="9">
                    <cfset appendString = "nine">
                <cfset returnString = returnString & " " & appendString>
                 <cfset appendString = mid(arguments.number,i,1)>
                 <cfset returnString = returnString & appendString>
    <cfreturn returnString>

Simple Coldfusion MSSQL Data Export/Import

Hey all,
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">


<cfquery name="GetSource" datasource="#SourceDSN#">
    Select * from psasmart.dbo.#tableName#

<cfquery name="ClearExistingRecords" datasource="#DestinationDSN#">
    delete from #tableName#

<cfquery name="AllowIdInsert" datasource="#DestinationDSN#">
    set identity_insert #tableName# on

<cfset rowCounter = 0>

    <cfloop query="GetSource">
            <cfset rowCounter = rowCounter + 1>
            <cfset form = structnew()>
            <cfloop list="#GetSource.columnList#" index="colName">
                <cfset form[colname] = getSource[colname][rowCounter]>
            <cfinsert datasource="#DestinationDSN#" tablename="#tableName#"> 
            Row #rowCounter# - ID #id# Inserted<br />
            <cfcatch type="any">
                Row #rowCounter# - ID #id# Failed #cfcatch.message# #cfcatch.detail#<br />

<cfquery name="DisAllowIdInsert" datasource="#DestinationDSN#">
    set identity_insert #tableName# off


Turn a query into an excel spreadsheet

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 from="1" to="#QUERY.recordCount#" index="i">
                <tr valign="top">
                    <cfloop list="#QUERY.columnList#" index="columnName">
                         <td align="left" class="report">
                                <cfcatch type="any">
    <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>