Tuesday, May 29, 2012

A Random Number Generator For Dynamics CRM 2011

This blog is a bit of a departure in that it centers around a bit of code. I am not a coder. I cannot write jscript, .Net or register a plugin. I know CRM from a functional perspective and where code can be used to augment the functionality. I leave the actual writing of code to others. The advantage of this is it gives me great motivation to come up with unusual ways to solve problems. An example of this was my global search / universal search tool for CRM. Totally codeless, reasonably practical and very easy to maintain and configure.

Today I am looking at a random number generator.

For those of you still adverse to the curly braces and semi-colons, you can skip to the section where I talk about updating the random numbers en masse, which is codeless.

Why Do We Need a Random Number Generator?

It is an excellent question. I personally need it for another blog post I am planning to do on network analysis but another reason might be to create a random sample of contacts for a marketing campaign, or some other business case where a representative sample of records are needed but it would be impractical to use the entire set of records. Another reason might be to populate data for the testing of reports, dashboards or graphs.

Why Did You Abandon the Righteous Path of the Codeless?

I did try to do it without code using workflows (e.g. populating a number field with the current date and time) but this was not possible. With the ability to create custom workflow assemblies with the next major update I imagine a whole new world of workflow fun will open up. Now all we need are synchronous workflows, calculated fields and the dialog DB Query function in workflow and we are set.

How Do We Do It?

The first thing we do is create a field to hold the random number. In my case I have created the imaginatively labelled ‘Random Number’.


For transparency I added this to the form and to the default view.


Now we need the code.

The jscript Code

Thanks to Rhett Clinton, another aussie CRM MVP for the key bit of code:

var num = Math.floor(Math.random()*101);

While an aussie, he lives in the UK, so his quiet demeanour and respectful attitude often mean people confuse him for being English or a New Zealander.

So what is happening in this code line? Well we are taking a variable ‘num’ and setting it to a value. Starting from the inside and working out, Math.random() returns a number BETWEEN 0 and 1 i.e. never equal to them. Multiplying this by 101 gives us a number between 0 and 101. Math.floor rounds the result down and, as the result will always be greater than but not equal to zero and less than but not equal to 101, the result is a number between 0 and 100.

Next we need to assign this value to the field we have added to the form. To do this, I Binged about and came across Hosk’s Javascript Xrm.Page basics. Other than through his active online presence, I do not know Ben Hoskins. His blog suggests he likes a beer and calls it as he sees it which means I like him already.

Combining the gems from Hosk’s site with Rhett’s code and with the input of my colleagues Bhavin Mehta and Rafael Urbano you get the following:

function RandomNumber()
var num = Math.floor(Math.random()*101);

Putting this into a web resource, we then attach it to a form event e.g. OnLoad and we are good to go.


Using OnLoad means every time the form is opened, the random number value is changed. This also means the form wants to be saved, even if nothing else has changed. If this proves impractical you could use the OnSave event instead.

The result is a field whose value changes every time you open the form, although it only triggers on the opening of the form (for OnLoad) or the saving of the form (for OnSave). The bulk edit form or changing values through other methods, such as workflow, do not trigger the script.


Other Approaches

Another approach would be a plugin. This would remove the need to open the form and could be triggered off, for example, the ticking of a box on the form. Therefore we would tick the box via mass editing or workflow and the random number code would be fired.

Mass Updating Values

The biggest problem in using jscript is when you need to update multiple records at the same time. Because jscript will only fire when the form is opened it means that you need to open up the form for every record you want to update. Another approach is to use data enrichment. The advantage of this approach is you can insert a random number onto the records with no code whatsoever.

Firstly hit the ‘Export to Excel’ button on the list of contacts you want to update.


For static exports you will see the option to re-import the data


Once we have it in Excel, we can use the Excel RANDBETWEEN() function to generate our random numbers. The function RANDBETWEEN(0,100) does the trick.


We then import our data with the import wizard and our records have random numbers associated to them.


NB: The numbers in the previous two screenshots are different only because I took the pictures over different runs. Usually they would be the same.

This approach is a little manual but has the advantage of not requiring the jscript to generate the random numbers.


There you have it, the ability to add a random number to a record. Using jscript the number will change every time you access the form or, via re-importing, the number will change every time you export and refresh the random number via Excel functions. Using a plugin allows for more options for updating the field. Enjoy!

Sunday, May 20, 2012

Salesforce First Quarter Results For 2012

Salesforce just released their first quarter results for 2012 (what they call 2013 Q1) so I thought it would be interesting to see how salesforce kicked off the new year. Obviously the growth in revenue is the main push of their messaging but the numbers show a few more things.

For previous analysis (of which there is a fair amount, use the search engine on the left with the keyword ‘salesforce’. The last analysis was on the full year results).

Where Do I Get My Numbers From?

There was a recent LinkedIn survey asking which CRM will be the leader in five years. In that I put in my five cents regarding my fears for the long term prospects of a company failing to make a profit and was challenged on where I was getting my numbers from. As usual, all financial numbers come from the detailed financials on salesforce’s own web site. I use the GAAP numbers (Generally Accepted Accounting Principles) rather than the non-GAAP numbers used in salesforce press releases.

What is the difference between GAAP and non-GAAP? GAAP financials follow conventions adopted by bodies such as the Securities and Exchange Commission (SEC) so that different companies can be compared on an equal footing. Non-GAAP means the company in question deviates from these conventions, limiting the ability to compare them to other companies.

Often the reason cited for such a deviation is because the company believes their model is unique and that usual accounting does not accurately reflect their prosperity. Another reason companies deviate from the GAAP path is because of ‘one-off’ events skewing results (what is called pro-forma accounting). While certainly a pioneer in cloud software, I am not convinced they need to deviate as they do such that substantial losses and negative free cash flows turn into non-GAAP profits and non-GAAP positive cash flows.

In other words, if you want to hear about ‘unbooked work’ and ‘deferred income’ or about non-GAAP EPS go to the salesforce press center. Here I use the numbers salesforce report to the SEC.

Revenue Growth

Here I will bring in some addition data, taken from money.msn.com ten year summary for salesforce. The annual revenue growth was:

To January 2004: 88%
To January 2005: 84%
To January 2006: 76%
To January 2007: 60%
To January 2008: 51%
To January 2009: 44%
To January 2010: 21%
To January 2011: 27%
To January 2012: 37%

We see that, like many small start ups, initial growth was massive but tapered as the company grew larger. More recently, with Benioff’s strong focus on revenue, growth has picked up again. Looking at the numbers from salesforce’s detailed financials we see the nature of this new-found growth.


The black line is a two-period moving average. Moving averages tend to smooth out the bumps to give a clearer picture of the underlying trend. In this case we see that whatever Marc was doing to generate the growth in sales is starting to wear off with the curve flattening.


Again, using the ten year summary, we see margins (income as a percentage of revenue were):

To January 2003: –19%
To January 2004: 4%
To January 2005: 4%
To January 2006: 9%
To January 2007: 0%
To January 2008: 2%
To January 2009: 4%
To January 2010: 6%
To January 2011: 4%
To January 2012: –1%

So while revenues increased over the past couple of years, margins have suffered and are back in the negative which has not been seen since the  company’s early days.

Again, the quarter-by-quarter graph from the salesforce detailed financials is even more damning.


To compare to the revenue growth, about the same time revenue started improving (2010 Q1) was the same time margins started suffering. The situation now being such that for every $100 of revenue, the cost to the business is $103.


Back in February I suggested that while the business was losing money, the bleeding seemed to be constant and not getting worse. This has proven to not be the case.


While the drop in margin is small (about 2%) because the revenue has increased so much, the loss this quarter is greater than the combined loss of the previous three quarters combined. Let me write this in bold, large letters to drive home the point.


Salesforce managed to make a loss of $19m compared to the $12m lost of the previous three quarters. Part of me wonders whether there was some creative accounting done to keep the previous quarter in line with the others to round off the year, pushing the bad news to this first quarter, giving another three quarters to make up for lost ground before the next annual report. If this is the case I would expect to see the next quarter recover slightly from this dreadful result.

What surprised me is, in the announcement call for the quarter, Benioff describing the quarter as a “great start…absolutely spectacular” and the CFO, Graham Smith, describing is as “excellent start…better than we expected”. If this is a good start I would hate to see a bad one. I understand Benioff is focussed on revenue growth but when he said in the previous quarter’s call he was ‘committed to  increasing our margins’ I was thinking he would put the company towards profitability not sink in further into the red.

What surprised me more was not one analyst on the call for this quarter picking him up on it. Everything was about the ‘deferred income’ and ‘unbooked work’. Here is the thing:


Selling $10 bills for $9 does not make for a healthy business and lately, to get the revenue growth up, they have been selling them for $8. The analysts are asking how many $10 bills are likely to be sold next week when they should be asking “why are you selling yourself short?”

Stock Sales

Back in February I asked what the directors of Microsoft and salesforce were thinking. I looked at the messages they are sending to the market compared to their personal share sales. Graham Smith, CFO, despite lauding the praises of the company in every quarterly announcement (this one included) continues to offload his ownership of the company. Here are the numbers if you are interested. Every week Graham exercises some options and also has an automatic share sale in place. The automatic shares netted him around $2.7m in April and the share options generated around $320,000.

While there is an argument to say share ownership and options aligns an employees interest to that of the shareholders, there is also an argument that says if I am exercising options and selling shares every week I will, within my ability, do whatever it takes to convince the market the share price should remain as high as possible. The difference in these goals is the difference between the long term prosperity of the company and telling the market what they want to hear to maintain the share price in the short term.

While I have no doubt Graham is acting with all appropriate care in his capacity as CFO, I can also understand if he feels conflicted in his role and I do not envy his position in this regard.

Subscription Numbers

Unfortunately salesforce kept tight lipped about the enterprise deals they did this quarter and about their subscription numbers, so I, again, have to speculate based on their revenues. My best guess, assuming they are maintaining a revenue per subscriber per quarter level of around $150 is that they have 4.6m subscribers. Assuming the average company size is still around 35, this means the number of customers is about 132,000. The bad news is, if these numbers are correct, each month each subscriber costs salesforce $1 ($1 * 4.6m users * 4 months = the approximate loss for the quarter).


I understand Marc is keen to capture market share but it seems the price for doing so is proving very high to the point where the health of the business is being jeopardised. While the trend of tapering growth as the size of a company increases has been rebuffed, the cost has been margin to the point that the company is making significant losses.

The question I have is “where to from here?” Being in the red is simply not a sustainable position, otherwise every company would do it and pay no tax. Salesforce are quick to point out there is plenty of revenue in the pipeline but this is only of benefit if they can make a profit from it. Will salesforce raise prices? Will they find new efficiencies in the business to reduce operational costs while maintaining service? Will they slash sales and marketing spend, killing revenue growth, cruise along on subscription income but inevitably sacrifice share price? Time will tell.

Monday, May 14, 2012

Moving To The Cloud: Update

How the world can change in a month. Back in mid-April I wrote about moving my data to the cloud. In previous parts I talked about using Office 365 and moving ten years of e-mail across to the cloud Exchange server.

So what has happened since last month? Microsoft has released their SkyDrive App and, while Mesh is still operational, with Microsoft announcing the death of the Live brand (of which Mesh is a key part) it is probably a fair speculation to suggest it is a product on the way out.

To this end, my cloud data strategy has shifted somewhat.

So What Is In the SkyDrive App?

The application itself is pretty simple. Once you install it, it copies the entire contents of your cloud SkyDrive to a local folder of your choosing and then keeps it synched. Given Microsoft were giving 25G for free (new signups only get 7G), this had the potential to fill up one of the 32G drives on my PC tablet so I have only installed it on the old laptop.

It is like the old Mesh but with less fine control on what gets brought down and where it goes. There is also no option for synching shortcuts but, apparently, this will be a standard part of Windows 8.

The 100M limit has been increased to 2G, although for larger files you can still use Gladinet to chunk them (I am not sure it is worth it as for files larger than 2G, I’d probably go with a-drive if they are simply family photos or movies).

Finally, if you have really long folder paths and file names, they may get picked up when SkyDrive starts copying down files. In this case it suggests you rearrange things and start the app again.

What About Mesh?

I used Mesh for a couple of purposes. The first was to do what the SkyDrive app does i.e. keep a local copy of information. The second was to sync certain folders onto my wife’s computer and also to my work computer. Short of copying EVERYTHING down to those other machines this is no longer possible. The best that can be done is sharing folders online via a shortcut. While do-able, this is little more than a workaround.

I have now killed Mesh. It was actually fairly simple. I copied the folders to a new folder in my SkyDrive called Mesh, turned off Mesh for all computers and then took a backup in case things go awry. I can always browse online if I need a folder at work and I have given my wife the appropriate shortcuts for the stuff she needs. It is a little clunkier than before but workable.

What About New Stuff?

Essentially, I have the SkyDrive folder in the My Documents area. Anything new is held on the desktop and moved across when things get too crowded. With the relatively complex folder structure of a Windows user account, this is not a great solution but I will see how it goes. My fear is something being dropped into another Documents folder and being overlooked.

Hopefully, either the app will improve over time or Windows 8 will simplify the user account structure.


I am not the biggest fan of the new SkyDrive app but transitioning to use it was relatively straightforward and I am sure the feedback from the web will coax Microsoft into tweaking it. With devices becoming more cloud-friendly for data storage I am not sure forcing a user to dump down their entire SkyDrive is the greatest choice and my prediction is this will be the first to go.