Back in 2000, I joined a dot-com darling called Ariba. Well, their success faded pretty quickly and I often wondered if I should have taken my other job offer, from Siebel (now a part of Oracle and a competitor to Salesforce.com). One measure of relative career goodness I use is the number of job ads available for a particular technology, and Siebel always had more job ads than Ariba.

So, how handy is it to have Salesforce.com experience on your resume?

Well, a quick look at Craig’s List didn’t reveal too many openings, but I did receive an email from Valerie Day who works in ‘Talent Acquisition’ for Deloitte. They have quite a few listings for Salesforce Consultants and Managers.

I asked Valerie how Salesforce Consulting differs from other types of consulting. Here’s what she had to say:

“Salesforce provides the ability to move to a more virtual model of remote consulting which saves money, improves efficiency by reducing time wasted on travel as well as improving the life expectancy of a consultant by providing a better work/life balance.

“Implementing SFDC follows a more iterative approach to consulting as opposed to longer more drawn out waterfall consulting approach which is more common with on-premise applications such as SAP, Oracle, Siebel. This leads to faster implementations, real time development and a more interactive experience for the client.

“Because of the easy to use interface, it does allow for functional folks to develop code where as with other systems this work could only be achieved by dedicated technical resources.”

I certainly agree with that last statement. I remember the first time I met with a Salesforce sales person. They were able to answer all the technical questions themselves, which is not the norm with other technologies.

I also agree that implementations are quicker because you can prototype solutions immediately, rather than having to build the basic technology first. Not good for earning heaps of revenue for a consulting firm, but I guess that’s their problem!

For those people interested, Valerie can be contacted at vday@ (followed by the obvious domain name).

The Bottom Line

• Salesforce-skilled people are in demand out there
• Salesforce implementations tend to be shorter than the traditional on-premise applications

If you’re thinking of doing the Salesforce certification for Force.com, take a look at ForcePrepare. It has tutorials, FAQs and even a mock multiple-choice exam with answers and explanations.

I asked Naveen, the author of the site, why he put it together:

“I am a Force.com certified developer. I had cleared the exam few weeks earlier. The tutorial contents (and other material on the forceprepare site) are written by me. The tutorial contents were my study notes when preparing for the developer certification. I have developed similar tutorial sites in the past like www.preparepm.com, www.javaprepare.com and www.thecloudtutorial.com.

“The purpose of building the site is to help people prepare for the SalesForce certifications.”

I haven’t tried any of the exams, so I asked Naveen what he thought of his exam:

“I found the preparation for the exam useful. It made me understand the breadth of the Force.com platform. The exam appeared to be reasonably complex. It had some situational questions. There was not a lot of material available on the web (mock exams etc.), so I was not sure what to expect in the exam. That made the exam appear difficult.”

Well, hopefully ForcePrepare will make it easier for other people.

Good work Naveen!

The Bottom Line

• Salesforce offer certifications including one for Force.com
• The ForcePrepare site will give you good exam preparation material from somebody who has already done the exam

Fellow blogger CRMFYI tweeted this to me.

It’s a picture from the Dreamforce 2010 website showing the front-row of Dreamforce 2009, filled with Press and Bloggers.

I’m seated beside David Schach (x2od and co-author of Daily Shinro), behind Jeff Grosse (CRMFYI). I think we were busy Tweeting funny messages so they’d appear on the big screen. Or playing Tetris.

Neat, eh! ( WMY89XUNEFTB )

The Bottom Line

• We’re world-famous on a web page
• Remember to always dress well!

This is a little off-topic, but I think that my audience of readers may actually know a lot about this topic!

My company is searching for a Business Intelligence (Reporting) system, and I’d love to get suggestions of systems to investigate!

Our requirements are:

• Web-based reporting tool
• Concurrent or ‘unlimited user’ licensing — one of the core values of our company is “Open Company, No Bullshit”, so we want to give reporting access to everyone. I don’t mind if there are tiers of users (eg only some can create reports), as long as I can give access to 200+ people.
• Give users (or at least a subset of users) the ability to play with crosstabs / Pivot Tables of data, so they can slice & dice the data themselves. They should be able to select rows, columns and filters so that they can do self-service reporting.
• Give users (or at least a subset of users) the ability to create their own private reports, and the ability to publish reports to the rest of the company. There should be some form of security in place so that people can’t overwrite ‘official’ reports (eg ones created by the IT department).
• Saas or runs under RedHat (no Microsoft technologies, please!)
• Users can access on the Mac (eg Firefox, Safari as browsers)
• No need for ETL capabilities — we’ve done all that!
• Our preferred database is PostgreSQL, but we could be flexible on that

The system will report on data in our corporate databases, so I’m not looking for any particular Salesforce functionality.

Saas solutions we know about:

• Birst
• GoodData
• Zoho Reports
• PivotLink
• RightScale

Non-SaaS solutions we know about:

• MicroStrategy
• InstantOLAP
• Inetsoft Style Intelligence
• BIRT
• Pentaho
• Jaspersoft
• LogiXML
• Dimensional Insight
• WebFOCUS
• Informatica Data Explorer
• Panorama NovaView
• m-Power
• Prelytis LiveDashboard
• RapidMinder
• SQL Power Wabit
• Statistica
• Yellowfin

We also ruled out some vendors due to the need for a Microsoft platform:

• Cognos Express
• Interactive Reporting
• QlikView
• Synaptris
• Tableau
• …and many others

So, do you know of a kick-arse web-based BI tool that empowers users to create their own reports?

If so, I’d love to hear from you either via ask@theenforcer.net or as a comment on this blog. Thanks!

The Bottom Line

• I’m looking for a kick-arse web-based reporting tool
• We’ve looked at many and haven’t been perfectly satisfied
• Do you know of any others?

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′)

Oh boy, I love debugging!

And today I really had to debug DataLoader!

I do a lot of stuff with DataLoader — see my previous blogs on the topic. But I’ve always uploaded to Salesforce from a PostgreSQL database — never extracted.

DataLoader allows a job to be configured where a SOQL query is run, the data is extracted from Salesforce and the results are inserted into a database. The documentation is woeful on this whole topic, but fortunately DataLoader comes with sample process-conf.xml and database-conf.xml files that contain some examples.

The basic method is:

• process-conf.xml defines the job and includes the SOQL in a extractionSOQL parameter
• An SDL file defines the field mapping between Salesforce and the output
• database-conf.xml defines the SQL statement to use, plus the parameter types

First hint: The Field names in the SDL file are case-sensitive. Use “ID” instead of “Id” and the field won’t come across.

Second hint: All fields come across in quotes. This totally destroys any attempt to load numbers!

Here’s part of the database-conf.xml that I used:

<bean id="extractMegaphoneQuery"
      class="com.salesforce.dataloader.dao.database.SqlConfig" singleton="true">
    <property name="sqlString">
        <value>
            INSERT INTO renewals_megaphone (
               period, megaphone_date, opportunity_name, reason_lost, owner, expiry_date, product, years_owned)
            VALUES (@period@, @megaphone_date@, @opportunity_name@, @reason_lost@, @owner@,
                    @expiry_date@, @product@, @years_owned@)
        </value>
    </property>
    <property name="sqlParams">
        <map>
            <entry key="period"           value="bigint"/>
            <entry key="megaphone_date"   value="java.sql.Date"/>
            <entry key="opportunity_name" value="java.lang.String"/>
            <entry key="reason_lost"      value="java.lang.String"/>
            <entry key="owner"            value="java.lang.String"/>
            <entry key="expiry_date"      value="java.sql.Date"/>
            <entry key="product"          value="java.lang.String"/>
            <entry key="years_owned"      value="integer"/>
        </map>
    </property>
</bean>

You’ll notice I have two fields that are numbers — period (bigint) and years_owned (integer). When trying to load these fields into my database, I kept getting the message:

Sql error: ERROR: column "period" is of type bigint but expression is of type character varying.
org.postgresql.util.PSQLException: ERROR: column "period" is of type bigint but expression is of type character varying

There was no way that I could convince DataLoader that the field was numeric — I tried lots of java.lang.Integer type entries in the sqlParams, but didn’t help.

I then changed the output to csvWrite instead of databaseWrite so that I could see the output, and it came through like this:

"PERIOD","MEGAPHONE_DATE","OPPORTUNITY_NAME","REASON_LOST","OWNER","EXPIRY_DATE","PRODUCT","YEARS_OWNED"
"83823.0","2010-06-08","Joe Smith","Invalid","John","2010-03-14","a0624004000qRtkgA2","3.0"

You’ll notice that the first and last columns are meant to be numbers, but they are coming through in quotation marks. That’s what was causing the database load to fail!

So, I updated my SQL to convert the strings to numbers:

    <property name="sqlString">
        <value>
            INSERT INTO renewals_megaphone (
               period, megaphone_date, opportunity_name, reason_lost, owner, expiry_date, product, years_owned)
            VALUES (@period@::numeric, @megaphone_date@, @opportunity_name@, @reason_lost@, @owner@, @expiry_date@, @product@, @years_owned@::numeric)
        </value>
    </property>

This successfully cast the strings into numbers and they finally got stored in the database (which automatically ignored the decimal places when converting to bigint and integer).

I hope the blog post saves other people similar problems!

The Bottom Line

• DataLoader can extract data and insert it into a database
• Numbers are quoted on export and cause database errors
• I type-cast the string into numeric to enable the database to load it

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 = :o1.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 = :o2.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

A while ago, I mentioned a survey from Salesforce regarding improved Analytics capabilities.

It appears that some form of Report enhancement is definitely on its way, because I logged a UI bug with the current Report Builder, and got this response:

The issue that you brought forward is, indeed, a bug, however it is being treated with a low priority right now as our developers are planning on replacing the current report builder with a new version – making this bug irrelevant.

So, guess what’s probably coming in the next release!

The Bottom Line

• A new Reporting system is on its way