I posted recently about Batch Apex, which I used to solve an interesting problem for data summary across lots of records. That code was working great before Winter 10 was released – unfortunately, Batch Apex is no longer working properly since the update. See this discussion for information on the problems people are encountering. The basic issue is that if your query is complex or returns a large number of records (the whole point of batch apex), it fails. I some cases you get an error, in others the query simply returns no records and reports that it was successful at running 0 batches.

If you were thinking of using Batch Apex in your own code, you will want to wait until they have a chance to iron out the bugs. I have submitted a case to support, and will post here when I learn more.

UPDATE 10/30: A release came out this week that appears to have fixed the issue of large queries. I haven’t had a chance to test all our use cases, but it looks promising.

Does your organization maintain a group calendar of staff events, trainings, room rentals, or any other set of dates that are also stored somewhere in Salesforce? Do you wish there were a way to display them on a calendar?

One option would be to use workflow to create an event on every user’s calendar whenever an object is saved. But that creates noise for users, and isn’t nearly as intuitive as having a separate calendar for a given purpose. What you really want is a group calendar available to everyone that displays only events pertaining to one thing.

The use case

My client, a theater that sells blocks of tickets to its shows using Salesforce, needed a place to track the status of all their bookings on a calendar. To meet their requirement, I created a public calendar in their account. (See my general post on group/public calendars on the NPower Seattle blog for how to do this.)

Getting the events on the calendar

My first thought was to use workflow to put events on that new calendar whenever an opp was moved to the right stage. It turns out you can’t do that – although a public calendar can be the “owner” of an event, you can’t select a public calendar as the user for a workflow task or event. Besides, the client needed not only to put this event on the calendar in the first place, but to update it automatically anytime the stage of the opportunity changes.

To maintain the events on the calendar, I created the following trigger:

trigger oppChangeAfter on Opportunity (after delete, after insert, after undelete, after update) {
// trigger to manage events on the shared theater Show Calendar
// written by Evan Callahan, copyright (c) 2009 NPower Seattle
// released under the GNU General Public License, http://www.gnu.org/licenses/gpl.html

      // need to know the salesforce ID of our public calendar
      final id publicCalendarId = '02380000000YHci';    

      // this flag allows us to bypass all queries if the opp is not the type that goes on the calendar
      boolean createEvents = false;

      // get acct names to put them in the events we create
      set oppAccts = new set();
      map acctMap;

      if (!trigger.isDelete) {
            set acctIds = new set();
            for (opportunity o : trigger.new) {
                  if (o.stageName != 'Quote' && o.stageName != 'Closed Lost' && o.show_date__c != null
                                    && o.show_date__c >= system.today().addMonths(-1) && o.theater_city__c != null) {
                        createEvents = true;
                        oppAccts.add(o.accountId);
                  }
            }
            if (createEvents) acctMap = new map([select id, name from account where id in : oppAccts]);
      }  

      // get opp ids in this trigger set
      set oppIds = (trigger.isDelete ? trigger.oldmap : trigger.newmap).keyset();    

      // get contacts for these opps so we can attach them to the events
      map oppCons = new map();

      if (createEvents) {
            for (opportunityContactRole ocr : [select opportunityid, contactid
                                                                        from opportunityContactRole
                                                                        where opportunityId in : oppIds and isPrimary = true]) {
                  oppCons.put(ocr.opportunityId, ocr.contactId);
            }
      } 

      // get existing events for these opps - need to update them
      map tzShowEventMap = new map();
      for (event e : [Select id, e.WhoId, e.WhatId, e.Subject, e.OwnerId, e.IsReminderSet, e.IsAllDayEvent, e.ActivityDate
                                          From Event e where whatId in : oppIds and ownerId =: publicCalendarId ]) {
            tzShowEventMap.put(e.whatId, e);
      }      

      event[] eventsToDelete = new event[0];
      if (trigger.isDelete) {
            eventsToDelete = tzShowEventMap.values();
      } else if (createEvents) {
            // add an event for the show
            for (opportunity o : trigger.new) {
                  if (o.stageName != 'Quote' && o.stageName != 'Closed Lost' && o.show_date__c != null
                                    && o.show_date__c >= system.today().addMonths(-1) && o.theater_city__c != null) { 

                        // create the event or get the existing one
                        event e = tzShowEventMap.containsKey(o.id) ? tzShowEventMap.get(o.id) : new Event();
                        e.isReminderSet = false;
                        e.isAllDayEvent = true;                        

                        // connect to the contact, if any
                        e.whoId = oppCons.containsKey(o.id) ? oppCons.get(o.id) : null;                        

                        // connect to the opp
                        e.whatId = o.id;                        

                        // get the acct name
                        string acctName = (o.accountId==null || !acctMap.containsKey(o.accountId)) ? '[Unknown]' : acctMap.get(o.accountid).name;
                        if (acctName.length() > 50) acctName = acctName.substring(0, 50);                                    

                        // the subject of the event includes the opp stage, acct name, and number of tickets purchased
                        // example -  45 Booked: NPower Seattle
                        e.Subject = ((o.total_tickets__c != null && o.total_tickets__c > 0) ? o.total_tickets__c.format() + ' ': '') +
                               (o.StageName == 'Closed Won' ? 'Closed' : o.StageName) + ': ' + acctName;                               

                        // the date of the show is in a custom field
                        e.activityDate = o.show_date__c;                            

                        // to put it on the right calendar, set the owner ID to the calendar ID
                        e.ownerId = publicCalendarId;                          

                        // put this event on the map
                        tzShowEventMap.put(o.id, e);
                  } else {
                        // delete events with opps that no longer fit the bill
                        if (tzShowEventMap.containsKey(o.id)) eventsToDelete.add(tzShowEventMap.get(o.id));
                  }
            }
            // add and update!
            upsert tzShowEventMap.values();
      }
      if (!eventsToDelete.isEmpty()) delete eventsToDelete;
}

Another option for a truly public calendar

This calendar is working well – we created a link to it for all users, and they can hover over events on the calendar to see the details or link directly to the opportunity or associated contact. However, the calendar is not publicly available outside Salesforce.

Another option we considered was using the Google Apps API to add the opportunities to an external Google calendar. That would allow us to share the calendar with employees or partners who don’t have Salesforce accounts – or even to the general public. After taking a peek at the API on the developer site, I don’t think it would be much more effort. The Google calendar wouldn’t offer direct links to the Salesforce objects, but it would be a lot more flexible than the calendar in Salesforce.

Here is a 5-minute demo of the nifty Visualforce interface I created for running batch apex (see my earlier post on Batch Apex and why we are using it). I needed to create the custom page because:

• You can’t schedule batch apex – have to start it manually.
• Our batch processing involves several distinct steps that need to be queued up one after the other.
• You are not allowed to run a new batch apex process until the previous is complete, and you can’t run one automatically when the previous one finishes.
• A Visualforce page with an ActionPoller component is a great workaround; it starts the first batch, monitors it every 10 seconds until it finishes, then starts the second batch process, and so on.

Salesforce.com has a new feature called Batch Apex. It is currently available only as a preview by special request, but is expected to be in general release later this year. One of my nonprofit clients has a very large data set and have been looking forward to the ability to add up opportunity values in a variety of ways. We used Batch Apex to create a tool for them, and it seems to be working extremely well.

In this post, I will briefly explain what Batch Apex is, and describe our use case for the feature. In a later post, I will show the tool we created and how it works.

About Batch Apex
Apex has limits. You can perform only so many queries, and process only so many results; this allows Salesforce to keep your resource usage under control. Batch Apex blows these limits out of the water by allowing you to perform a query that returns up to 5 million records, and then create a special type of apex class that processes the records 200 at a time. There are still some rules as to what you can do in a Batch Apex class, but it can be a great workaround for apex limits.

Once you write a “Batchable” Apex class that follows the proper design pattern, you can use the class to execute your query, which queues up an Apex job that executes little by little until it is complete. How fast it runs depends on factors such as complexity of your query, time of day, and the extra capacity of the Salesforce system. You can monitor your job’s progress by clicking Setup | Monitoring | Apex Jobs.

As you see, the last apex job shown was broken into 1,327 batches of 200 records – which means the query must have returned and processed over 265,000 opportunities.

Why We Used It
We use many strategies for summarizing data in Salesforce, including report totals, roll-up summary fields, and apex code in triggers. Because roll-up summaries do not support calculations of opportunity totals into contacts who have roles, we have always used triggers to keep these calculations current. However, if your data set is large enough, you will hit apex limits.

The following screen shows the donation summaries our client asked us to provide for all their donors, households, and accounts. Having this data available on the contact and household records will allow them to produce reports that filter in creative ways – for example, donors whose donations are rising or falling from year to year or those with one type of donation history but not another.

With the tool we created, the client can visit a Visualforce Page (daily or weekly, perhaps) and kick off the Batch Apex that performs all the rollups. The Apex runs in steps: the first two batches roll up opportunities to a temp table, a third copies totals from the temp table to the contact, household, and account objects, and so on. When the jobs finish running, they know the totals are all correct. We still use triggers to add a newly closed opportunity to the totals – but because all the really heavy lifting now belongs to the offline Batch Apex process, we don’t expect to hit any limits.

In my next post, I will show you the Visualforce tool and a bit of the Batch Apex code I wrote.

I’ve been busy writing Apex, so no time to post to the blog. One thing I’m learning is that once you have some useful Apex code, like the lead conversion and payment processing tools we’ve created, it is easy to deploy it to multiple organizations — and that is just what we’re doing. However, in every case there are small tweaks to make, and more test methods to write, and more debugging, so it important not to underestimate the time needed to reuse your solutions.

We currently have two open positions in in our growing CRM consulting practice here at NPower Seattle (and many clients ready for help!). If you have Salesforce expertise or other relevant experience and are looking for a opportunity to work with nonprofits on a great team of developers and implementors, please take a look at these.

NPower Seattle serves over 450 nonprofit clients each year by providing technology consulting, education and training. Our clients include arts and environmental groups, human services agencies, food banks and youth-serving organizations.

Find full job descriptions at http://www.npowerseattle.org/get-involved/jobs. Submit resumes and cover letters to Resumes (at) NPowerSeattle.org.

Salesforce is so reliable and available, it is easy to forget to back up your data locally. Lately, I’ve was reminded that it is a good idea to back up your Salesforce data from time to time. Salesforce allows administrators to download a complete export up to once a week.

It isn’t that you have to fear for the Salesforce database crashing or being lost. I recommend downloading for a couple reasons:

• It could help if you ever accidentally make unintended changes to data and want to return to an earlier state.
• It could also be helpful in the event that Salesforce or your internet access ever goes “down” at a critical time – you’d have a way to access your data offline.

To download the backup, go to Setup (Administration) | Data Management | Data Export. Click the button – a while later you’ll get an email with a link to download your data file. Save it somewhere safe.

Alternatively, if you use Demand Tools, you can back up to a file as often as you like. I use this to create a copy of the database in a Microsoft Access file.

In a previous post, I talked about a few ways that I customize web-to-lead forms. One key change I make is to ensure that certain fields get filled in for every web lead. In a Salesforce page layout, you can require fields, but web-to-lead doesn’t allow you to do this – any web-to-lead form submission will create a lead, even if critical fields such as last name or email are left blank.

Most of our clients want to make sure their leads fill in a minimum amount of information, such as name and email. Moreover, you can catch spam by requiring sensible entries – since spammers don’t always fill in the fields.

Read the rest of this entry »

In a previous post, I talked about a few ways that I customize web-to-lead forms. One of the most important improvements you can make, I think, is to add Javascript functions to format phone numbers.

When you enter 10 digits into a phone number field in Salesforce, it gets formatted as (206) 555-1212. Web to lead forms don’t do this by default, so phone numbers show up in Salesforce with inconsistent formatting. This is the reason I first started fiddling with Javascript in web-to-lead forms.

Javascript lets you change data when a website user moves out of a field – you simply respond to the onblur event by calling a function such as my formatPhone function. In the web to lead form, your phone field tag will look like this:

<input  id="phone" maxlength="40" name="phone" size="20" type="text"
     onblur="formatPhone(this);" />

The Javascript code goes in a <script> block within the <head> section of the web page:

<script language="JavaScript" type="text/JavaScript">
<!--
    //Written by Evan Callahan, NPower Seattle
    function formatPhone(num) {
    var re= /\D/;
    var newNum=num.value;
     if (newNum != "") {
        while (re.test(newNum)) {
         newNum = newNum.replace(re,"");
        }
        if (newNum.length == 7){
           newNum = '(206) ' + newNum.substring(0,3) + '-' +
               newNum.substring(3,7);
           num.value = newNum;
        }
        if (newNum.length == 10){
           newNum = '(' + newNum.substring(0,3) + ') ' +
               newNum.substring(3,6) + '-' +
               newNum.substring(6,10);
           num.value = newNum;
        }
      }
    }
//-->
</script>

My function defaults 7 digit numbers to a 206 area code – if you aren’t fortunate enough to live in Seattle, you can replace this with a different default (or skip the whole section).

We use web-to-lead forms pretty extensively for our Salesforce clients, because it is so much less expensive for them than integrating with the API.  We’ve found that WTL can work not just for ordinary leads, but also for simple event registration, volunteer signup, pledges, and many types of "applications" for service or grants.  Leads don’t have to be just people – they can bring in organization, donation, grant, or service information as well.

For example, we worked with a group that matches teen interns to other nonprofits that need interns.  The organizations that want interns apply for services on a web-to-lead form; the teen applicants sign up on another.  The first type of lead gets converted into an account, a contacts, and an internship "opportunity," while the intern applicants stay as leads until they are either matched to an internship (via contact roles) or rejected and deleted.

This is just one example of how web-to-lead has allowed us to set up simple website integration relatively quickly.

The more you use web-to-lead, however, the less you’ll like the HTML form that Salesforce provides you.  We edit the form pretty extensively, for several reasons:

• To arrange and align form fields in a table and apply styles
• To format phone numbers and other values as they are entered
• To provide validation, making certain fields required and helping to avoid spam
• To set hidden values, such as Lead Source or Record Type, that we don’t want to show on the form
• To create or modify a picklist for values, such as Campaign

The first thing I do for web to lead is format the form in a table – two columns, with the labels at left and the fields at right. Then I add styles for labels and fields to the stylesheet.  For examples, take a look at forms for Teens in Public Service, Communities Connect Network, and Arts Corps.  To see the form’s HTML, view the source code for the page and search for WebToLead.

I will offer other a few other examples of web-to-lead techniques in subsequent posts.

If you are using the latest Force.com IDE to develop s-controls or Apex code, you know how nice the latest upgrades are — for editing code, testing, and staying in sych with Salesforce.

I’ve just discovered something else really great — you can edit metadata for objects and fields in your Salesforce instance.  (It has to be a sandbox or developer instance, but you can then package and "deploy" your changes to a production instance.)

Have a custom object or a picklist in one instance and want to copy it to another?  Need to change a bunch of field names or picklist values?  You no longer need to package the items and install them from the appexchange, or go through all the pointing and clicking.  Instead, you make the changes in the object metadata XML files in Eclipse — you can use the XML design tools, or just edit the raw XML:

At first, I was getting a lot of errors when saving objects.  There is a bug, and I found the workaround here.  Basically, there is this file called package.xml — if you are adding an entirely new object (via the New menu), the Force.com IDE updates that file with your additions.  But if you just add custom fields, or if you copy and paste anything from one instance to another, it won’t be in package.xml, so you’ll get an error when saving or synchronizing to Salesforce. 

The trick is to open up package.xml, find or create the section that lists the type of thing you want to create, add the tags, save package.xml, and THEN save your new items.  For example, putting this code in package.xml lets me add the three custom fields shown in the Lead.object file above:

   
        Lead.Address_Type__c
        Lead.Birthdate__c
        Lead.Communication_Preference__c
        CustomField
   
Be sure to add the code to package.xml before EACH thing you add/paste and save, because Salesforce synchronizes package.xml when you save, taking out any entries that aren’t actually in your instance.