Wednesday, November 27, 2013

How to Destroy a Salesforce Instance in 1 minute.

In the past few months I've been working on a tool that restores all or part of a Salesforce sandbox from a backup. Naturally I needed to restore the same sandbox over and over to debug the tool.

Hum...sandbox refreshes can occur at most once per day (developer sandbox). This note is about how I refreshed my sandbox dozens of times per day.

Here's the script:

#!/bin/sh
java -jar sqlforce.jar <<!
connect production gsmithfarmer2@gmail.com mypassword
echo Clearing gmsmithfarmer2 production
DELETE FROM Case WHERE id <> null;
DELETE FROM Opportunity WHERE id <> null;
DELETE FROM Lead WHERE id <> null;
DELETE FROM Campaign WHERE id <> null;
DELETE FROM Account WHERE id <> null;
echo Finished clearing gsmithfarmer2 production
!

The script uses the free SQL/Force tool from www.capstorm .com to do a lot of damage to my salesforce instance in just a few lines of code. Dangerous - yes. Convenient -- yes.

The SQL/Force tools also includes jython integration -- with this you do lots of wacky things to Salesforce data in just few lines of code.

Thursday, November 21, 2013

Here's an important bit of SOQL "SELECT * FROM Lead" trivia I bet you do not know...

Quick quiz: If you run the SOQL statement:

  •  SELECT id, LastModifiedDate FROM Lead WHERE LastModifiedDate
will it ever return records whose LastModifiedDate is TODAY?

If your answer is "No" then you are wrong. In fact it is pretty simple to make the SOQL return lots of records from TODAY. Here's how.

  1. Start the SOQL statement
  2. Modified one or more Lead records that match the SOQL criteria BUT have not yet been returned by the query.
  3. The query will return the modified records WITH LastModifiedDate value outside the range of the SOQL statement.

I discovered this "feature" working with a customer who has a highly active Lead table (upserts every couple of seconds). Once in a blue moon a lead record would be copied to SQL/Server even though it did not meet the criteria of the SOQL (the app was CopyStorm -- www.capstorm.com). Though it took a while to figure out the problem (since it appeared to be random and rare), reproducing it was easy once the root cause was understood.

Thursday, July 18, 2013

How to Extract Attachments from Salesforce

This week the "Salesforce Power User" board had a question about how to easily extract attachments so I took 10 minutes and coded up a solution. The solution uses www.capstorm.com's free SQLForce/Jython tool that has been around for years. I use it with clients every day to write one-off data manipulation scripts and for scheduled jobs that scrub and report on SFDC data. Every admin with scripting skills should have this tool in their back pocket.

Enough self-promotion...here's the solution...
-----------------------------------------------------------------------------------------------------

"""
Extract attachments from Salesforce and put them into regular files.
"""

import SQLForce
import os
import base64

##
## Put all attachments into this directory. A subdirectory will be created for each
## unique parent id. Attachments will be named the same as the "name" field in Salesforce.
##
outputDir="C:/tmp/attachments"

##
## SOQL that find the parent record ids where we want the attachments.
## Change this variable to find the owner's of attachments you want to extract.
##
attachmentParentSOQL="""
    SELECT id FROM Account WHERE name LIKE 'Z%' LIMIT 5
"""

if not os.path.exists(outputDir):
    os.makedirs(outputDir)

def extractAttachments( session, parentIds, outputDir):
 
 
    extractSOQL = "SELECT id, parentId, body, contentType, isPrivate, name FROM attachment WHERE parentId IN ('" + "','".join(parentIds) + "')"
    for rec in session.selectRecords(extractSOQL):
        myDir = os.path.join(outputDir, rec.parentId)
        if not os.path.exists(myDir):
            os.makedirs(myDir)
         
        fh = file(os.path.join(myDir, rec.name), 'wb')
        fh.write( base64.b64decode(rec.body))
        fh.close()
        print rec.name

 
session = SQLForce.Session("sandbox")

parentIds = []
for rec in session.selectRecords(attachmentParentSOQL ):
    parentIds.append( rec.id )
    if len(parentIds) > 200 :
        extractAttachments( session, parentIds, outputDir )
        parentIds = []

if len(parentIds )>0:
    extractAttachments( session, parentIds, outputDir )
 

Friday, March 15, 2013

Quick Opportunity Change Owner Script

As sales people come and go I am often asked to change opportunity ownership status using a variety of criteria. Often I could do the change with the Salesforce UI but a find running a simple SQLForce a lot quicker (and way more adaptable).

I have used the following core script for years -- all I have to do modify the "sqlWHERE" definition in the code when a new situation arises.  Believe me, typing "jython

import SQLForce

def _getOwnerId( session, alias ):
    return session.select("select id from user where alias='" + alias + "'")[0][0];
               
if __name__ == '__main__':
    session = SQLForce.Session("production")
   
    newOwnerAlias = "newSalesperson"
    oldOwnerAlias = 'oldSalesperson"

   
    newOwnerId = _getOwnerId( session, newOwnerAlias )

    sqlWHERE = "owner.alias='" + oldOwnerAlias + """'
                AND isClosed=false
                AND closeDate >= 2012-01-01
                """
               
    updateRecords = []
    for record in session.selectRecords( "SELECT id from Opportunity WHERE " + sqlWHERE ):
        updateRecords.append( [ record.id, newOwnerId ])

       
    if len(updateRecords) > 0:
        print "Time to Update ", len(updateRecords)
        session.update( "Opportunity", ["ownerId"], updateRecords )
    else:
print "No records modified"


Note: SQLForce is freeware at www.capstorm.com

Tuesday, March 12, 2013

Deep Account Profiles -- Beyond Apex

Sales gave  me an interesting problem lat week:

  • Given a pattern that matches one or more account names show the matching accounts, all child accounts, and asset/contact/case/opportunity status/history.
  • Give me the results in Salesforce.
Wow! Given the SFDC query governors I knew there would be no way of delivering a solution using APEX.  The query limits would be blown before I was halfway thru some of the multi-nationals we serve.

First the results:

The user can enter a number of parameters at the top of the screen. In the example a query of "*Queensland" as entered as the account. The tool found all accounts containing "Queensland" and all children of the accounts. They are arranged in in a hierarchy in the top table (along with some summary data).  The links in the top table jump to the details for each account. Other links on the page link to the corresponding Salesforce record.

How Was It Done?

The first step was easy. Get the data out of Salesforce into a RDBMS so the queries will run fast without Salesforce governor limits. We have used Capstorm's CopyStorm product to maintain an "almost live" copy of Salesforce in SQL/Server for several years (it runs as a cron job and keeps our SQL/Server up to date).

The second step was to develop the SQL queries.This step was quick because the tools were developed in SQL/Server -- fast turn around time. Here's the query that finds active assets for an account.

SELECT PROD.name as Product, ASSET.number_of_users__c as NumUsers, PROD.licenseType__c as LicenseType
    , PROD.licenseKeyMethod__c as KeyMethod, PROD.industry__c as Industry
    ,  ASSET.purchaseDate as PurchaseDate, ASSET.usageEndDate as UsageEndDate, ASSET.licenseKeyIssueDate__c AS KeyIssueDate
    , LIC.name as License
    , ASSET.id as assetId, LIC.id as licenseId
    FROM salesforce.Asset AS ASSET JOIN salesforce.Product2 as PROD ON ASSET.product2id=PROD.id
    JOIN salesforce.Software_License__c AS LIC ON ASSET.licenseId__c=LIC.id
    WHERE (ASSET.usageEndDate >= GETDATE() )
ORDER BY ASSET.usageEndDate DESC, PROD.name ASC

The third step was to develop a simple servlet that returned the results of a query in JSON format. This part took the most time -- a couple of hours.

The fouth step was to consume the servlet JSON in a bit of html/javascript/JQuery and create the web page you see a the top of this post.  Fiddling with the css took the most time.

The final step was to create a Visual force page to hold the HTML page from the previous step. This is the step that makes the tool look like it is part of salesforce. This step was surprisingly easy (the greater/less than  characters have been replaced with [] to make the code easier to paste in the doc).


[apex:page ]
[!--
Access to the account profile tool that lives inside the firewall.
Note: It works on salesforce data that is a day old.

 --]
[script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js"][/script]
[script language="javascript"]
$(document).ready( function() {
var height = $("body").height();
$("#dmzReport").css('height', height + "px" );
});


[/script]
[iframe id="dmzReport" height="600px" width="100%" scrolling="true"
src="http://mysecret.com/superman/SalesforceReportServer/AccountProfile.html"
][/iframe]
[/apex:page]

The Moral of the Story
Having a "hot" copy of your Salesforce data in a RDBMS opens up all kinds of  options that are just not possible (or affordable) with straight Salesforce/Apex.  Rather than spending hours working around SFDC governor limits or dealing with SFDC batch jobs a RDBMS copy of your data let's you concentrate on solving the business problem at hand.

The cost of maintaining our "hot" copies of SFDC is cheap (we maintain both MySQL and SQL/Server copies). In our case the work is done by a simple cron job -- our effort is zero.