We recently reassigned a heap of Opportunities between staff members. However, the previous Opportunity Owner had open Activities on the Opportunities. The new Opportunity owners couldn’t close those Activities since they belong to somebody else.

So, they asked me to find a way to bulk-close the Activities.

This sounded simple, but was made more difficult by the fact that Activities can link to many different object types: Account, Opportunity, Campaign, Case or Custom Object.

The connection is made via WhatID, which is the ID of the associated object. It can be accessed via SOQL like this:

SELECT Id, What.Name from Task

However, not all fields are available, so you can’t SELECT What.OwnerId.

Fortunately, I found a forum post called Getting Object type of WhatId/WhoId Task/Event fields gave me a few hints, and I came up with this code:

// Get list of Opportunities owned by new person
Opportunity[] opps = [Select Id from Opportunity where OwnerId = '005200300014jeN'];

// Get incomplete Activities owned by previous person attached to the above Opportunities
Task[] tasks = [select Id, What.Name from Task where OwnerId = '00520000000tSOj' and Status != 'Completed' and WhatId in :opps];
for (Task t : tasks) {
  t.Status = 'Completed';
}
update tasks;

This grabs a list of ‘owned’ Opportunities and checks for any Activities (which are actually Task objects) that have a WhatId matching those Opportunities.

Straight-forward and pretty simple. Almost makes up for not being able to traverse directly to the linked object.

The Bottom Line

• Activities can link to multiple objects
• They connect via WhatId, but only a limited number of fields are exposed, eg What.Name
• Use an ‘IN’ comparison to match the Activities with Opportunities

I run into this problem all the time.

I want to write a quite routine in the System Log window to Mass Update some records (eg my previous Mass Delete via System Log window blog post). I want to find all records before a certain date, but SOQL never likes my date format, eg:

select Id from Opportunity where Expiry_Date__c < 2010-01-01

Yes, it is possible to convert it to Timezone format and do it this way:

select Id from Opportunity where Expiry_Date__c < 2008-01-01T00:00:00Z

but I’ve always thought that silly when comparing against a Date field.

So, I eventually figured out that I can do it this way:

select Id, Name, LastLoginDate from User where LastLoginDate > :Date.valueOf('2008-01-01')

Of course, this only works within the context of Apex, such as the System Log window. It won’t work in pure SOQL tools like SOQL Explorer. Here’s an example:

Opportunity[] opps = [select Id from Opportunity where Expiry_Date__c < :Date.valueOf('2010-01-01')];
System.Debug(opps.size());

The Bottom Line

• Date strings can’t be entered into SOQL
• Option 1: Use Timezone format: 2010-01-01T00:00:00Z
• Option 2: Convert from string: :Date.valueOf(’2010-01-01′)

I was configuring DataLoader to export a list of Opportunities, and I went to select the “Owner Name”. However, only OwnerID is available on an Opportunity.

“No problem!” I think to myself, as I go and create a Custom Field with a formula equal to Owner.Alias.

“What?” I say in surprise. “It won’t let me access a field on the Owner object!”

Mmm. This is strange. Then a Google Search reveals an 18-month old Ideas request to Make “Owner Id” Look up fields available for formulas.

Oh dear.

Well, that’s a shame, but it’s easily solved! I created:

• A field called Owner_Link__c of type Lookup(User)
• A Trigger to copy OwnerId to Owner_Link__c when the Owner is changed
• A test for the Trigger

Trigger:

trigger Update_OwnerLink_on_Owner_Update on Opportunity (before update, before insert) {

  // When 'Owner' field is changed, update 'OwnerLink' too

	// Loop through the incoming records
	for (Opportunity o : Trigger.new) {

		// Has Owner chagned?
		if (o.OwnerID != o.Owner_Link__c) {
			o.Owner_Link__c = o.OwnerId;
		}
	}
}

Test:

public with sharing class TriggerTest_OwnerLink {

	static TestMethod void testOwnerLink() {

		// Grab two Users
		User[] users = [select Id from User limit 2];
		User u1 = users[0];
		User u2 = users[1];

		// Create an Opportunity
		System.debug('Creating Opportunity');
		Opportunity o1 = new Opportunity(CloseDate = Date.newInstance(2008, 01, 01), Name = 'Test Opportunity', StageName = 'New', OwnerId = u1.Id);
		insert o1;

		// Test: Owner_Link should be set to user 1
		Opportunity o2 = [select id, OwnerId, Owner_Link__c from Opportunity where Id = &#58o1.Id];
		System.assertEquals(u1.Id, o2.OwnerId);
		System.assertEquals(u1.Id, o2.Owner_Link__c);

		// Modify Owner
		o2.OwnerId = u2.Id;
		update o2;

		// Test: Owner_Link should be set to user 2
		Opportunity o3 = [select id, OwnerId, Owner_Link__c from Opportunity where Id = &#58o2.Id];
		System.assertEquals(u2.Id, o3.OwnerId);
		System.assertEquals(u2.Id, o3.Owner_Link__c);
	}
}

This then gave me a new Owner object on my Opportunity on which I could create Formulas:

I could also use it in the DataLoader by referring to Owner_Link__r.Alias.

Hooray!

Easy to solve, but it’s a shame it was necessary.

The Bottom Line

• Formulas can’t access Opportunity.Owner fields
• Create a ’shadow’ field to hold Owner and populate it via a Trigger

A new Salesforce blog post by Ümit Yalçinalp (whom I interviewed back at Dreamforce 2009) is showing some incredible new features for the System Log Console.

I first discovered the joys of the Console while trying to debug some Apex. I used it to call an Anonymous function to run my code, or sometimes I’d just paste my whole code into the debug window and run it from there. It’s also a great place to run some quick code to update records.

However, it’s not that great. It’s very hard to do good debugging in a web-based, multi-tenant environment. Some recent improvements were quite nice but they look decidedly antiquated compared to what’s going to be released.

It seems that we’ll be able to step-through already-executed code. So, rather than pausing the whole Salesforce system, waiting for us to hit a ’step’ button (not great for a multi-tenant system!), it captures very detailed information and allows us to play back the executed code while viewing the execution stack and the lines of Apex code.

Very clever indeed!

The Bottom Line

• A new System Log Console is being piloted
• It’s giving amazing capabilities for a cloud-based system
• See the full article for more details

Wow, I just noticed the new System Log window in Salesforce.com:

The first big improvement is the provision of a Log History. When executing code in the top half, the log file appears in the bottom half. This also works when data is updated through other means, such as editing a record. Previous logs can be viewed by clicking. (I did, however, notice that it was a little slow when the log was large.)

Another little improvement is the way the “Execute” button changes to “Executing…” when clicked. I have always been confused over this, because the window appears to ‘hang’ when executing time-intensive code (eg looping over many records) and I could never tell if the command was correctly transmitted.

Thank you, Salesforce development team!

The Bottom Line

• The System Log window has been updated
• Multiple log histories are available

Here’s a lesson I learned while making our Cloud Developer Challenge entry.

Take a look at these lines of code and tell me which ones are OK and which ones are dangerous:

integer count = [SELECT count() FROM Contact];
Contact c1 = [SELECT Id FROM Contact LIMIT 1];
Contact c2 = [SELECT Id FROM Contact LIMIT 1];
Id first = [SELECT Id FROM Contact LIMIT 1].Id;
Contact[] contacts = [SELECT Id FROM Contact LIMIT 1];
Contact[] allContacts = [SELECT Id FROM Contact];

If you executed the above code on most systems, it will run just fine. However, there is a situation in which you’ll get the dreaded List has no rows for assignment to SObject error. Can you figure out which one?

It’s when there’s no objects to return!

For example, if you have a system with no Contracts, try running the above code with Contract in place of Contact and you’ll get:

ERROR - Evaluation error: System.QueryException: List has no rows for assignment to SObject
ERROR - Evaluation error: AnonymousBlock: line 2, column 15

What happened? Well, these 3 lines will all give an error if there’s no row found:

Contact c1 = [SELECT Id FROM Contact LIMIT 1];
Contact c2 = [SELECT Id FROM Contact LIMIT 1];
Id first = [SELECT Id FROM Contact LIMIT 1].Id;

While a SELECT normally returns an array/list, these statements are using the shorthand syntax that assumes only one row is returned. What’s not obvious is that it also assumes that exactly one row is returned!

While this is unlikely to occur for Contact, it is highly likely to occur for any custom objects you create, especially when a WHERE statement is used that might return zero rows, such as:

Player__c player = [SELECT Id from Player__c where Name = :username];
if (player != null)
    p = player.Id;

The above code will fail if there is no Player__c record with the matching username. It doesn’t actually return a null.

It would be safer to do the following:

Player__c[] players = [SELECT Id from Player__c where Name = :username];
if (players.size() > 0)
    p = players[0].Id;

It’s one of those situations for which you wouldn’t normally think of creating a test, so it’s safer to just avoid the possibility rather than making an assumption about your data.

The Bottom Line

• The shorthand syntax that expects one row to be returned from a SELECT statement is very handy
• It’s also dangerous if no rows are found!

I’m pleased to announce my entry into the 2009 Force.com Cloud Developer Challenge .

The Challenge is designed to evangelize the Force.com platform, encouraging lots of people to discover, learn and use Force.com. The rules are wide open — just build something using Force.com, Visualforce and Sites (so that it is publicly accessible). It’s a very clever idea — sort of a small version of the X-Prize , with the concept that offering prizes will encourage more people to do interesting than actually directly paying people to do it!

Our Entry

So, would you like to know about our entry?

Yes, I use the word ‘Our’ rather than ‘My’ because I’m happy to say that I teamed up with David Schach, author of the X-Squared on Demand blog . Previous readers will remember that I met David some time ago when he visited Australia . Well, it just so happened that David read my previous blog entry about the Cloud Developer Challenge and dropped me an email to say he was visiting Australia again, and did I want some help?

This was a god-send, because I had been hitting lots of brick walls in my ramp-up of Visualforce and Sites knowledge, and David is an absolute expert in the subject. So, I did all the high-level UI, he did all the low-level ‘engine room’ stuff and we worked in the middle to make a very exciting site.

Introducing Daily Shinro

The site we developed is a Social Gaming Website that we call Daily Shinro .

The idea for the site began with my work team at Atlassian , where we play a daily game of SET , a really fun logic puzzle that changes each day. To keep track of our scores, we created a shared Google Apps spreadsheet with our scores and we soon discovered that it was actually just as fun to analyze the scores as to play the game!

We had been looking around for another daily puzzle that we could all play, preferably something that only took a couple of minutes and which had a scoring element. Unfortunately, very few online games took our interest. However, around that time, I had become addicted to playing Shinro Mines on the iPhone. Shinro is a little-known puzzle that has been described as a cross between Minesweeper and Sudoku.

So, once I put together the desire to enter the Cloud Developer Challenge, the need for another team puzzle with a social scoring element and my enjoyment of Shinro Mines, the choice of project became obvious!

A Social Gaming Website

There’s really two parts to Daily Shinro: the game and the social aspect.

The Game
I created the game in JavaScript using the fantastic open-source Raphael JavaScript graphics library that provides cross-browser graphics. It renders in XML for IE (boo!) and SVG for every other browser (yay!). It even supports animation.

Raphael was actually written by Dmitry Baranovskiy , a colleague of mine at Altassian. I highly recommend you look at the demo pages!

To provide a ‘daily’ concept for the game, a new puzzle is made available each day. The background picture for the puzzle is a daily selection from flickr, selected with the help of the beautiful Idée Multicolr Search Lab . It just adds a bit of spice and variety to the daily puzzle!

Social Gaming
From the very beginning, my intention was to create a ’social’ gaming experience, based upon the spreadsheet developed by my work team. Basically, scores are calculated by how well each player beats the team average. Therefore, it needs at least two people to play the puzzle and all scores net-out to zero. This has the advantage that, if somebody doesn’t play one day, they aren’t penalized.

This scoring system is implemented at two levels in Daily Shinro — Public and ‘League’.

At the Public level, players’ scores are compared to the public average. At the League level, scores are compared only amongst your friends or office co-workers who are members of the same League.

Leagues are all about comparisons within a team rather than between teams — sort of like a private gaming site for friends. Thus, everyone can create their own League and invite friends to join. The score graphs then reflect the scores amongst members of the League.

The charts are generated with some magical Apex code that gathers up League scores, compares players to game averages and then outputs the results into a Google Charts URL.

I invite you all to visit www.dailyshinro.com and play the game!

If you find any bugs or strange behaviors, please let us know!

Will we win?

Ah, that’s the big question! My personal goal is to have the site ‘mentioned’ in the results pages of the Cloud Developer Challenge. That way, we’ll feel rewarded for the long hours that were put into the site.

We’ve done several things to give us an advantage in the competition:

• We made a fun site that is attractive and enjoyable to use.
• We utilized lots of different technologies to showcase how Force.com can ‘pull together’ capabilities from across the web to build an even richer application environment.
• We added lots of pages of explanations , showing how we used Force.com technology. I’m hoping that this will help the folks at Salesforce.com promote the Force.com platform, which means that they’ll want lots of people visiting our site to help promote their technology. And what better way to do this than mentioning us in the final results?! (Clever, eh!)
• Finally, there’s this blog, which I know is read by about 1000 people per month (including some legendary Salesforce staff!). Throw in David’s blog and we’re hoping to get the attention of enough people in Salesforce that we’ll get on the short-list.

Oh, one word to the judges — we fixed a few bugs in the last couple of days. So, if you visited the site already, please visit again and see it in its fully-working glory!

It’s been a tough Challenge, but it was great fun and an utterly fantastic way to learn Visualforce and Sites. I started with zero knowledge and now consider myself a competent user of those technologies. I’d also like to thank David Schach whose knowledge of Visualforce and Sites is just incredible. I couldn’t have done this site without him.

The Bottom Line

• Visualforce and Sites are mature and capable technologies for building websites on Force.com technology
• The Cloud Developer Challenge was an excellent way to ’spread the word’ and get people to use the technologies
• You’ve got to visit Daily Shinro!
• If you know the judges, please tell them how great we are! :)

I’ve bitten bullet! I’m currently working on an entry for the Force.com Cloud Developer Challenge, which fortunately was extended to the end of August 2009.

I started a bit late because I couldn’t think of anything fun to make. Then, a few cool ideas came together and I’m spending whatever time I can on the effort. I’ll talk more about the entry in a later post, but thought it worthwhile posting my learnings as part of the process.

Visualforce

The Challenge wants something built using Force.com Sites (technical stuff) and that mostly uses Visualforce (technical stuff ).

To date, I haven’t had a business need to justify my learning Visualforce so it remained a mystery. However, that has changed with my need to crash-learn Sites and Visualforce.

The first thing I realised was how reliant Visualforce is on the Apex programming language. I had earlier postulated that Apex was at the top of the Force.com skillset, being the hardest to learn. I’m now thinking that Visualforce should be at the top, because it relies so much on Apex to add useful functionality. Using Visualforce without knowing Apex would be difficult, indeed. So, at least I did things in the ‘right’ order.

Frustration

As with developing any new application, and even moreso when trying to use a new technology, there are things that take a while to figure out. Here’s some things I learned:

Field-level security also impacts Sites. I was trying to figure out why a page wasn’t displaying some fields (they came up as blank) and why I was getting occasional "unauthorized access" errors. Yep, turned out to be field-level security settings for my Sites Guest Profile. I managed to debug it using the Field Accessibility function under Security Controls. It appears that "Guest" profiles used by Sites don’t appear in the selection set when adding a field (unless it’s a Required Field), so after creating a new field I had to go back to the Guest Profile and add access permissions. Argh!

Update: In fact, it could be a bug. The Sites and Portal profiles don’t appear when creating a custom field, but if I hit ‘Previous’ to go back to the Profiles selection screen while creating the field, the additional profiles appear. I’ve logged it as a potential bug.

The <apex:page> tag doesn’t show the page title if showHeader="false". I found it mentioned on a discussion board and mention it here in case it frustrates you, also.

Governor limitations are still our enemy but it’s slightly better under Visualforce. According to Apex documentation, the "Total number of records retrieved by SOQL queries" is 1000 x batch size for Triggers and 10,000 for Visualforce. This becomes a real stinker when used with the count() function on a SOQL query since every row is counted even though the function is only returning an integer. Thus, SELECT Count() from Contact will fail if there are more rows than allowed by the limit. This is really, really stupid. If you agree, vote for this idea to Count the SOQL count() query as a single row query.

Instant Apex in the System Log window is fantastic! I used it to test the Governor Limit with code like this:

List ls = new List();
for (integer i = 0; i < 1000; i++) {
  ls.add(new Test__c());
}
insert ls;
integer c = [select count() from Test__c];
System.Debug(c);

The Salesforce UI was really slow. Really, really slow. I used the Net tab in Firebug to see what was slowing things down, and it was continually reloading JavaScript files that should have been cached and the wholepage was taking 15+ seconds to load. Here’s a picture of the time taken after clicking the ‘New’ button to add a Custom Field:

Well, I eventually discovered that Firefox was not caching SSL pages. I turned it on (enter the address about:config then set browser.cache.disk_cache_ssl to true and now the pages load at a screaming pace!

To trigger field updates via a link, look at Scott Hemmeter’s article on how to Invoke Apex from a Custom Button using a Visualforce Page.

Stylesheets are always a pain but the Salesforce Unearthed blog has a good article Style your VisualForce Pages -for newbies that was very helpful, especially the bit about standardstylesheets="false". I also found a good article about DIV Based Layout with CSS that explains the basics of formatting a page using DIVs. Also, be sure to read the bit in the Visualforce manual that mentions how to match style id.

Static Resources have to be public. I’ll admit, I got some strange variations on this, but lots of documentation give this advice.

In fact, Salesforce Unearthed has an excellent example of how to use a Zip file as Static Resource. It lets you put your stylesheet and images in a single zip file. Fascinating!

The Bottom Line

• There’s a lot to learn to get Sites and Visualforce doing what you envisage
• You’ll need Apex and HTML skills (preferably CSS, too)
• All time for the standard frustration of coding in new systems

I came across a thread about Dataload batch size and triggers in the Salesforce Developer Community.

Several people are reporting strange behaviour in their Triggers, such that a batch of 200 records actually calls the Trigger twice, but variables are not reset. Apparently it’s an intended behaviour and not a bug, but it’s not documented.

As a result, I’ve reduced all my batch sizes to 100 to avoid any potential problems. You might want to do the same.

The Bottom Line

• Beware of Triggers with batches of over 100 records
• The online Developer Community is great for finding answers to strange situations

I had been keeping an eye on our Storage Quota and suddenly noticed that we had passed 100%. We don’t store documents on our Salesforce instance — just object data, most of which is imported from our legacy system. Unfortunately, this had been filling up and we exceeded our 1GB quote (20 users).

First, I used Mass Update Anything (available for both Mac and Windows) with a query to find some old data records. I could then delete those records and re-query. Unfortunately, it only processes about 1000 records at a time.

Then, I saw a Custom Object that I could do without. It was taking 80MB, so I deleted the object. Unfortunately, the 80MB remained allocated — perhaps because the Recycle Bin only empties after 30 days, but I’m not sure. Also, Mass Update Anything didn’t like querying that object, so I was stuck.

Then I remembered a post I had read that morning from MK Partners about using the Salesforce System Log window to mass-update some data. Matt had written some quick Apex that executes in the System Log window. So, I modified it to do a mass delete.

Two points to note:

• The SELECT statement can only return 1000 records, lest you receive the error “System.QueryException: Inline query has too many rows for direct assignment, use FOR loop“
• The whole Apex block can only use 10,000 query rows (hence the 10x loop)

So, I had to run it a few times to delete all my records. It all worked very well.

Oh, one more point. When a custom object is deleted and brought back to life, it gets renamed (eg my_obj_del__c), so take a look at the object page to find the correct API name.

The Bottom Line

• Apex can execute in the System Log window
• Apex governor limits can be mostly avoided
• It pays to read blogs!