Monday, December 28, 2015

My New Blog Home

Alas, it appears Microsoft’s Live Writer is no longer supported by Blogger so I have finally moved to WordPress (
There will be a bit of a transition as I tweak the layout but I promise the orange is gone.

Wednesday, December 23, 2015

Dynamic QR Codes for Dynamics CRM

For some reason, Live Writer is failing to publish to Blogger which makes writing blogs tricky. Combined with the travel I am doing this Christmas means it will be tricky for me to knock out my usual three blogs a month. Either way have a great Christmas break ;)

I recently had a demo where I needed to generate QR Codes for CRM records and then make the codes available for printing. The scenario was for events where the QR code would appear on the invitee’s email and then the recipient could print it and be scanned at the event to confirm their attendance. This was something I had never done before and I was not sure it could be done. Generating QR codes is easy but dynamically generating a unique QR code for every Contact record in CRM and make it available for marketing is something else. Thankfully a bit of research (and a tiny bit of code later) and I was good to go.

What to Code

QR Codes can code practically any text you like. In this case I needed to code the web address for a Contact record. Every record in CRM has a unique web address which you can see when you click the ‘Email a Link’ button.

Generating the Code

The trick to generating the QR Code is the Google Charts API. In short, you construct the right URL and Google renders it as a QR code on the fly. For example, here is the QR code for my blog:

The API for QR codes is deprecated i.e. no longer supported by Google, but Google says it will remain active in the foreseeable future.

Now all we have to do is bring the CRM URL and the QR Code generator together, put the result in a field then add it to an email and we are set.

Bringing it Home

The first step is to access the unique web address of the record. To do this I use a workflow to populate a field on the Contact form with the record’s URL on creation.

The unique web address of the record is a special field accessible in workflows. Just look for ‘Record URL’.

The next step is to construct the web address and put it into the QR Code field so that when it is clicked, or added to an email, it will display the QR code.

In principle, this could also be done with a workflow but when I tried it would not work. It turns out the ‘&’ characters in the CRM URL for the Contact record confuses the Google API and we need to ‘encode’ the URL. This means replacing the ‘&’ with ‘%26’. Unfortunately I cannot do this with workflows or calculated fields so I needed to get some code written to do this and populate the QR Code field. Thanks to Steven Zhang for his flawless code (I have not got permission to provide the code but if you bribe your local dev they should be able to replicate the code without too much difficulty).

The end result is two populated fields on the Contact record which get populated on the Contact’s creation: one is a URL to the record and the other generates a QR code which, when scanned, also goes to the record.

In the case of our demo, we used ClickDimensions which allows you to add the web address of an image to an email template to be rendered on delivery, which was perfect.


The ability to generate QR codes on the fly for CRM records is very powerful. In this case I am using it for Contact records for events, but it could also be used for products for picking slips, for event flyers or to link with Adxstudio portals to direct non-CRM users to forms or sites so they can interact through the CRM portal.

Even outside of Dynamics CRM, the Google API for generating QR codes is a great tool to have in the toolkit.

Monday, November 30, 2015

Calculating the Day of the Week Without Code

This blog is inspired by a forum post I read where someone asked to display the day of the week in a CRM view. Unfortunately, I cannot find the request now but it is possible (and without code).


In the above, the Input Date is the date we want the date for and the Day of the Week shows the correct day.

The Calculation

For this one we need the following fields.


Working backwards:

  • Day of the Week: An option set of the days of the week (set via a workflow)
  • DOTW Number: A Whole Number which shows a number representing the correct day of the week 
    (a calculated field and, in this case, a number between –3 and 3)
  • Dodgy DIV: Strictly speaking, not a mathematical DIV but close enough for our purposes. In our case, this represents the week our Input Date is in. It is a calculated field.
  • Input Date: The date of interest, entered by the user.
  • Reference Date: A date we know the day of the week for. In this case, 1/1/2015 which was a Thursday

To anyone outside of the USA, Belize, or the Federated States of Micronesia, I apologize for using “Middle Endian” formatting for my dates. It is a blatant play to the American market on my behalf and does not affect the calculations.

In terms of the Reference Date, my preference was to incorporate this into the Dodgy DIV calculation but I could not figure out how to input a fixed date e.g. “01/01/2015” into the calculated field. CRM either thought it was an integer or thought it was a string. If anyone knows how to do this, please leave a comment at the end.

Given I could not feed my reference date directly into the Dodgy DIV calculation, I had to automatically enter it into a field via a Business Rule. Here it is.


I tried using the Set Default Value action but struggled to get it to behave so I just used the Set Value action in the end. I also was forced to put in a condition so I selected one which is always true.

With this value set, I could then calculate the Dodgy DIV.


A couple of issues with this formula, which is why it is dodgy. What I wanted it to do was calculate the difference in days between 1/1/2015 and our Input Date and then divide it by seven. It turns out that the DiffInDays function has a pretty huge bug in it, in that it gets the difference in days wrong. I found that if I put in the same two dates into the DiffInDays formula, it returned –1 and not 0, as expected. This is why I add one.

The second issue is, in C (the language I used to code in a loong time ago), if you divided two numbers and put them into an integer variable, it rounded down, effectively being a DIV operation. Dynamics CRM does not play this way and applies a normal rounding operation (x.5 or more goes up and less than x.5 goes down).

Using my Dodgy DIV, I can now calculate the day of the week. The DOTW Number (Day of the Week Number) is also a calculated field.


This is where the clever trick comes in (if I do say so myself). Here we calculate the difference in days again, add one to account for the bug in the DiffInDays formula and take away the Dodgy DIV value, multiplied by 7. Because the Dodgy DIV field has applied a rounding, the difference is the Modulus (also know as the remainder) or it would be for a pure DIV function. In our case we generate a number between –3 and 3, rather than a number between 0 and 6.

Finally, we have our days of the week. Unfortunately there is no simple way to set the Option Set Value with a formula using a number so I had to use a real time workflow.


The workflow is triggered on creation of the record and on the changing of the Input Date field. There are seven IF statements, one for each day of the week, linking the DOTW Number to the right day.

Once all of this is done it works like a treat, with the day value being set on the saving of the record. It is then a case of adding the Day of the Week field to the view you want to see it in.



I like this solution as it opens the way for quite a few other requests seen in CRM systems. For example, with the day of the week we can check whether a day is a work day when setting an appointment. Although I have not fully figured it out how yet, I imagine we could also use a similar technique to work out the number of working days between two dates. So many possibilities thanks to calculated fields.

If you are not exploring how calculated fields can help you manage your business processes in Dynamics CRM, perhaps you should because, as you can see above, they open up a wealth of options, previously inaccessible, without using code.

Tuesday, November 24, 2015

The Salesforce Drinking Game



Here is the Salesforce drinking game I play when listening to the quarterly transcripts (because even I have to make it a little more interesting). You pour a drink every time they mention Non-GAAP. You get to take away a drink if they mention GAAP.

I currently have 12 drams of one of Scotland’s more affordable single malts in front of me thanks to the drinking game. Every quarterly review from now on I will report the ‘dram count’ for the report. Given this may run my whisky supplies dry, I am open to sponsorship by whisky makers or bottle donations (come on Marc, I know you are good for it). All contributions welcome.

Why is Non-GAAP so important to the quarterly reports? Because it allows Salesforce to turn their persistent losses into a mythical profit. If they applied the same accounting treatment to their carbon emissions, they could claim that every time you use Salesforce, a glacier grows.

I fully expect to detox when Salesforce turn a profit because all of their reporting will transform into GAAP numbers. Until then, here is mud in your eye.

Numbers of Note

Normally I post the big table of numbers. I will still use the big table of numbers but it strikes me you are probably more interested in the insights than the data so I will still be using the same numbers, just not filling up the blog with the table. For those new to the game, I take my numbers from Salesforce’s own website but rather than use the ‘creative’ numbers of their press releases, I use the ones they report to the SEC because you get in trouble if they are wrong.


In the last two quarters, Salesforce was heading in the right direction. A rental adjustment meant a tiny profit was turned in quarter one and the loss was not huge in quarter two but, unfortunately Salesforce are back to their usual form this quarter with a $25m loss. Compared to the losses of the past (Salesforce lost a little over a quarter of a billion dollars last financial year) it is not huge but still, it is not a profit.

Transaction Growth

I predicted last quarter that transactions would go flat. Fortunately, for Salesforce, I was wrong.


Transaction growth, which had been steadily falling, had an uptick this quarter, back to 16% growth.

Revenue and Cost Growth

The year on year revenue and cost growth tell a story about Salesforce’s history and gives insight on where they are heading.


If we look at where the solid green and red lines cross, we see three periods in the history of Salesforce.

The Sensible Years (before 2011 Q1 i.e. before 2010)

Before 2011 Q1, that is 2010, revenue growth was on top. The business flourished combining high revenue growth with increasing profits and market share. Accounting did not have to be creative and the business could do no wrong. Here is the transcript from 2010 Q4 where, not surprisingly, reporting was in GAAP numbers. This changed, and in 2011 Q2, we had a mix of GAAP and Non-GAAP reporting and we finally transitioned to full Non-GAAP reporting in 2011 Q4, just prior to the losses appearing.

The Aggressive Years (2011 Q1 to 2015 Q1 i.e. between 2010 and 2014)

This is when marketing and sales costs grew faster than revenues, thus the creative accounting. The official line is the strategy ensured aggressive market share growth. My interpretation is the executive, being compensated in shares, wanted to maintain the share price while they offloaded their holdings (the executive have consistently sold their shares in Salesforce despite their optimism for the company). With the market fixated on revenue growth, the decision was made to sacrifice profit to ensure revenues continued to go through the roof. Non-GAAP reporting helped hide the losses which resulted.

Interestingly, Salesforce only started making a loss a year into this period (just after the switch to Non-GAAP reporting) so if you wanted to predict when Salesforce would go into the red, the growth chart above predicted it a year in advance. For Salesforce, revenue and cost growth is an excellent leading indicator.

The Buyout Years (2015 Q1 to the present i.e. 2014 onwards)

Since 2014, Salesforce has focussed on getting marketing and sales spend under control and they are doing a pretty good job of it. The last couple of quarters, the growth difference has been a consistent 5%. If revenue and cost growth is a leading indicator, this suggests profitability in the near future.

As suggested in my labelling of this period, and influenced by recent events, I see this as the executive getting the books in order for a buyout.

The idea of a return to profitability is also backed up by the margin graph.


While the solid blue line is all over the shop, the four-period trend line is heading upwards towards the positive.

Earnings Call Buzzword Bingo

Here is the list of words mentioned 10 or more time in the last five quarters


2015 Q3

2015 Q4

2016 Q1

2016 Q2

2016 Q3

Number of words








































































Sadly we lost ‘Marketing’ and ‘Enterprise(s)’ which seem to no longer be a focus. I have heard mixed reports on the success of the ExactTarget acquisition which may explain why Marc is moving away from talking about it. In terms why ‘enterprise’ is on the nose, I assume Salesforce is no longer bringing in the big deals they used to brag about so much.

As for other trends in the words, ‘Revenue’ mentions are steadily declining as the business slows down. Talk of the cloud and Salesforce as a platform is also diminishing. Marc says the literally hundreds of CEOs he talked to in the quarter do not care about the cloud, but about their customers so maybe this is to blame. This being said, one of the key selling points of the past was the Force platform so to move away from this is curious.

Google Trends

I am dropping the Google trends from the quarterly report. Dynamic CRM has won the Google search war and is the more international product. To replace it, I am adding the ‘Truth in Transcripts’ section below.

Insider and Institutional Sales

It is a Black Friday sale for the shares of Salesforce.


2015 Q3

2015 Q4

2016 Q1

2016 Q2

2016 Q3

Insider Sales






Institutional Sales






As I predicted last quarter, the insiders (such as the board) have increased their selling and the institutions are also selling out, going from selling 3% of their holdings to almost 6%. The insiders are getting nervous? Do they think the price of the shares is about to crash?

Looking to the Future

I predicted Salesforce would break even but they made a 25 million dollar loss so no joy there. In terms of revenues, I was 4% off which is not too bad. Next quarter I predict $1.8b in revenue and another $25m loss.

Truth in Transcripts

My new section where I examine the claims made in the quarterly transcript and challenge them in reference to the GAAP numbers reported to the SEC. All quotes taken from the Seeking Alpha transcript.

Here are the creative claims this quarter:

“And of course, Salesforce, as you can see will be the fourth largest software company in the world next year, but you can see that we'll be one of the only software companies ever to reach $10 billion in revenue.” – Marc Benioff

I think this was a genuine mistake by Marc. I think he meant to say “…$10 billion in revenue within <x> years”. Microsoft alone pulls in over $20b a quarter in revenue so the statement is clearly wrong.


“…we also delivered…margin improvement” – Marc Benioff

In Non-GAAP terms perhaps. In GAAP terms, margins fell 1.4%. As shown above though, the longer term trend suggests things may improve in the future.


“…we are also deeply committed to continuing to increase our profitability, and the results this year are evidence of that.” – Marc Benioff

“From a bottomline perspective, we delivered another quarter of improving profitability.” – Mark Hawkins

GAAP margins have gone down each quarter this financial year as have profits with the last two quarters showing a loss. The long term trend is positive but this quarter, and the previous one were not great for margin improvement.


So the misdirection is mostly around profitability. Clearly this is the chink in the Salesforce armour. It is really difficult for them to make as much noise as they do through marketing and make a profit.


Salesforce continues to struggle with making the business profitable and, while things have improved compared to the aggressive years, the executive are still wrestling to stabilise the business. If they can keep revenue growth above cost growth they should be ok but it is clearly hard for them to keep costs under control. As usual, I look forward to the next quarter to see if management solve the riddle of making a profit or whether they will continue to keep me drinking, stick with the Non-GAAP fairytales and pretend the ship is in calm waters, rather than admitting it is heading for an iceberg.

Saturday, November 14, 2015

Post MVP Summit Roundup



Another Summit comes to an end and I am back in Australia waiting until next November. In the photo, I am in green mid-way up on the left in front of CRM greats Chris Cognetta and Gustaf Westerlund.

What is the MVP Summit?

The MVP Summit is one of the bigger perks for being a Microsoft MVP. Every year, Microsoft hosts a conference exclusively for all of the MVPs across all products. This year about 2,000 MVPs came to Redmond to meet their respective product teams and learn about what is coming up with their product.

Meeting the CRM product team is great but, just as delightful, is meeting my fellow MVPs from around the world. About 50 of the world’s CRM MVPs made it to Summit this year and I am yet to meet an MVP I did not like. Their passion and willingness to share their knowledge is incredible.

The days were spent in presentations with the Microsoft CRM product team.


but there was also time for a bit of socialising after hours.


Lots was learned both on the Redmond campus and off campus.

What Did You Learn?

Unfortunately, much of what is discussed at MVP Summit is under a Non-Disclosure Agreement (NDA). If I reveal too much I get kicked out of the MVP program. To be honest, the roadmaps and directions of the product are subject to change and refinement so there is little value in publicising a feature only for it to slip another six to twelve months.

What Can You Tell Us?

The good news is some of our time with the product team was spent reviewing the changes coming in Dynamics CRM 2016, as described in the Preview Guide, which I went through a few weeks ago. With it being tricky to get demos of the complementary products to Dynamics CRM such as Microsoft Dynamics Marketing, seeing them in the flesh gave me a much better appreciation for their capabilities so I thought I would go through some of my takeaways for what is coming towards the end of this year. To play it safe, I will focus on the products reviewed in the Preview Guide (although Field One looks really good!).


Parature got as much coverage as it did in the Review Guide i.e. it was not shown at all. Read into this what you will.

Microsoft Dynamics Marketing (MDM)

We got to see first-hand the SMS Marketing capabilities coming in the next version. You can send SMS messaging out, with custom fields and the recipients can reply for actions such as unsubscribing. Imagine doing a mass email in CRM and the experience is similar. If SMS marketing is important to your business and you are already using MDM, this will be a welcome addition. Unfortunately it will only be available in limited markets initially which does not include Australia. Given SMS marketing is not that big over here, I can wait.

CRM App for Outlook

This is not to be confused with the Outlook Client for Dynamics CRM. The CRM App for Outlook is an add-on to which allows tracking of emails, creating contacts, and the opening of CRM records directly from the interface. This is huge for a bunch of reasons but one reason is it gives people with Apples the ability to track, which was not previously possible. Seeing it in action really brought home how useful this will be.

OneDrive for Business

OneDrive will now be a document store for Dynamics CRM. Its behaviour will be slightly different to SharePoint in regards to security and visibility. While SharePoint security is managed by SharePoint with documents being visible to all who can access the store, regardless of CRM access, OneDrive security its with the CRM user.

So, if a user adds a document to a OneDrive store in CRM, unless they share it, only they can see it in CRM. If another user goes to the same CRM record, the document will not be visible until the author shares it.

Initially I thought this was a bad thing but, on reflection, it simply provides options when implementing the system. For example, let us say CRM is being used for storing counselling sessions. While the fact that a meeting was scheduled may be public, the visibility of the notes of the meeting can now be controlled by the counsellor.

Interactive Service Hub

We got to see these first hand and, for service centres, this is a big leap forward. While managing cases is handled very well in Dynamics CRM, doing it in volume is hard. The new service hub seeks to address this by providing tools for high volume call centers. There is also a new form type, specifically for the service hub to help with efficient case processing. My preference would have been to bring the benefits of the new form type to the normal form layout but perhaps this will happen in time.

Mobile Offline Support

We saw this in action and it works in a similar way to the Outlook client’s offline capability with the playback graph. I am very happy for this one and to see it uses mechanisms already familiar to the product team gives me confidence it will do the job.

Next Generation Search

This was another feature demonstrated which, in effect, takes the columns of results generated by the universal search tool and compiles them into one list making good guesses as to the most relevant record. I like the idea of it and, if the universal search is activated for many entities it should make finding records much easier.


I was excited when I did my original summary of the Review Guide but seeing the functions in the flesh at Summit has made me realise how much effort Microsoft is putting into the product and how efficient they are at achieving results. We really are seeing the kinds of innovation in 12 months we used to see in three or four years in the past. I now have two things to look forward to; the release of CRM 2016 and next year’s MVP Summit in November.

Saturday, October 31, 2015

Questions for Summit

A short blog today. In 12 hours I get on a plane and fly halfway around the world to one of my favourite cities in the world, Seattle. The weather is lousy but the people are great. Think Portlandia with more tech.

The reason for my crazy-long trip is the annual pilgrimage to Microsoft’s Mecca, Redmond to meet up with my fellow CRM MVPeeps and the CRM Product Team at the MVP Summit. Every year I get a few days to hang out with some of the best and brightest and talk CRM amongst other topics, especially after a few Mac and Jacks.

The reason for the blog is not to wax lyrical about the trip but to send out a call to arms. If you have questions or demands for the MVP Product Team, put them in the comments and I’ll pass them on. If the answer is not in violation of my NDA, I’ll even tell you what they said in response Winking smile

See you in Seattle, my MVP comrades Winking smile

Sunday, October 25, 2015

Four Acquisitions To Make Dynamics CRM Awesome

Saluting One of the Greats

I am sad to report that the Dynamics CRM MVPs will soon lose one of their most passionate voices.

Shan McArthur, the CEO/CTO of Adxstudio Inc. is about to lose his MVP status. This is a shame because the MVP community will be lesser for it. MVPs are sometimes accused of being the Polyannas of our product; the public relations arm of Microsoft, but Shan proves this is not the case.

In the time I have known him, (he has been an MVP as long as I have) Shan fought passionately to turn Dynamics CRM from being a great product to being the best it can be. Shan is unflinching in giving feedback to Microsoft on how to improve their product, sometimes in the MVP channels, sometimes face to face.

Has Microsoft cut him off for not being “Disney” enough? Have they silenced the “No” in a room full of “Yes’”? Not at all, they have acquired his company and put him on the payroll. The MVPs’ loss is Microsoft’s gain. He can no longer be an MVP because you cannot be an MVP and work for Microsoft.

This decision also speaks to the character of Microsoft and shows a maturity not always seen at the big end of town. It makes me proud to be associated with them.

Fortunately, Shan lives near Redmond so there will be opportunity for those of us left behind to catch up with him when we converge on Microsoft for the annual MVP Summit.

What Adxstudio Brings to the CRM Table

Adxstudio brings a couple of things to CRM. Firstly, Shan has created a range of great tools for organisations developing for CRM. Microsoft now owns these and, presumably, they will continue to innovate the tools and make them available to the CRM development community. Secondly, Microsoft obtains Adxstudio Portals. Adxstudio Portals turns Dynamics CRM into a content management system with its own web portal.

For the online version of Adxstudio Portals, this web site is hosted by Adxstudio themselves (presumably on Azure). For on-premise, you have the web site on your IIS. Moreover, Adxstudio Portals comes with templates and add-ons for different website functionalities, like a web forum, which get added into CRM as solution files. Too easy.

Adxstudio fills the gap of a portal for CRM, a common business requirement.

This had me thinking which other third party add-ons would take Dynamics CRM from great to unassailable, if Microsoft acquired them.


There is often confusion over this one because of Microsoft Dynamics Marketing (MDM). Does CRM need ClickDimensions if we have MDM, albeit at a cost? The fact is while both augment Dynamics CRM’s marketing capabilities, their value propositions are very different. MDM is a comprehensive marketing management tool, the result of the acquisition of MarketingPilot. It covers everything from media buying to document collaboration. In principle, your marketing department could run MDM even if you do not use Dynamics CRM.

On the other hand, ClickDimensions is built on Dynamics CRM and strengthens a number of weak areas in the product, providing many marketing features required by businesses using a CRM system such as:

  • Mass communications and monitoring tools
  • Surveys
  • Lead scoring

Because ClickDimensions is ‘all in’ with Dynamics CRM, all of the data it generates is held within CRM entities and, just as CRM gains functionality from ClickDimensions, ClickDimensions gains functionality from Dynamics CRM, such as the analytics capabilities and workflow engine.

While the competitors have isolated, loosely-connected ‘clouds’ of sales and marketing functionality, by acquiring ClickDimensions, Microsoft gains a single product with comprehensive capability across both sales and marketing.

Resco/CWR Mobility

It is true that Dynamics CRM has mobile functionality, in terms of cross browser/device compatibility and apps in the various stores to access CRM, but the functionality has its limitations and there are products with pedigree in the market which take CRM to the next level.

Two common mobile add-ons to Dynamics CRM are Resco and CWR Mobility. Both have similar functionality, although the preview videos suggest you need to develop CWR Mobility to meet some of the standard functionality of Resco.

So what do these products offer which the mobile solutions of CRM do not have? Really, there are three key features which make them integral to the mobile experience:

  • Linking the device’s GPS to CRM to show things like Accounts and Contacts nearby
  • Linking to the device’s camera to capture images in the field and easily bring them into CRM
  • Full offline capability (in the case of Resco)

While the mobile clients from Microsoft seek to replicate the web client experience on the mobile device, these two third party tools go beyond this to integrate CRM with the features of the device.

If Microsoft bought one of these two, their mobile client really would go from good to great.


The ‘R’ in CRM stands for Relationship but CRM systems are more adept at capturing records than the relationships between them. So where do you go when Connections does not cut it? Datahug and Introhive seek to fill the gap using readily available corporate data sources, such as the company Exchange server.

By seeing who is emailing whom and how often, not only can we improve the quality of the data we hold in our CRM system but also gain insight on how strong the relationship is and who may be able to provide an introduction. We can generate new leads, find other contacts at the companies we are targeting and see if the communication is two-way or decidedly going in one direction.

These products provide huge value using information usually going to waste and, if built into CRM, would be a clear differentiator from the competition where big data is generated but insights are going wanting.

Experlogix CPQ/Cincom CPQ

Out of the box, CRM is good at managing the sales of physical widgets but not so good outside of this. For service selling and recurring revenue sales, CRM is a little weak. Also, for scenarios involving products with complex setup e.g. purchasing the parts to make a bicycle, it is very hard to put the dependency rules in. This last scenario is solved with  a Configure-Price-Quote (CPQ) solution, such as those produced by Experlogix and Cincom.

By acquiring add-ons which go beyond simple product sales, again, CRM will gain a significant edge on its competitors.


Every software product has areas for improvement and Dynamics CRM is no exception and there is always a balance to be struck between building innovation and acquiring it. It is great news that Microsoft has acquired Adxstudios because of the power Adxstudio Portals brings to the Dynamics CRM platform. Perhaps some of these other products will, in the near future, also join Microsoft’s ranks taking Dynamics CRM to a new level.