The Selected Item Cannot Be Tracked in Microsoft Dynamics CRM

I had an interesting problem this week. For one project we are at the ‘pointy end’; go-live is upon us and training is underway. The client is loving the Outlook integration and so, while I normally train through the web client, in this case, it was done through the Outlook client.

Everything was going fine. As is often the case there had been some feedback from those in the room regarding their security privileges and what they would need to do in their role and these would need to be tweaked.

At the end of one session one of the users reported not being able to track appointments. Sure enough, they created the appointment in Outlook and when they right-clicked and Tracked, the error reported “The selected item cannot be tracked in Microsoft Dynamics CRM.Item Name=<Appointment Name>”. Usually when there is a security permissions error, the error shown calls this out but this was different.

What was also strange was if I opened the appointment it claimed it would be tracked once it was saved which, of course, it already was. Forcing a CRM sync tried to track the item but produced the usual ‘unable’ error.

Checking the user’s permissions everything checked out. They had User Delete and Create for Activities so they could untrack and had basic access to the Service Calendar entities to allow for the tracking of Appointments.

So I thought I would try to add an appointment and track with the trainer user account, which had System Administrator permissions. The result?

• If I opened a Contact in CRM and created an appointment, the Outlook form opened, as per user permissions, and everything worked fine
• If I created an appointment directly in Outlook and tried to track it through right-clicking on it in the calendar (the track button did not appear in the ribbon for it unless I opened the record) it produced the same error

This was a bit of a mystery and resorting to Google/Bing did not yield any joy either.

The Breakthrough

I remembered, back when I was playing with TwInbox that custom items in Outlook could not be tracked to CRM. I also noticed that rather than the usual ‘New Appointment’ button on the calendar in Outlook it read ‘New MeetingConference’, which looked plain wrong.

(what the button should look like)

A bit of internet searching and chats with the client’s IT folk revealed they had, in fact, used a conference scheduling add-on for Outlook in the past but this had been abandoned quite a while ago. It seemed its legacy was still within their standard operating environment (SOE).

Extensive playing about in Outlook to find the right setting eventually yielded gold. By right-clicking the calendar and selecting “IPM.Appointment” for the ‘When posting’ option, suddenly everything started working as expected.

All we needed to do was get the users to apply this setting in future training sessions, fix up those who had been in the class (about ten users) and adjust the SOE. Given the next training session was with the executives, I was glad we had discovered it sooner, rather than later.

Conclusions

Generally, if you cannot track an Outlook item into CRM it is going to be a permissions issue. Some things to check are

• the Append To privileges for Users, Contacts, Accounts and whatever you want to Set Regarding to
• Create rights for Activities
• Appropriate access to the role Service Management privileges so CRM can write the appointment to the Service Calendar (using the privileges of a standard role such as salesperson is a good place to start)
• User Activity delete permissions if you want the user to be able to untrack and remove from CRM

Failing this, it may be that Outlook is behaving in a non-standard way under the influence of an add-in. If so, see if you can create an Activity from a CRM record using the Outlook forms (this can be set in the user options on the first tab). If this works, given CRM spawns the correct form, it is likely the problem is Outlook has had its default forms messed with and this is where the problems lies.

Good luck and my hope is this post saves you a few hours of heartache troubleshooting Outlook-CRM tracking

Import Tricks For Dynamics CRM 2011

One of my most popular posts is from four years ago, Import Tricks For Dynamics 3.0 and 4.0. Having just done another multi-lingual import for a client, I thought this is a good time to update with my latest discoveries and tricks.

This blog is NOT a review of the features of the import tool as this has already been covered in multiple places elsewhere. This is a few tips and tricks when working with the import tool to make your life a little easier.

Templates and Lookups

A lot of work was done with the Import Wizard in 2011 and it is greatly improved. Back in version 4, there were two ways of getting data into CRM: the Import Wizard and the Data Migration Manager. The version 4 Import Wizard was not as powerful but much easier to use. The Data Migration Manager was a little unfriendly but potentially quite powerful (or so I was told, I always managed to get away with using the version 4 Import Wizard). In 2011, there is no longer a Data Migration Manager but the power of the Import Wizard has greatly improved.

You can now import practically any entity and CRM will provide you an Excel-compatible XML template to use which tells you handy things like field lengths and which fields are mandatory. You can also import lookup fields and link off any unique value you like. For details of both of these features check out this previous blog post of mine.

One thing that I will add to my previous post is that, while you can often click the Import Data down triangle and get a template, the behaviour is not always completely consistent.

Therefore, if you are having troubles accessing a template for a specific entity, you can always go to Settings – Data Management – Templates For Data Import and download any possible template from there.

Information That Cannot Be Imported

Unfortunately it is still not possible to import non-printable characters such as carriage returns and line feeds and these must be stripped out of things like the description text before importing. In my most recent import, the data came from an Oracle source and had SYN signal characters through it which caused no end of grief until they were purged. My old Import Tricks For Dynamics 3.0 and 4.0 post has an Excel formula for removing such characters, once you have identified the column, if this is causing you problems.

While you can now bring in the Owner of the records, you cannot bring in Inactive records (***STOP PRESS*** See Jukka’s comment below for how to work around this). While the template and mapping allow you to select ‘Inactive’ as an option for the Status Reason, the records will fail on import as the Import Wizard assumes all records have a Status of Active and the combination of an Active Status and an Inactive Status Reason is forbidden. My workaround was to create a new Status Reason of ‘Inactive_Temp’ for the Active Status. I also created a workflow which, when a record is created, checks the Status Reason and, if it is ‘Inactive_Temp’, deactivates the record with the Change Status step. This completely automated the importing of inactive records once I had adjusted the mapping to import records with an Inactive Status Reason to be ‘Inactive_Temp’ in CRM.

Another thing that cannot be imported are relationships across N:N links. There is, however, a codeplex project by L33t coder and fellow CRM MVP Andrii Butenko (a33ik) which promises to get around this. I am yet to try it but I do have this requirement on another project so it may come in handy very soon.

The Old Chicken and Egg Problem

One of the problems I mentioned in my previous post was that Accounts and Contacts have a 1:N relationship with each other through the Parent Customer lookup on the Contact and the Primary Contact lookup on the Account. This always caused problems as, with the version 4 Import Wizard, you could link one but not the other. I am now told you can link both (I did not need to do it on this project). The trick is to zip both the Contact source file and the Account source file together and point the Import Wizard to this zip file.

There is also a system mapping for ‘Generic Contact and Account Data’. While I had some difficulties using this (but in the end did not need to) the idea is if you have a big spreadsheet of Contacts, you can use this as a source file to create both the Accounts and the Contacts at once. While untested, I expect if this had a Primary Contact column in it, CRM would do the right thing linking Contacts to Accounts and also populating the Primary Contact field.

Unicode Data

As with version 4, you can still import unicode data, allowing you to populate fields with Chinese, Korean, Japanese or any other unicode text. The xml templates generated by CRM are unicode compliant BUT Excel is really bad at dealing with unicode files. If you plan to save your xml file as, say, CSV, Excel will make this a non-unicode compliant CSV file. The only option you can save the xml file to in Excel and preserve the unicode data is ‘Unicode Text’ which is a tab delimited text file (which the CRM Import Wizard is happy to deal with and was the source format for most of my imports).

One ‘feature’ of Excel 2010 which was very embarrassing was if you double-click a unicode-compliant CSV file generated elsewhere, Excel opens it but strips out the encapsulating double quotes on the first column. The upshot is if the text in the first column contains a comma, this shifts that row’s data out of sync. This was embarrassing in that I was accusing the client of not encapsulating the data properly when it was simply Excel screwing it up. What is more bizarre is that if you open Excel and click File-Open… to access the file (rather than double-clicking the file in the folder) it treats the unicode CSV file properly and does not strip out the encapsulating quotes.

Size Limits

While most of the news with the 2011 Import Wizard is good, one very frustrating feature of the new tool is the source file size limit. Your source files cannot be larger than eight megabytes. This limit is really frustrating, especially with unicode files. You have no choice but to split the files into sets of, say 10,000 records and queue them up for importing. In my case I was only importing in about 100,000 records so while a little annoying it was not a show-stopper.

Here are a few tips for getting your files down below the eight megabyte limit:

• The XML file, given it contains a lot of metadata about the source fields, is larger than a CSV or Unicode TXT file. Therefore, before importing, try saving your source file in a ‘simpler’ format
• Delete empty columns as this can save a few hundred kilobytes from the file

Error Export Bug

There is a great feature in the Import Wizard where you can export the rows which failed to import. CRM will package these rows up ready for re-importing once you have sorted out the issue.

However, the feature has a bug. In my case one row had caused an error due to a lookup failure. I exported and noticed all the data had shifted relative to the column headers. My culprit was the first column which was the salutation field and this particular record had an empty salutation. Therefore, my suggestion is to ensure whichever column you have first in your source file, make sure it is always populated e.g. the Last Name field for Contacts. Otherwise there is the risk that the data will be corrupted i.e. shifted if you Export Error Rows.

Similarly, I did see occasion where the Import Wizard got confused, when reviewing the import file, by the number of columns because the last column was not completely populated (something that also used to happen in version 4). So I would also recommend the last column in your source file be always populated. Status or Status Reason are good fields for this.

Slowness of Importing

Any tool used to import data into CRM, if it is using supported methods, needs to go through the web service layer. Unfortunately, the web service layer can only insert one record at a time causing a bit of a bottleneck for mass imports. Discussions on the web indicate the best you can hope for is about 50 records per second. If validations come into play or you have a slower server, this can go down to 10 records per second or lower. In my case, depending on server load, I was getting as low as 3-4 records per second in some cases.

I know no way of improving this in any significant way, other than using unsupported import methods i.e. direct SQL writes which I would not recommend given the potential for long-term disaster. It is simply something to be aware of and plan for.

Conclusions

Overall, the 2011 Import Wizard is a great tool and a huge improvement over its predecessor. However, like the previous version, it also has a few peccadillos which can cause headache if you are not aware of them. My hope is, with the above, you can get your data into CRM and avoid too many problems.

Salesforce Second Quarter Results for 2012

You have played the quarterly conference call drinking game now it is time to look at the numbers. Is growth still king or has Salesforce moved its gaze towards reining in its costs?

Where Do I Get My Numbers From?

As usual all number are brought to you from the Salesforce detailed financials PDF. If the numbers in this PDF seem at odds to what you heard on the conference call or in the multitude of lazy media updates, this is because the numbers Salesforce push are their non-GAAP numbers. These are numbers which Salesforce believe more accurately reflect the health of their business but are completely unregulated. The numbers I use are the GAAP (Generally Accepted Accounting Principles) which use conventions enforced across all companies to ensure meaningful comparisons can be made and to ensure a base set of conventions are used across the reporting.

Try and Explain This GAAP/Non-GAAP Thing To Me One More Time

The biggest controversy is when it comes to profit. The non-GAAP revenue Salesforce reports is similar to the GAAP revenue but Salesforce turns its GAAP loss into a healthy non-GAAP profit. How does this work? The fourth page of the PDF tells all. Here is the summary of the big differences:

• GAAP loss from operations turns into a non-GAAP profit
• GAAP net loss turns into a non-GAAP net income
• GAAP Diluted earnings per share goes from a negative to a non-GAAP positive

The big-ticket item that does this transformation is the ‘stock-based expenses’, at $85 million for the quarter.

What is Stock-Based Expense?

This comes from employee options. An option is a right to buy an share at a certain price at some date. In many companies, Salesforce included, options are given to employees to ‘align them’. The logic goes if an employee has the option to buy stock at a certain price, they are motivated to get the share price as high as possible on the date they can ‘exercise’ the option. The logic is reasonable but, as Warren Buffett points out, the difference between an employee option and a shareholder’s share is the employee spent nothing to get it; they have no skin in the game and nothing to lose.

From a GAAP perspective, working out what to do with options is easy. You value the options as if they have been ‘cashed in’ and treat them as an expense. Because the employee will give you money when they do exercise them, you also include the incoming cash in your cash statement. Other than the fact you have more shares in the pool and have therefore diluted the value of each individual share, everything, cash-wise, balances out.

The biggest difference in Salesforce’s GAAP/non-GAAP reporting is this ‘stock-based expense’. I understand the logic: “To see how sales is going, look at the customer transactions, not the employee transactions”. This is well and good but it does not remove the fact that to generate those customer transactions, options were thrown at employees and those options have a value, otherwise they would not have the power to ‘align’.

Salesforce are big at quoting revenue growth, non-GAAP profit and operating cash flow (see buzzword bingo for details). Revenue growth is real and cannot be denied. However, as mentioned in this article, the operating cash flow Salesforce quotes includes the value of the options value as a positive cash value but the non-GAAP profit does not include the expense. In other words, they recognise the money generated from the options in their reporting but not the expense. This is in no way illegal but can, understandably, cause confusion.

The questions to ask are simple: “If Salesforce reports non-GAAP to more accurately reflect the health of their business, why exclude option expense but include the related cash inflows?” and “If Salesforce are using non-GAAP for another purpose, what is it?”

But enough of my soap-boxing and onto the numbers.

Salesforce Made Another Loss (But Not As Big As It Was Last Quarter)

Salesforce halved their loss this quarter going from a loss of almost $20 million last quarter to $9.8 million this quarter but about double what it was for the same quarter last year. No loss is good so hopefully this is Salesforce turning a corner (as suggested by comparison with the previous quarter) rather than descending further into loss.

Salesforce is Starting to Control its Expenses (Sort Of)

This shows the percentage year on year growth of revenue (red) and costs (green). Before the start of 2010 (2011 Q1) revenue outpaced costs. Then costs started to blow out until their worst around the middle of 2011 (2012 Q2). This quarter gets us back to where we were two years ago with revenues growing faster than costs. Well done Salesforce. Let us hope this trend continues.

Margins, while still in the negatives, are also heading in the right direction, now at –1.34%. Another positive sign, although still negative.

Subscription Speculation

The good news is Microsoft have reported their CRM numbers, now having, as of July 2012, 2.7 million users and 36,000 companies using Dynamics CRM. This means the average company using Dynamics CRM has 75 users, about double my estimated average company size for Salesforce of around 35 users.

My prediction is Microsoft will break through the three million user mark some time around the end of this year (say November to January).

Again, using the Salesforce revenues as a guide, we can estimate the Salesforce subscription base at around 4.9 million users with just shy of 140,000 companies.

The subscription revenue continues to hover around two meaning Salesforce has around twice as many users as Dynamics CRM.

However, the actual difference in the subscriber numbers has flattened, although we have seen this flattening before, as per the chart below, just not for such a long period.

Conclusions

Salesforce is still on shaky financial ground. They are making a loss, but never talk about it, talking about non-GAAP profits and operational cashflows instead. However, costs are not growing as quickly as they were previously and, with revenues still growing there is the possibility Salesforce will return to profit in the future.

In terms of subscription, Microsoft still has about half the users of Salesforce but the difference is flat at around 2.2 million. It will be interesting to see if this difference reduces in the future.

My thoughts are it would be nice for Salesforce to be consistent in the messages they send out i.e. treat the stock-based expense consistently across their measures but that does not make for rosy numbers. However, even if their outward messages cause confusion there does seem to be an effort internally to control expense. Competition is good so the healthier Salesforce can make themselves financially the better for the consumer. As usual, I look forward to the next quarter to see if Salesforce can return to profitability.