gokubi.com

will see the world differently.

The brilliant Simon Fell, father of the Salesforce.com API but apparently not the father of Applescript, has just thrown a new beta release of Maildrop, his Mac application for getting emails into Salesforce.

If you’re a beta-testing kind of person, give it a shot and give Simon feedback!

My co-worker Dave Manelski wrote some Apex to help the Salesforce.com / Vertical Response integration record bounced emails better. His code just got posted on the Vertical Response blog.

Nice work Dave!

Canadian charitable organizations have to give out tax receipts for all donations, and these tax receipts have to have sequential numbers. No numbers can be skipped, and they must be unique.

So I built a way to do this back in the day of S-Controls. It was a big pain. I can’t use autonumbers, because we only want to assign them to real gifts that meet certain criteria.

But with Apex triggers, it’s much easier. I wrote a trigger that when an Opportunity is added or updated and meets certain criteria, it gets the next available tax receipt number. The tax receipt number field is also set to be Unique, so I won’t get duplicate values.

Here’s the code. First the Trigger:

trigger ONEN_Opp_tax_receipt on Opportunity (before insert, before update) {
// Written by Steve Andersen, copyright (c) 2008 ONE/Northwest
// This program is released under the GNU General Public License. http://www.gnu.org/licenses/

	ONEN_RecordTypes rtlookup = new ONEN_RecordTypes ('Opportunity');
	// get opp rectype id of gift from Constants
	Id giftRecordType =  rtlookup.GetRecordTypeId(ONEN_Constants.OPP_DEFAULT_RECTYPE_FORTESTS);
	//list to hold Opps to be receipted
	List<Opportunity> oppsNeedingReceipts = new List<Opportunity>();

	//loop through the trigger set
	for ( Opportunity thisOpp : Trigger.new ) {
		//get a tax receipt if it's a gift, it's won, and it doesn't already have one
		if(thisOpp.RecordTypeId == giftRecordType && thisOpp.IsWon && thisOpp.Tax_Receipt_Id__c==null)	{
			//add to the list for receipting
			oppsNeedingReceipts.add(thisOpp);
		}
	}
	//if we found any that need receipting, process them
	if(oppsNeedingReceipts.size()>0){
		//instantiante the tax receipt class
		ONEN_Tax_Receipting taxReceipt = new ONEN_Tax_Receipting ();
		//loop through all the opps
		for (Opportunity updatingOpp : oppsNeedingReceipts) {
			//assign the next tax receipt id to the custom field
			updatingOpp.Tax_Receipt_Id__c = taxReceipt.getNextAvailableTaxReceipt();
		}
	}
}

And the Class:

public class ONEN_Tax_Receipting {
// Written by Steve Andersen, copyright (c) 2008 ONE/Northwest
// This program is released under the GNU General Public License. http://www.gnu.org/licenses/

	//double to hold the highest tax receipt
	private Double highestUsedTaxReceipt;

	//constructor
	public ONEN_Tax_Receipting() {
		Opportunity[] higestTaxReceiptedOpp = [select Tax_Receipt_Id__c from Opportunity where Tax_Receipt_Id__c!=null order by Tax_Receipt_Id__c desc limit 1];
		//get the higest tax receipt number
		if (higestTaxReceiptedOpp.size()>0) {
			highestUsedTaxReceipt = higestTaxReceiptedOpp[0].Tax_Receipt_Id__c!=null ? higestTaxReceiptedOpp[0].Tax_Receipt_Id__c : 0;
		} else {
			highestUsedTaxReceipt = 0;
		}
	}
	//get the next tax receipt number
	public Double getNextAvailableTaxReceipt() {
		highestUsedTaxReceipt++;
		system.debug(highestUsedTaxReceipt);
		return highestUsedTaxReceipt;
	}

	//this opp fits all the criteria and should get a tax receipt
	static testmethod void testNewOppGetsTaxReceipt () {

		ONEN_RecordTypes rtlookup = new ONEN_RecordTypes ('Opportunity');

		Opportunity opp1 = new Opportunity(
			Name = 'test opp1',
			RecordTypeId = rtlookup.GetRecordTypeId('Gift'),
			CloseDate = system.Today(),
			Amount = 1,
			StageName = 'Closed Won'

		);
		insert opp1;
		Opportunity testOpp1 = [select Tax_Receipt_Id__c from Opportunity where id = :opp1.Id limit 1];

		System.AssertNotEquals ( null,testOpp1.Tax_Receipt_Id__c );
	}
	//this opp already has a tax receipt and it shouldn't be overwritten. The next opp should get the next number.
	static testmethod void testNewOppAlreadyHasTaxReceipt () {

		ONEN_RecordTypes rtlookup = new ONEN_RecordTypes ('Opportunity');

		Opportunity opp1 = new Opportunity(
			Name = 'test opp1',
			RecordTypeId = rtlookup.GetRecordTypeId('Gift'),
			CloseDate = system.Today(),
			Amount = 1,
			StageName = 'Closed Won',
			Tax_Receipt_Id__c = 99999

		);
		insert opp1;
		Opportunity testOpp1 = [select Tax_Receipt_Id__c from Opportunity where id = :opp1.Id limit 1];

		System.AssertEquals ( 99999,testOpp1.Tax_Receipt_Id__c );

		Opportunity opp2 = new Opportunity(
			Name = 'test opp2',
			RecordTypeId = rtlookup.GetRecordTypeId('Gift'),
			CloseDate = system.Today(),
			Amount = 1,
			StageName = 'Closed Won'

		);
		insert opp2;
		Opportunity testOpp2 = [select Tax_Receipt_Id__c from Opportunity where id = :opp2.Id limit 1];

		System.AssertEquals ( 100000,testOpp2.Tax_Receipt_Id__c );
	}
	//not won opp shouldn't get tax receipt
	static testmethod void testNewOppNotWon () {

		ONEN_RecordTypes rtlookup = new ONEN_RecordTypes ('Opportunity');

		Opportunity opp1 = new Opportunity(
			Name = 'test opp1',
			RecordTypeId = rtlookup.GetRecordTypeId('Gift'),
			CloseDate = system.Today(),
			Amount = 1,
			StageName = 'Closed Lost'

		);
		insert opp1;
		Opportunity testOpp1 = [select Tax_Receipt_Id__c from Opportunity where id = :opp1.Id limit 1];

		System.AssertEquals ( null,testOpp1.Tax_Receipt_Id__c );
	}
	//non-gift opp shouldn't get tax receipt
	static testmethod void testNewOppNotGift () {

		ONEN_RecordTypes rtlookup = new ONEN_RecordTypes ('Opportunity');

		Opportunity opp1 = new Opportunity(
			Name = 'test opp1',
			RecordTypeId = rtlookup.GetRecordTypeId('Grant'),
			CloseDate = system.Today(),
			Amount = 1,
			StageName = 'Closed Lost'

		);
		insert opp1;
		Opportunity testOpp1 = [select Tax_Receipt_Id__c from Opportunity where id = :opp1.Id limit 1];

		System.AssertEquals ( null,testOpp1.Tax_Receipt_Id__c );
	}

}

It’s not bomb-proof. If multiple people are assigning tax receipts at the same time I might have problems. But it’s simple, and it’s not a high volume group. Any feedback is welcome!

Spirit of Racine Half Ironman Triathlon, Racine, Wisconsin

Better late than never…this is a race I did almost 4 months ago.

To cap of my trip to Wisconsin, I took part in the Spirit of Racine Half Ironman with 2000 random midwesterners willing to get up at 5 am and jump in Lake Michigan.

I did one Half Ironman last year, and it was a painful experience. I ran out of fuel about 5 hours in and struggled to the finish. My goal was to finish much more gracefully, and beat my time from last year. I was successful in those two goals and had a great race.

Death march up the beach…

A cool aspect about this race is that it was a point-to-point swim, so not curving around buoys and swimming laps in a lake. But that mean you have to walk to the start–in this case 1.2 miles up the beach. It was a beautiful beach, though, and it was cool walking to the foggy start with 2,500 people.

Game face…3 minutes to start.

The start was delayed because of the fog. Not affecting the start time was the 55 degree water temperature. The beach was abuzz with conversation about how cold the water was. And it was really, really, cold. I saw a few people with dish-washing gloves on to keep their hands warm!

Hey, that wasn’t so bad!

There were three aspects of the swim that made it the best open water swim I’ve ever done:

1. Swimming stright is just plain more fun than swimming in circles.
2. When you swim point-to-point, there is no bunching up at the corners!
3. The water was so clear I could see the sand bottom–I just lined myself up with the ripples and used them like lane lines.

The swim was my best leg relative to the field. I really enjoyed it!

I hit the bike and ate and drank like crazy. I was determined to fuel up properly and did a good job staying on my eating schedule. The course was pretty good–through the city of Racine and then out on rural highways. But Wisconsin gets cold in the winter, and years of freezing and thawing causes concrete roads to develop big cracks every 20 feet or so. It was no fun biking 56 miles with that periodic jarring. Ugh. I was glad when it was over!

The last quarter mile was a bit of a haze…

The run was great, if a bit hot. By the time I finished it was 85 degrees. But I kept a steady pace on the two-loop course, and kept in the game by shouting out supportive comments to other runners. I’m not usually very talkative, but I’ve found it’s a great distraction, and I think most people don’t find it too annoying.

The last quarter mile was really hard, but I guess that’s a sign of leaving everything on the course. I was really pleased to have the pain be so short-lived. Much better than last year. I got a PR by 40 minutes, and I’ve never been so pleased to finish in 516th place!

Overall Place: 516
Age Group Place: 89
Overall Time: 05:17:36
Swim: 00:29:52 (554th)
T1: 02:13
Bike: 02:40:59 (652nd)
T2: 01:41
Run: 02:02:50 (650th)

Using custom components makes lots of stuff possible. Here’s an example of a year-end giving thank you email:

This shows all gifts for 2008 and attaches a PDF of the giving history as well. Note that this is done just by connecting to the Contact, so this would work in the Salesforce.com mass mail interface.

Update: You cannot use vf templates in mass email or in the apex outboundemail method in Winter ‘09. Maybe next release…

Also note that it’s an ugly HTML email just because I’m not a designer. You can make this as professional as you’re willing to make it.

Here’s the relevant code:

The VisualForce Email Template

<messaging:emailTemplate subject="Thank you for your Support!" recipientType="Contact" >
    <messaging:htmlEmailBody >
        <html>
        <body>
            <p>Hello {!recipient.Household_Greeting__c}--</p>
            <p>Thank you so much for you giving this year. Every gift helps us make a difference in our envrionment...</p>
            <c:thisYearGivingTable ContactId="{!recipient.Id}"/>
            <br/><br/>
            We look forward to seeing you in 2009!
            <br/><br/>
            Best,
            Steve

            </body>
        </html>
    </messaging:htmlEmailBody>
    <messaging:attachment renderas="pdf" filename="{!recipient.Household_Greeting__c}_2008_Giving.pdf">
<html>
<body>
<h3>2008 Giving History for {!recipient.Household_Greeting__c}</h3>
<c:thisYearGivingTable ContactId="{!recipient.Id}"/>

</body>
</html>
</messaging:attachment>
</messaging:emailTemplate>

The VisualForce Component that is included in the Email Template:

<apex:component controller="thisYearGivingTableController" access="global">
	<apex:attribute name="ContactId" description="This is the Contact Id." type="Id" assignTo="{!thisContactId}"/>
	<table border="1">
		<tr>
		    <td><apex:outputText value="Date"/></td>
		    <td><apex:outputText value="Amount"/></td>
		    <td><apex:outputText value="Check Number"/></td>
		    <td><apex:outputText value="Check Date"/></td>
		</tr>
 	<apex:repeat value="{!thisYearOpps}" var="opp" id="theRepeat">
		<tr>
		    <td><apex:outputField value="{!opp.CloseDate}"/></td>
		    <td><apex:outputField value="{!opp.Amount}"/></td>
		    <td><apex:outputField value="{!opp.Check_Number__c}"/></td>
		    <td><apex:outputField value="{!opp.Check_Date__c}"/></td>

		</tr>
	</apex:repeat>
	</table>
</apex:component>

The Apex Controller that powers the logic for the VisualForce Component:

public class thisYearGivingTableController {
	//capture the contact id
	public Id thisContactId {get;set;}
	//a list to hold this year's gifts
	public List<Opportunity> thisYearOpps = new List<Opportunity>();
	//get the gifts into the list
	public List<Opportunity> getThisYearOpps() {
		//criteria for opps
		thisYearOpps = [SELECT Id, Amount,CloseDate, Check_Date__c, Check_Number__c FROM Opportunity
			WHERE IsWon=true AND Year__c=:String.valueOf(system.Today().Year()) AND Id IN
			(SELECT OpportunityId FROM OpportunityContactRole WHERE ContactId = :thisContactId AND Role='Individual Donor')
			ORDER BY CloseDate];
		return thisYearOpps;
	}
}

Kudos to Andrew and the team for making the email template functionality so robust! This is killer stuff!

When I saw that Winter ‘09 was going to include VisualForce email templates, I got really excited. I thought about all the cool things I could do with pages running custom controllers and getting whatever data I wanted.

And then I saw that VF email templates weren’t going to be able to use custom controllers and I wrote them off.

Well, I’ve dug back into them and turns out they are going to be amazingly useful. And when we get custom controllers on them, they will be seriously butt-kicking.

Here’s a VF email template:

<messaging:emailTemplate subject="Thank you for your Support!" recipientType="Contact" >
    <messaging:htmlEmailBody >
        <html>
        <body>
            <p>Hello {!recipient.Household_Greeting__c}--</p>
            <p>Thank you so much for you giving this year. Every gift helps us make a difference in our envrionment...</p>
            <apex:outputPanel rendered="{!recipient.Total_Gifts_YTD__c>0}">
                <apex:outputText rendered="{!recipient.Total_Gifts_YTD__c>0}">
                    <table>
                        <tr>
                            <td>Gift Date</td>
                            <td>Check Number</td>
                            <td>Check Date</td>
                            <td>Amount</td>
                        </tr>
                </apex:outputText>
                <apex:repeat value="{!recipient.OpportunityContactRoles}" var="opps" id="theRepeat">
                    <apex:outputText rendered="{!opps.Role=='Individual Donor'&&opps.Opportunity.IsWon&&opps.Opportunity.Year__c=='2008'}">
                        <tr>
                    </apex:outputText>
                    <apex:outputText rendered="{!opps.Role=='Individual Donor'&&opps.Opportunity.IsWon&&opps.Opportunity.Year__c=='2008'}">
                        <td><apex:outputField value="{!opps.Opportunity.CloseDate}"/></td>
                        <td><apex:outputField value="{!opps.Opportunity.Check_Number__c}"/></td>
                        <td><apex:outputField value="{!opps.Opportunity.Check_Date__c}"/></td>
                        <td><apex:outputField value="{!opps.Opportunity.Amount}"/></td>
                    </apex:outputText>
                    <apex:outputText rendered="{!opps.Role=='Individual Donor'&&opps.Opportunity.IsWon&&opps.Opportunity.Year__c=='2008'}">
                        </tr>
                    </apex:outputText>

                </apex:repeat>
                <apex:outputText rendered="{!recipient.Total_Gifts_YTD__c>0}">
                    </table>
                </apex:outputText>

            </apex:outputPanel>
            </br></br>
            We look forward to seeing you in 2009!

            Best,
            Steve

            </body>
        </html>
    </messaging:htmlEmailBody>
</messaging:emailTemplate>

In line 1 I connect this template to the Contact record as RecipientType. Then when I send to a Contact, I can merge fields. So in line 5 I’m pulling a custom field on Contact called Household_Greeting__c. So far so good.

On line 7 I start building an HTML table, and I only want to show it if this Contact has given money this year, so I render only if their Total_Gifts_YTD__c>0. Slick. No custom controller, but I can still put some logic in there.

On line 17 I create a repeat that will loop through all OpportunityContactRoles this Contact has. Whoa! I suddenly have access to all their donations!

On 18 I want to create a table row, but only when the opp we’re dealing with is won, was in this year, and this contact was the individual donor role on it. We have to repeat the same rendering logic on each field as well.

I’m looping through OpportunityContactRoles, but I want Opportunity information. That’s really easy. On line 22 I get {!opps.Opportunity.CloseDate}–the close date off the Opportunity on this contact role. Simple!

So with no controller, I can get any related list off the Contact and iterate through it. And I can traverse across multiple relationships. Only getting the records I care about requires some rendering hacks, but it works. Also, I don’t think I can sort the items at all.

I’m impressed with what VisualForce email templates can do with no controller. Check them out, they’re easy to use! And when they give us the ability to use Custom Controllers, we’ll be able to do crazy things.

Update: In the comments Andrew sets me straight on a key feature–you can put custom VisualForce components in VisualForce email templates. Wow. We can now do just about anything we want. Seriously, there are very few limitations to what can be done now. Thanks Andrew for pointing out that killer feature!

I’ve built a prototype app for managing email subscription preferences in Salesforce.com. Check out the movie to see it in action.

Right now Salesforce.com doesn’t allow unauthenticated users to modify standard object records like Contact. I had to write some tricky code to make this functionality work, but this code is actually in violation of the current license agreement.

I’ve posted a Salesforce Idea for this licensing change. Vote it up!

All the sessions from Dreamforce 08 are now online.

I was a part of two sessions. First was “The Platform to Change the World,” where I got to present with my Bari Samad of Green for All. We start our part of the talk about 3/4 of the way through. We’re a bit rushed because we got squeezed for time by the folks who presented before us. You can use the slider in the movie player to cut directly to Bari’s presentation if you wish.

I then got to present with Lisa and Marc about all the community aspects of our Salesforce.com work. There is great content in this talk, so check it out.

We have all lived through a defining event in American history. This country was founded on language so eloquent it remains an inspiration to the world. Yet even as Thomas Jefferson was setting his thoughts on independence and human rights to paper, our country was stained with the sin of slavery.

On Tuesday we elected a President of African descent and said, collectively, officially and for the first time, that race is not a disqualifier from holding our highest office. We have finally showed we are willing to move on from our 400-year-old conflict, and get to the hard work of living up to those words written over two centuries ago. We have a long way to go, but Tuesday signaled we are ready to go there. Now there is just the work to be done.

Where were you when it happened?

I wanted to be home with my family. I wanted to have my children experience the overwhelming excitement and emotion I felt when the victory became likely, then highly probable, and then official. I wanted to sit on the couch with my wife and take it all in. Just as we watched so many primaries and whether in victory or defeat, we found ourselves surprised at how inspired and touched we were by this amazing person. I wanted to check election results on my home computer where I invariably found myself after every primary, whether in victory or defeat, giving more money to Barack so that he could make it to today.

But I wasn’t at home. I spent the election in San Francisco, at a work convention.

I have to tell you that I am incredibly lucky. When I go to Dreamforce I drop into an amazing community of leaders who are showing the world how we can make the better place. I get to meet so many new folks and hear about the great work they’re doing to address the challenges we all face. I get to connect with good friends I’ve made over the years through this work.

When we all headed over to the breathtaking Starlight Room at the Sir Francis Drake Hotel, the election was already in the bag. The TV was on as the middle of the country rolled in, cementing the win for Obama. And then at 8 PM the west coast came in and the networks called it.

The room erupted in a prolonged cheer, and you could hear the relief, disbelief, and unadulterated happiness all wound up in it. Everyone in the room realized that we were witnessing a moment that would live in American history for a long time. And we got to witness it together.

The Salesforce.com Foundation hosted us at the Drake, and it was a lovely party. The Foundation has been a core driver to the work I do. Without them I would be hacking php at home. But with their support I get to put the world’s best CRM platform in the hands of nonprofits who couldn’t otherwise afford it, and couldn’t otherwise use it. They are making a large impact in the world. Seriously large.

That night, I was honored and humbled to be presented with the Salesforce.com Foundation’s first annual Community Hero award and an incredible gift of $10,000. I was humbled that these amazing folks–Steve Wright, Meghan Nesbitt, Tucker MacLean, Bryan Breckenridge, Chris Atwood, Suzanne DiBianca and so many others–recognized me for work that for me is just plain fun! Thanks so much to you all–we couldn’t do it without you.

After the party was over we headed down to the street and found Powell Street lined with Obama supporters. Everyone was cheering and dancing. Cars were honking their horns, with passengers leaning out windows waving Obama signs. We stopped and watched, joining in the improptu celebration. I didn’t want to go anywhere. I wanted to stay and share the moment with San Franciscans.

Then about 30 men in colorful sweatshirts marched up Powell and formed a semi-circle in the crowd next to us. They began to sing acapella songs about freedom, equality, and Obama. We all listened as they waved their rainbow flag and sang beautifully. These men were celebrating Obama, but they had a lot more on the line in this election than I did. Californians were at that moment voting to strip gays of the right to marry. At the time the result was not known, but everyone was very worried that prop 8 would pass, which it did in the wee hours of the night.

The chorus then broke into our national anthem. We all sang along, and I could tell that many in the crowd were singing our anthem with conviction for the first time in a long time.

I sang, too, and was moved by what my country did that day. I didn’t think we could do it, and we did. I have mouthed the words to the national anthem countless times, but I realized last night that I thought I lived in a different country than I do. None of the vast injustices we’ve perpetrated over the years were made right that night. But we took a step in the right direction. The electon showed me I was wrong about who we are and what we can accomplish. We can move in the direction of love and respect, and we might see that out of this presidency.

So that’s how I spent this historic election night. I was away from my family, but with a community I care deeply about, with many very good friends. I’ll never forget that night. I’ll never forget the hope and joy in our uplifted voices. Thanks to all my friends who were there. Congrats to all who worked for Obama and this hard-fought victory!