Tuesday, September 7, 2010

Two weeks with the bride.

After two weeks of with my bride (for 26 years), it's time to get back to work.

I have a two part problem:
* I want to take snapshots of my Salesforce on demand. My primary need is to backup the data.
* I do not want another database/application infrastructure to maintain.

Using the libraries from SQLForce, I have taken a stab at a the problem (called CopyForce) that introspects any SalesForce instance and creates a local database that matches the Salesforce schema/data. So far the only database back-end I have used is H2 (a good alternative to Apache/Derby).

Any takers for beta testing? The source is open at http://code.google.com/p/sqlforce/

Back to my inbox...only 89 message to go;-)

Tuesday, May 11, 2010

Moving Salesforce Attachments Around

I had an interesting problem (from Portugal) early this morning. A series of attachments had been attached to the wrong account. We wanted to move the attachments to a new account -- NOT BY HAND.
It would be nice is Salesforce supported something like:
  • UPDATE Attachment SET parentId='new' WHERE parentId='old'
Unfortunately Salesforce does not support updating of the parentId field.

Fortunately, the jython SQLForce module made this problem easy to solve. Here is the complete script:

import SQLForce

def getAccountId( session, name ):
    records = session.selectRecords( "SELECT id FROM account where name='" + name + "'")
    if 1 == len(records):
        return records[0].id
    
    return None

if __name__ == '__main__':
    
    session = SQLForce.Session("sandbox")
    srcAccountId = getAccountId( session, "Biota Scientific Management Pty Ltd")
    destAccountId = getAccountId( session, "Biota Structural Biology Laboratory")
    
    print "Src Account", srcAccountId
    print "Dest Account", destAccountId
    
    for att in session.selectRecords( "SELECT id, body, contentType, isPrivate, name FROM attachment WHERE parentId='" + srcAccountId + "'"):
        session.insert( "attachment", ["body", "contentType", "isPrivate", "name", "parentId"],
                        [[att.body, att.contentType, att.isPrivate, att.name, destAccountId]])
        session.delete( "attachment", [att.id] )

Yeah, I should have cached both the updates and deletes into a python array and made two calls to Salesforce instead of two per attachment. What can I say -- I was lazy.

Tuesday, March 16, 2010

Select Records Instead of Arrays

Earlier versions of the SQLForce Jython module support SELECT statements like:
session = SQLForce.Session("sandbox")
for row in session.select( "SELECT id, FirstName, LastName FROM Contact LIMIT 3"):
    print "First Name: " + row[1]
    print "Last Name: " + row[2] 
This works OK as long as each row is referenced in close proximity to the session.select() and not many columns are being selected.

When selecting a lot of columns, I found this technique to be error prone...so I introduced a form of select, session.selectRecords() that returns jython classes where the class attributes are set to the name of each column. Example:

session = SQLForce.Session("sandbox")
for row in session.selectRecords( "SELECT id, name, account.name FROM Contact LIMIT 3"):
    print "Name: " + row.name
    print "Account: " + row.account.name
The only trick to to use the same column name referenced in the SELECT statement. Naturally, a session.selectRecords2(sql, callback) form is also supplied.

That's it..a small change that makes code more readable.

Saturday, February 20, 2010

Multi-line Value Handling with the SQLForce Jython Module

An engineering student, Caleb Smith -- MST (the old Univ of MO, Rolla) found an interesting problem this week where the SQLForce jython module failed. He was searching for errors in the Salesforce Contact table where the complete address was entered in MailingStreet (rather than using the MailingCity, MailingState, etc. fields).

His code was something like:

import SQLForce

session = SQLForce.Session("Sandbox")

for row in session.select( "SELECT id, MailingStreet FROM Contact WHERE MailingStreet!=null"):
    if isStreetInvalid( row[1] ):
        reportTheError( row[0], row[1] )

Looks simple enough -- but it failed because the jython code was assuming NO EMBEDDED NEW LINES in any of the returned columns. Multi-line addresses were processed as additional rows.

If run against SQLForce 1.0.3 (or above) Caleb's loop runs like he expected originally. Multi-line addresses are stored in row[1] as multi-line strings.

Underneath the Hood

Underneath the hood, the Jython Session.select() was changed to always replace the SELECT that occurs at the beginning of the SOQL with a SELECTX. SELECTX works just like the SELECT command except that the results are returned as simple XML. Jython parses the XML using SAX to build each row. Simple!

Thanks for the bug Caleb.

Tuesday, February 16, 2010

Is There Junk in My Salesforce Pricebooks?

I had an interesting problem today: Find products in my Salesforce pricebooks that we have never sold.. In my case I want to delete these products from Salesforce (that's another post).

Finding the products was easy. I wanted all products where:

  • No Opportunity referenced the product.
  • No Asset referenced the product

Here is the script:

import SQLForce

session = SQLForce.Session("sandbox")

for product in session.select("SELECT id,name, division__c FROM Product2 ORDER BY name asc"):

    nOps = session.select("SELECT COUNT() FROM OpportunityLineItem WHERE PriceBookEntry.product2Id ='" + product[0]+ "'")[0][0]

    nAssets = session.select("SELECT COUNT() FROM Asset WHERE product2Id='" + product[0]+ "'")[0][0]

    pricebooks = []

    for pb in session.select("SELECT DISTINCT Pricebook2Id FROM PricebookEntry WHERE Product2Id='" + product[0]+ "'"):
        pricebooks.append(pb[0])

    if "0"==nOps and "0"==nAssets:
        notUsed = [product[0],product[2],product[1], nOps, nAssets, "\t".join(pricebooks)  ]
        print "\t".join(notUsed)

I put the results of the script into an Excel worksheet so marketing can look for mistakes. Later I will modify the script to delete the records I found.

Thursday, February 11, 2010

Jython and Salesforce

A few weeks ago I launched a open source version of a Jython module (SQLForce) for working with Salesforce (the base functions are in a jar). Here's a problem solved earlier this week.

We just started using  Salesforce forecasting subsystem and needed to initialize Opportunity.ForecastCategory based on two Opportunity custom fields. Hundreds of records had to be updated -- doing it by hand was not a good option.

The solution...the following short SQL Force script:

import SQLForce

session = SQLForce.Session("sandbox")
nUpdated = 0

##
## Case where ForecastCategory should be Best Case
##
for record in session.select("""SELECT id, forecastCategory FROM Opportunity 
     WHERE (forecastCategory='Commit' OR forecastCategory='Pipeline')
     AND isClosed=false 
     AND include_in_estimate__c=false 
     AND include_in_upside__c=true  
     AND license_type__c!=null
     AND closeDate > LAST_YEAR
     """):
 nUpdated += 1
 print nUpdated
 session.runCommands("UPDATE Opportunity SET forecastCategoryName='Best Case' WHERE id='" + record[0] + "'" )


There were a couple of addition loops in the code for setting other Opportunity.ForecastCategory values. Looks useful? See SQLForce Project on code.google.

Friday, August 28, 2009

SalesForce Java Migration Tools for Lots of Records

For a recent SalesForce migration, we developed a strongly typed java toolkit based on SFDC's WSDL.  We have found the the kit highly resusable:
  • To save us a lot of time, we wrote a code generator (template based) for creating strongly typed access classes to any SObject.
  • Connection pooling.
  • Since I am a unit test coverage fanatic (I love Lemma (the ECLemma variant)) the code is we exercised.
  • Optional Eclipse framework  exposes the capabilities. We use this for one-off tasks because we can easily push solutions to end-users.
  • In our migration effort, we transferred the contents of a  30GB  SFDC into a 2nd SFDC database during a long Friday evening.
Anyone trying to modify a lot of records in SFDC (using Apex) quickly runs into governor limits.   You can beat this with clever Apex/PageReference programming in many cases but the result is brittle code.  A better solution is often a toolkit that hides many of the governor limits and is written in a language with a solid eco-system.