CEO wins Appy Award at Dreamforce 2008

So it occurs to me that I shouldn't be so humble and should announce the fact that my organization, Center for Employment Opportunities, received the "Power of Us" Appy award at Dreamforce 2008, the annual Salesforce conference. Having done the bulk of the customization in Salesforce for CEO, this was a proud moment for myself, and it was great getting some recognition for the organization as well. CEO is just starting to provide Salesforce consulting services so recognition from Salesforce that we've created a best-in-class solution goes a long way in proving to our potential clients that we have the skills and experience to help them.

For my die-hard fans, you can watch the Appy award presentation here, it's about 23 minutes in.

Salesforce and Crystal Reports

And specifically how they don't play nice together. I've used Crystal Reports for many years now, and as far as reporting tools for relational databases go, I've always been happy with it. The problem being, Salesforce isn't truly a relational database and while it's great for data input, has never been well optimized for data output. Crystal has attempted to create a data connector for Salesforce, and I've been able to do a lot with it, but it's still fraught with problems. I've also come to have issues with the company itself, which is now SAP, but I'll get to that.

The problems. First off, while they give the impression that you can build a report on Salesforce data the same as you would on any other database, that's not entirely true. Like I said, Salesforce isn't really relational (it's relational-ish, perhaps?) so the standard methods of relating tables and defining filters in the Select Expert doesn't always produce what you're expecting. I haven't fully put my finger on what works and what doesn't, and I'm disinclined to do much in the way of QA for Crystal, but I often find that filters I've defined in the Select Expert have to essentially be replicated by suppressing groups or detail sections of records that shouldn't have been returned in the first place based on my criteria. Pay very close attention to results returned where your Select Expert criteria contains a lot of "and"s and "or"s, that's where I hit the most problems.

Then there are the issues with efficiency. Anyone who has developed reports on a relational database knows that complex reports can take a long time to run, hence the rise of OLAP (which I really need to learn more about). Well, if Salesforce is your data source, then expect the time to run a report to increase exponentially. When you include multiple Salesforce objects and define filters on what data to return in the Select Expert, you often end up pulling in a lot more data than you really need because of the limitations of Salesforce's SoQL language and Crystal has to then process what should be displayed. The workaround to this is to write Command statements in SoQL to pull the data from Salesforce wherever possible instead of pointing CR to multiple Salesforce objects. But part of the advantage of a tool like CR is supposed to be the GUI interface, so it's frustrating always having to write long, complex SoQL statements for reports that shouldn't really be that complicated.

Then there's the limitations of Salesforce itself that impact Crystal. Any other database, you'd have the ability to write SQL statements to do aggregate functions on the data (max, min, count, etc) and then let that processing happen on the database server itself and just return the results. But SoQL isn't that robust. A common request I get is to see the last time a participant (Contact) met with a JD, or the first time they worked transitional work after graduating from Life Skills class. I have 2 options to accomplish this. I can create a roll-up summary field in Salesforce and then use that field to pull in details about the record associated with that roll-up summary field. So in my first example, I create a roll-up summary field in the Contact record for the maximum Event date where the Event Record Type = "JD Meeting". Then if I want more information than just that date, like who the meeting was with, I have to relate the Contact table with the Event table on both the Contact Id and that roll-up summary date field. Kind of a long way to get not very far.

But even that roundabout solution for aggregates is not always an option, because the roll-up summary fields don't allow you to filter data based on another field. So in my second example, it's not enough to get the first time someone attended transitional work ever, I need to see the first time they attended transitional work after they've graduated from class, and our participants sometimes attend class multiple times over many years. I can't create a roll-up summary field for that, so my only option is to pull every single transitional work attendance record into the report, group it by participant, filter it for dates that are greater than their last class graduation (which has to happen in Crystal, and not in Salesforce, for the same reason as the roll-up summary limitation) and then only display the first record, hiding all the rest. That's a lot of data being pulled into Crystal, when I ultimately only need about 1% of it.

So I realize all of that was a bit wonky, but anyone who's done reporting should get the gist of it.

Now I have a number of reports designed in Crystal that can't run on crystalreports.com because they take so long to run that they time out. So despite this great on-demand system we have, I still have users calling me to run reports periodically, just what we were trying to get away from. Then further complicate this by crystalreports.com having unexpected outages 3 times during business hours in the last 6 months, and users running operational reports (like payroll) who can't wait for that crap, call me then. And ever since SAP took over Crystal, there's no more phone or email support, even something as catastrophic as their entire system being down has to be posted on a forum and then you just sit back and hope one of their techies is paying attention. (They claim they have email support, but don't believe it, the support people just email you and direct you to that forum). Very annoying, very unreliable, very unprofessional.

So what now? I'm still trying to figure that out. At the recent Dreamforce conference, I explored many reporting solutions. Everyone's answer is similar though- yank all of the data out of Salesforce into a data warehouse, either on your servers or theirs, and then do the reports on that. So I'm looking into the solutions that include data warehousing as part of an on-demand service so we can keep moving in the direction of cloud computing. They're all a bit young though, and I've now been burned by 2 supposed Salesforce reporting solutions (Crystal and Jasper Reports) so I'm wary about moving too quickly. I'm hoping to just hold things together long enough to let some of the bigger players test out available solutions and get them past the 1.0 stage. More to come on this I'm sure.

Scheduling Code in Salesforce

Update 12/15/11: It's worth noting to anyone stumbling onto this page that Salesforce has since implemented functionality internally to Schedule Apex.  There are some scenarios were the code below may still make sense to implement, but this was originally a workaround to get around Salesforce's lack of scheduling for code, so be sure to research Scheduled Apex before deciding if this is useful to you.  This does still make sense in cases where the code to be scheduled isn't being run on a regular, periodic basis but instead should be triggered based on criteria in your data.
-----
 
First off, credit where credit is due. My initial inspiration for this was work done by Steve Anderson that he shared on his blog gokubi.com, which is a great resource. What this allows you to do is both schedule code to run at particular times, and what I added on to his work is the ability to break processes into chunks that won't hit the governor limits so that large processes can be scheduled as well. I believe SF is working on providing this functionality, but until then, feel free to make use of the code below.

The anecdotal explanation: I created a custom object called Scheduled Jobs which I use to set off a workflow rule. The workflow rule is triggered by a checkbox field called "Schedule". One of the fields in Scheduled Jobs is "Next Run Datetime" and there's a workflow rule that is set to run 0 hours after that datetime. When it runs, all it does is update a "Run Job" field which sets off the Apex code. Each time a job is run, in addition to whatever code I want run, I have Apex insert a new Scheduled Job record with the Next Run Datetime and Schedule = true, so the process starts over again. Since I have a few different jobs running this way, the Scheduled Job includes fields to indicate which method to run, the time it should be set off (I store it as military time and convert it to a datetime in the code). I even have a job which deletes old Scheduled Job records so it doesn't get too cluttered.

The code, minus some irrelevent bits, is below. The intMagicNum is something I had to include because some of the methods I run have to be run in chunks otherwise they hit Apex governor limits. So, for instance, I have jobs that send out emails but I can only send out 10 emails at a time. Each time it's run, the method is tracking which users have been emailed and only emails people that haven't received it yet that day. So when it's done, it returns how many emails it actually sent and if that number = 10, the job is scheduled to run again immediately (there's generally a 15 minute delay) until everyone has been emailed (less than 10 returned).

Code:

trigger InsertUpdateScheduledJobAfter on Scheduled_Job__c (after insert, after update) {
Integer intReturn;
Integer intMagicNum;
List sjList = new List();
Datetime dte;

for (Scheduled_Job__c sj : Trigger.new){
 if (sj.Run_Job__c == true){
 
  //Create Site Log records for 7 business days in the future, if needed
  if (sj.Method_To_Execute__c == 'CreateSiteLogs'){
   intReturn = clsScheduledJobs.CreateSiteLogs(null);
   //This job is run in batches of 1 site invoice (the max that can be processed
   //before hitting the governor limit, sadly), so less than 1 means it's done
   intMagicNum = 1;
  }
 
  //Send out Job Loss Report
  if (sj.Method_To_Execute__c == 'JobLossReport'){
   intReturn = clsScheduledJobs.JobLossReport(null);
   //Single email limited to 10 at a time
   intMagicNum = 10;
  }
    
  if (sj.Method_To_Execute__c == 'ScheduledJobCleanup'){
   intReturn = clsScheduledJobs.ScheduledJobCleanup();
   //This job always returns 0, shouldn't need to be re-run anytime soon
   intMagicNum = 1;
  }
 
  Scheduled_Job__c new_sj = new Scheduled_Job__c(
   Method_To_Execute__c = sj.Method_To_Execute__c,
   Name = sj.Name,
   Run_Job__c = false,
   Schedule__c = true,
   Time_to_Start__c = sj.Time_to_Start__c);
 
  //If the number returned by the method is less than the Magic Number,
  //create a Scheduled Job record to run it again tomorrow night
  if (intReturn < intMagicNum){
   //Tomorrow, 12am
   dte = Datetime.newInstance(System.today().year(), System.today().month(), System.today().day()).addDays(1);
  
   //Add on hour/minutes from Time To Start field
   dte = dte.addhours(Integer.valueOf(sj.Time_to_Start__c.substring(0,2)));
   dte = dte.addminutes(Integer.valueOf(sj.Time_to_Start__c.substring(2,4)));
  
   //Sometimes if there are multiple scheduled jobs around the same time,
   //SF processes them in bulk and the governor limits are hit, so make sure
   //they're all spaced out at least 15 minutes
   sjList = [Select Next_Run_Datetime__c from Scheduled_Job__c
      where Next_Run_Datetime__c >=: dte and 
      Next_Run_Datetime__c <: dte.addMinutes(30) and 
      Schedule__c = true and Run_Job__c = false
      order by Next_Run_Datetime__c DESC
      LIMIT 1];
   dte = (sjList.size() > 0 — sjList[0].Next_Run_Datetime__c.addMinutes(15) : dte);
   //Reset Scheduled Job record to midnight of tomorrow
   new_sj.Next_Run_Datetime__c = dte;
  
  //Otherwise, there are more records to process, so create a record
  //to run it again immediately
  } else {
   //Make sure this is offset from other jobs so they don't run in bulk
   sjList = [Select Next_Run_Datetime__c from Scheduled_Job__c
      where Next_Run_Datetime__c >=: System.now().addMinutes(-15) and
      Next_Run_Datetime__c <: System.now().addMinutes(30) and 
      Schedule__c = true and Run_Job__c = false
      order by Next_Run_Datetime__c DESC
      LIMIT 1];
   new_sj.Next_Run_Datetime__c = (sjList.size() > 0 – sjList[0].Next_Run_Datetime__c.addMinutes(15) : System.now());
  
  }
 
  insert new_sj;
 }

}
}

Prelude

So I thought to myself, what self-respecting geek in this day and age doesn't have their own blog? Why, it's precisely the self-respecting geek that doesn't have a blog. Come now, cynical voice in my head, there are plenty of respectable blogs out there; we can't condemn all news shows just because of Fox News, can we?

And that's how I'd like to set the tone for this blog, schizophrenia. In the spirit of open source, I'd like this to be a place where I share tips, tricks and snippets of code that others might find useful. Much of my work these days is in Salesforce, so much of my ranting will be on that topic, but I've also just adopted a bouncing baby network so I'll be getting into that as well. Actually, it's more of a coughing, hacking old man of a network, but in either case, it needs a lot of attention, TLC and diapers.

Enjoy and let me know what's useful, what's not and what you'd like to see more of.