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!

I had to deactivate a Trigger in Production recently and had a darn difficult time doing so!

At first, I tried to just make it Inactive. I did this by opening the metadata file within Eclipse, and chaning Active to Inactive. However, my change could not be saved to the server because of a Test that failed. I think it was a test for the Trigger, which was no longer active! In the end, things got so confused that I decided just to delete the Trigger rather than deactivating it. (This would also be less confusing for other people to support, since it might not be obvious that a Trigger is inactive.)

I then tried to delete the Trigger in Production via the Setup screen (normal Salesforce UI). However, in Production, the Del option is not displayed.

Triggers in Production — no option to delete

So, I went into my sandbox (where I do my Trigger development) and deleted the Trigger from there. I also deleted by TestMethod that tests the Trigger.

Triggers in the Sandbox — a Delete option at last!

Finally, I went into Eclipse and did a Refresh from Server. This made the files disappear within Eclipse.

I then deployed to Production, ticking Delete next to the files in my Deployment Plan. (For safety, I always Deselect All when deploying, then sort by Type. Only then do I tick which files to deploy, since choosing the wrong ones could cripple Production.)

The Bottom Line

• It’s more obvious to delete Triggers than deactivate them
• Deleting Triggers from Production isn’t straight-forward
• Delete them from Development/Sandbox first, then deploy the change to Production

I’ve been working on a fun problem for Force-Friend Nick (Hi Nick!).

The requirement:

• When an Opportunity is closed, workflow sends an email to the Accounts people (who don’t have a Salesforce login)
• The email is to include address details of the Primary Contact associated with the Opportunity

Some relevant information:

• Contacts are linked to Opportunity objects via the OpportunityContactRole object, which contains an OpportunityId, a ContactId and an IsPrimary flag (plus various other fields)
• Cross-object formulas can’t span the Opportunity-Contact relationship

The Solution

There are several ways this could be implemented:

• Move the email generation from workflow to Apex, which would give full access to the object model (but lose the easy user interface)
• Create a Trigger on OpportunityContactRole to copy information onto the Opportunity (unfortunately, we discovered that you can’t create a Trigger on this object!)
• Create a Trigger on Opportunity to ‘pull’ the information from the Primary Contact onto the Opportunity record

We decided to go with the Trigger on Opportunity. The information is only wanted when the Opportunity is closed, so it means we could write the Trigger to fire on closure of the Opportunity. It then looks for the Primary Contact (as flagged by the IsPrimary field) and copies it to a Custom Field (Primary_Contact__C) on the Opportunity object, which can then be used by cross-formula functions within workflow.

Here’s the code for the trigger:

trigger SetPrimaryContact on Opportunity (before update) {

   for (Opportunity o : Trigger.new) {
     // Check if the Opportunity just closed
     if (o.IsClosed && !Trigger.oldMap.get(o.id).IsClosed) {
       OpportunityContactRole contactRole =
            [select ContactID from OpportunityContactRole where IsPrimary = true and OpportunityId =  .id];
       if (contactRole != null) {
         o.Primary_Contact__c = contactRole.ContactID;
       }
     }
   }
 }

Simple result, but took a bit of thinking to create it.

The only funny-looking code is !Trigger.oldMap.get(o.id).IsClosed, which retrieves the old value of the IsClosed field to see that it really changed. I use oldMap because I read somewhere that you should not rely on Trigger.old and Trigger.new being perfectly aligned (that is, for example, Trigger.old[4] and Trigger.new[4] do not necessarily refer to the same fields). However, I can’t find that reference anymore, so feel free to tell me if I’m right or wrong!

The Bottom Line

• Not every object permits a Trigger
• Cross-object formulas also have their limitations
• Sometimes a bit of Apex trickery saves having to buy licenses for extra users!

Budding psychologists out there may be aware of Maslow’s hierarchy of needs, which is part of his 1943 paper A Theory of Human Motivation. Maslow puts human needs into a hierarchy:

Following Maslow’s lead, I wish to propose a Force.com Hierarchy of Skills:

The hierarchy has many omissions and contains much that is apocryphal (or at least wildly inaccurate), but does give an indication of the progression of skills while learning Salesforce.com and the Force.com platform.

Let’s take a look at each component:

Standard Objects & Functionality represent the first level of knowledge, which most users of Salesforce.com discover over time. Interestingly, while I’m the Administrator of my company’s Salesforce.com system, I don’t actually know all of the functionality, such as how to use Activities and Email templates. I can trust the users to figure that out.

When using the Force.com platform, there are no standard objects or functions. (Well, there’s a few like Users, but not the Sales-oriented stuff like Opportunities and Campaigns.)

Administration functions give access to the inner workings of the platform, such as adding Objects and Custom Fields, modifying Page Layouts and managing Security.

Reporting could arguably fit into either of these first two levels — it’s simple enough to give to Users, but is easier to understand and build reports if you know about Custom Fields and Formulas.

Workflow and Data Import build upon the knowledge of objects and fields. I’m talking about using the Data Loader rather than the Import Wizard. In fact, during a visit I once had to the Salesforce.com office in San Francisco, even the developers down-talked the Import Wizard. The Data Loader is very powerful and can do some magic with External IDs, but that needs a good understanding of the lower-level Admin skills.

Workflow similarly builds upon Admin skills, requiring knowledge of how information is updated and linked between Objects. It’s not hard to configure, but can be challenging to get ‘right’ in a system with lots of exceptions and strange uses.

Visualforce & S-Controls begin the concept of on-demand programming. I’ve grouped them together because Visualforce is actually replacing what S-Controls previously enabled. S-Controls have the benefit of using the familiar JavaScript language, but Visualforce is more integrated with the Force.com platform.

Apex represents the, umm, ‘Apex’ of Force.com skills. It provides powerful low-level Triggers and custom logic that can be hard to define in a point-and-click system. However, this power comes at the cost of complexity in learning the language, the development environment and the nuances of Force.com development. Fortunately, it comes with good error-prevention capabilities such as strong type checking, automatic reference checking between Apex code and objects, and good deployment tools.

I’ll admit that I was considering putting Visualforce above Apex, since Visualforce can ‘use’ underlying Apex logic (eg controllers). It’s also newer than Apex and you’re likely to find more Apex-skilled administrators than Visualforce-skilled. Nonetheless, I’m keeping Apex at the top due to its higher complexity and the great pun associated with the word ‘apex’.

I’ve got a few experiments in mind to test the validity of this Hierarchy. I’ll post the results when finished.

The Bottom Line

• If Maslow had access to Force.com, his Apex would have been different

Well, this little problem took my whole morning…

I wanted to give users a way to invoke my Webex Apex code, so I added a custom Button to my Campaign Page Layout.

I created a Detail Page Button that called the Apex:

sforce.apex.executeAnonymous('Process_Webex.process();')

This worked fine during all my testing, but when I moved it to Production, it generated an error:

Interestingly, it only happened in certain situations:

• Logged in as System Administrator: Worked
• Logged in as a Normal User: Did not work
• Logged in as a Normal User via the System Administrator “login as another user” function: Worked

Further investigation of the HTML source code showed that <script> includes for connection.js and apex.js were being added to every Salesforce.com page only when logged in as a System Administrator (or “as another user”), but not as a Normal User.

I eventually found an article on the Community Forums titled “Call an Apex class from a button“, written by one of the authors of the Force.com Cookbook that recommends inserting the following code into the Button:

{!REQUIRESCRIPT("/soap/ajax/10.0/connection.js")}
{!REQUIRESCRIPT("/soap/ajax/10.0/apex.js")}

And that worked a charm!

Update 2008-07-30

Word has come through that a fix is being rolled-out (tonight!), so it was a real bug!

The Bottom Line

• Seek and You Will Find (Translation: Google rox!)
• Share and Enjoy (Translation: Sharing knowledge amongst the Community benefits all)
• Even the best testing efforts can be thwarted unless you logon as the actual users, not just via the administrative Logon function

My company sells software via the Internet. Wonderful business model, lots of happy customers all around the world. The software is priced so cheaply that we can’t afford to do in-person demonstrations nor respond to RFPs. Instead, we use WebEx to give online demonstrations.

The Sales and Marketing people would love to track the success of these Webinars, where “success” means that somebody bought something. So, I decided to use Campaigns to track Webinars, with the Campaign Member status indicating whether they had bought something. Though the clever use of a trigger, the status is updated when a purchase is made.

However, this still left the problem of loading the initial details about the Webinars and attendees (Contacts). Doing this manually would involve:

• Creating a Campaign for each Webinar (or cloning an existing one) and filling in details (product, presenter, date)
• Checking whether each attendee already had a Contact record in Salesforce.com (we don’t use Leads), and manually creating a Contact record for those not found
• Linking each Contact to the Campaign, either via Manage Members on the Campaign (very slow), or by clicking Add Campaign on each Contact record (a bit easier)
• Setting the Campaign Member status to ‘Attended’

We can’t use any Import functionality, since it is designed to import Leads (which we don’t use) or to update the status of members (not all of whom are necessarily Contacts yet).

For all this work, Sales would have the luxury of tracking how many people attended each webinar, and how many of them later purchased. But were they happy? No! They wanted it all done automagically.

So, I examined the format of a WebEx attendance export list and created a custom object to match. Users import the list via the Import Custom Object wizard, which then fires a trigger. The trigger then:

• Creates a Campaign record (if required)
• Creates Contact records (where required)
• Adds a Campaign Member record (which links Campaigns to Contacts)

This was a day-long effort, only to be foiled at the last moment by DML governor limits. These limits are put in place by Salesforce.com to protect their multi-tenant architecture from being slowed down by individual users. Specifically, Triggers are only allowed 20 DML calls (insert, update, upsert, merge, or delete). There are various Bulk-Safe methods to avoid these limits but my Trigger was just too complex for such trickery.

Apex script unhandled trigger exception by user/organization
WebEx_Attendee: execution of BeforeInsert
caused by: System.Exception: Too many DML statements: 21
Trigger.WebEx_Attendee: line 128, column 9

So, I had to revert to using a static method off a class, which I hadn’t tried before. Somewhat more fiddly, since Force.com does a nice job of providing incoming records within Triggers, but it was fairly easy to convert the code from being a Trigger to being a Method.

Finally, there was the problem of how to activate the code. Triggers are easy, since they work when data is loaded. This situation, however, had to be user-controlled. So, I added a Button on the Campaign Page Layout which called my Apex code via the executeAnonymous function.

It doesn’t give a very user-friendly notice, but it does work! Please let me know if you have any recommendations of better ways to activate the code.

Update 2008-07-23: Even less DML

Well, I thought everything was good, but when I tested my code against real data (still in the sandbox, but using more data than just the TestMethod), I starting hitting even more Governor Limits.

The problem was, I had a major loop that did the following for each record:

• Inserted a Campaign (if it didn’t already exist)
• Inserted a Contact (if it didn’t already exist)
• Inserted a CampaignMember

I had to ‘insert’ each time, otherwise my next iteration through the loop wouldn’t ’see’ the recently added records. For example, if the same person attended two webinars, the first imported line would cause them to be created, but the second imported line had to detect the earlier insert so as not to insert the Contact again. (Confused yet?)

So, I totally rewrote the code to perform three loops — one each for campaigns, contacts and customers. As a result, I’ve gone from potentially 6 DML calls per record to 6 DML calls in total! It takes A bit of different thinking, but the results are wonderful — especially where one click and a couple of seconds suddenly produces hundreds of beautifully-linked records.

The Bottom Line

• The Force.com platform is amazingly expandable
• Apex gives ultimate flexibility since you can write anything
• I really, really enjoy hacking!