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.

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.

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.