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.

Here's an interesting problem. Sales wanted an excel spreadsheet that would help them call Salesforce contacts that have purchased products. That's easy enough -- Contacts JOIN Assets on AccountId.
Here's the hard part: Sales wanted the products purchased as:
- 1 column per product
- Put the # of seats in the row for each contact (or blank if not purchased).

If any RDBMS this is a fairly simple task using the RDBMS specific PIVOT operation. Excel could be used to create the pivot from raw data. In either approach the solution is not simple enough (especially since the resultant spreadsheet had around 100,000 rows and 80 columns).

I solved this problem in 3 minutes with a single MS/SQL query. How? I maintain a mirror of my Salesforce production instance using the CopyStorm product.  My mirror is within 24 hours of production salesforce data (my choice -- I could have selected within 15 minutes).

Here's the query (I masked some of the data in the query).


use salesforce

SELECT * FROM
(
SELECT
ACCT.name AS accountName, CONTACT.name as ContactName
,PROD.name AS productName, ASSET.number_of_users__c AS nUsers
,ASSET.purchaseDate

FROM
Account AS ACCT
JOIN Contact AS CONTACT
ON CONTACT.accountId=ACCT.id

JOIN Asset as ASSET
ON ASSET.accountId = ACCT.id

JOIN Product2 AS PROD
ON PROD.id = ASSET.product2id
WHERE
ASSET.purchaseDate IS NOT NULL
AND PROD.family LIKE 'FamilyName%'

) AS RAWDATA
PIVOT(
SUM(nUsers) FOR productName IN ([Product A], [Product B], [Product C], [Product D])
) pvt

SQLForce Saves Marketing Many Hours

Marketing had an interesting problem last week.

Marketing: We want to create a price book just like the "2012 Standard Price Book" but only include USD prices. Can we just clone the "2012 Standard Price Book" and have you fix it.

Any guesses about how much work it was to "fix" the cloned price book?  In sqlforce it took a single line:

SQLForce> DELETE FROM pricebookentry WHERE pricebook2.name='Distributor Price Book - 2012' and currencyIsoCode<>'USD';

Yes, using the DELETE statement is dangerous. The wrong WHERE clause (or no WHERE clause) can quickly destroy a lot of data. However, judiciously used, DELETE can save a lot of time.

Salesforce Rich Text Fields -- Problem Solved

I had an interesting problem this morning. An account rep had a small table in MS/Word that he wanted to pasted into a Salesforce rich text field.

Big Problem: Though the table was small all of the style crap added by MS/Word exceed the columns' 32,000 character limit for rich text fields. You think this would be an easy problem to solve?

Here is what I had to do:

First attempt: 
I saved the MS/Word as filtered HTML – too big.

Second attempt: 
I used CKEditor to create new html – too big.

Third attempt: 
I hand crafted the HTML, visualized it in Chrome, and then did a copy/paste from Chrome to SFDC – still too big. The SFDC gadget transformed my short HTML into something bigger than 32K. Odd behavior – SFDC sandbox worked; production SFDC did not. They must be updating the SFDC gadget in Winter’11.

 Final attempt:
 I wrote a one-line sqlforce UPDATE statement and patched the field. Finally, success.

• UPDATE Quote SET SpecialInstructions__c='....' WHERE id='00a88789a7s89af'

I love sqlforce. 

Upgrade to Summer'11

The SQLForce, CopyForce, and JavaForce updates to the Summer'11 protocol (22.0) have been posted to code.google. Other than support for a new SFDC column type and new proxy command line args there is nothing new to report.

I've been having problems getting CopyForce to work consistently thru proxy servers on Linux.
Are there any guru's reading that can throw me some same sforce connection code? On Windows it was easy to make the app use the system proxy and certificate store. I am having no such luck with Linux.

The next upgrade to CopyForce will dynamically alter the RDMBS schema when new columns are added (or existing columns are altered) in force.

Thanks for all of the downloads and feedback. Feedback is the only $$ for this job. I appreciate it a lot.

It's too hot to code. MySQL Anyone?

CopyForce (and CopyStorm -- the commercial variant) does not support MySQL (I have not needed it on a project). This morning I took a look at the effort....the effort looks small. In a couple of hours I was exporting Salesforce data.

A question: Is there enough MySQL interest to make it worth my effort? Please drop me a line if you would be interested in trying it out. If few people respond, I probably will not launch a MySQL version.

Since it is summer and time for outdoor fun, progress on a WAR that provides browser access to CopyForce data has slowed. Though the individual record pages and list of record pages are in place, I will need some concentrated time to bring it to beta state. Maybe this fall.

It's time to take a swim (it's hot outside).

Limits of SOQL -- SQLForce Solution

One aspect of SOQL that is a bit annoying is its restrictions of where column names can appear in a query. A query I have want to run a few times:

SELECT id FROM Account WHERE ShippingCountry != BillingCountry

Bummer....SOQL does not allow column names on the right side of an expression.

Recently a user reported a bug with SQLForce that is caused by the same SOQL limitation. He report that a SQLForce query like:

UPDATE Account SET ShippingCountry = BillingCountry WHERE ShippingCountry=null

set all ShippingCountry values to the string "BillingCountry." Why? Since SOQL does not have a native UPDATE statement, SQLForce simulates it using a SELECT followed by an Update() call. Since SQLForce knows that SOQL does not allow column names on the right, it assumes that the user forgot to quote a string in the UPDATE statement.

How can this problem be approached in a couple of lines of code. The answer for me is with the SQLForce Jython module. I'll post a typical solution tomorrow.