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.
