Wednesday, December 31, 2008

Perfect Storm of a CRM Upgrade

This is a post to the forums I did a while ago (edited a little). Hope it helps.

I've just completed what, I believe, amounts to the perfect storm of an upgrade. The client was moving from SBS 2003 (not R2) on a virtual machine to a physical Windows 2003 64 bit server. They were also upgrading CRM3 SBS to CRM4 Professional. As SBS was running SQL 2000 and CRM3 does not run on 64 bit machines the database couldn't be easily moved from one to the other. So we have an upgrade, migration across domains and a move to 64 bit.

The eventual path to success was the following:

1) Upgrade SQL 2000 to 2005
2) Upgrade CRM3 to CRM4 on the SBS virtual machine
3) Backup the database and transfer it to the new 64 bit server
4) Install CRM4 on the 64 bit server
5) Use deployment manager to attach the old database

Points of note:

* You can upgrade SBS SQL 2000 to 2005 but you may need to completely reinstall SRS as in my case the discs simply would not allow for SRS to be upgraded
* If you get "Malformed XML Found in Microsoft Dynamics CRM Saved Queries" this could be an issue with the saved queries table but it could also be a permissions issue. In my case the user who had installed CRM was not the domain administrator. Upgrading as the domain admin did not cut it. Promoting the original user to a domain admin and upgrading did the trick
* If you get "Action Microsoft.Crm.Setup.Server.InstallConfigDatabaseAction failed. Invalid user name. Failed to validate username for given domain. Only existing domain usernames and email addresses are allow" then try unplugging the network once the required components are installed. This cleared the error for me
* On a 64 bit box, make sure you are running IIS in 64 bit mode and therefore ASP.Net is registered in 64 bit mode. If not, CRM will get past the pre-checks, start installing and then bomb out leaving a mess which cannot be repaired, uninstalled or installed over
* Similarly do not try to install the 32 bit version of CRM on a 64 bit server. It will only lead to pain.
* The deployment manager can be used to add an organisation with the Professional license. It simply swaps out the default database for the new one and maps the users as usual (although, I again hit an issue with the setup user being someone other than domain admin. The error was "At least the setup user needs to be mapped before this organization can be imported". I had to swap map links to make it work) Good luck if you come across a similar situation ;)

How to quickly switch users when demoing Dynamics CRM

This is a little trick I got shown about six months ago which is just damn handy.

In some demo scenarios you want to switch to a different user to demonstrate the differences between users. In Dynamics CRM this is commonly used to show how entity access can be limited or how different roles have different rights assigned to them.

The key to this trick is that the user that runs IE, is the user CRM authenticates to for the web client. So if you run IE as 'Bob', when you go to CRM's web address, you will log in as Bob, even if you have logged onto the domain as someone else.

To set up a shortcut for IE which you can quickly run as a different user, create an IE shortcut (right-click, save to desktop) and then right-click the shortcut and go to properties.

Clicking the Advanced button allows you to specify 'Run with different credentials'. Ticking this box means when you double-click the shortcut, you'll be prompted to enter in alternative credentials. enter them in and now when you go to the CRM URL, you will be logged in as that person.

When hosting is a good option for CRM

Hosting business applications, that is, running business software on someone else's servers and accessing the system, typically through a web browser is gaining more and more attention as the technology becomes more user-friendly.

Outside of the cost considerations (do I want to pay a monthly fee or a large amount up front and hopefully less in maintenance in the future) there are other key factors in deciding which path to go down.

Here are a few other considerations before making the leap.

In-house technical ability
If you are running your own servers and have business-critical applications on these servers, you need staff dedicated to ensuring their function is aligned to the business' requirements and that if users have problems with the application, these staff can assist them. One of the key reasons I see CRM implementations fail is a CRM system is implemented and then falls into disrepair as no one within the company is tasked with championing its use or continually improving it.

Business attitude to information
Some businesses are fearful of housing their data outside of the four walls of their business. There is no wrong or right answer to this and it is simply something the business must consider.

Free CRMs

These are a couple of online CRM systems which you can play with for free. While they do contact management as well as any other system, often the areas where these free systems fall behind is in the ability to configure and in automated workflow.

While SugarCRM is open source, I do not consider it free in that there are still costs of installation, hardware and setup. Online CRM's have some of these costs but they are a fraction of an in-house solution's.


This relatively unknown CRM system allows for unlimited users. Its quite rigid in its layout and function but covers the basics.



Zoho have online apps for practically every office need. Zoho CRM is free for the first three users and covers the essentials, including advanced features such as workflow. Think of Zoho as Google Apps and Salesforce combined and at no cost.

Importing tricks for Dynamics CRM 3.0 and 4.0

The import tools came a long way between version three and four but they are far from perfect. Here are a few tricks to make the process of importing using the import wizard as painless as possible.

The main tip I can offer for version 3.0 is getting the GUID of records already in CRM. For details see my other post:


This is most commonly used for adding the parent account to contacts. The process is import your accounts, get the sheet of GUIDs and then use Excel's vlookup to insert the appropriate GUID into the Parent customer column of the contact import sheet.

For version 4.0, you no longer need the GUID as the import wizard will match off of the account name. This (rather naively) assumes the account names in the system are unique but the gain is it greatly simplifies the process of attaching contacts to their parent account. Either ensure account name uniqueness, try using the GUID or be prepared to manually attach those records which will fail.

Despite being a great improvement over its version 3.0 counterpart, the version 4.0 import wizard has a few shortfalls. The first one is you are almost certainly going to fail if you create your own data maps.

While the import wizard allows to define how the data from your source sheet gets mapped into CRM, if you choose to actually do this you are very likely to get errors on your import. The problem is manual data maps do not support mapping values to other tables without a lot of messing about. So, for example, if you are bringing contacts into CRM and you have account names in the parent customer column, this will fail with a manually created data map as the map needs to go to the account table. Even if you use the version 3.0 method of specifying the GUID it will fail.

Update: Apparently Rollup 2 has fixed this but if you haven't gone to Rollup 2 yet, the below gets around the issue.

The only way around this is to get CRM to automatically map the values for you. The only way you can get CRM to automatically create the map is to ensure the column headings in your source file match what CRM is expecting to see (and yes, capitalisation does matter). Generally speaking the values need to match the labels on the form. More accurately, the values need to be the attribute names. You'll know if it is working because when you go through the Import Wizard, the moment that you specify the entity you're trying to import, the data map value will show 'Automatic'.

If it fails and you're stumped which column value is confusing CRM, go to Data Mapping as if you're setting up a manual data map and you'll see the values it's expecting.

Another tip at this point is if your import file is sufficiently large, you will not be able to import sample data into the data mapping entity. The only way around this is to copy the first few lines of the source file and use this for your sample data.

Once you have the correct column headings and CRM is automatically mapping the import you're almost set. The only thing you need to check is that the source data is 'clean'. There are two things which commonly upset imports. The first is the last column in your source file needs to be completely populated. The order of the columns is not important to shuffle them around to make sure the last is completely populated otherwise you risk CRM not understanding the how many columns you have.

The second issue, and often most frustrating, is you cannot have line feeds or carriage returns in your source data. These are guaranteed to confuse CRM in regards to the number of columns. Finding which values actually have the offending characters can be tricky. The only tricks I know of are to run the cursor down the suspect column in Excel and if the value box at the top expands to more than one line, you have an offender. The other trick is to open the source file in notepad or some other text editor and look for rows which start in the 'middle' of a source row. Common columns including carriage returns and line feeds are address columns and note/description columns.

Once you've identified the offending column, Excel has a formula for stripping out the bad characters called SUBSTITUTE which allows you to replace one character with another in a string. Using the formula =SUBSTITUTE(SUBSTITUTE(ORIGINAL_CELL,char(10),""),char(13),"") will strip out the hidden characters and let the import go through.

Other issues can cause problems with import, such as a value in the source file not matching the value in a picklist attribute which you're trying to import the value into but the error messages for these are generally quite self explanatory. For version 4, to see the error messages, go to Settings-Data Management-Imports-Double click on the import job of interest and look at Failures. Alternatively, you may also get insight from Settings-System Jobs where the import job will also appear. Filter on Type: Imports if there are too many entries to navigate.

One time when the error logs will be of no use to you is with importing subjects and business units. The one thing to be absolutely sure of is that any subjects or business units you import are ultimately linked to an entry in the import file or to an entry in CRM. You CANNOT import the subject or business unit at the top of the tree. If you try, it and any entries linked to it simply will not appear in CRM. They are in the tables but never surface in the client.

As an aside tip, always populate a dummy field with an import-specific value. This way if you need to delete or modify the import in some way, the records are easy to identify in an Advanced Find. This is often used if you need to assign users to the records. Certain properties such as status and owner cannot be directly imported, so setting a value and changing the records within CRM is the only way to go if you're using the import wizard (the Data Migration Manager handles this better).

Another tip if you want to bring in records with non-English characters is to use the txt-unicode format for your import file. CSV simply won't work. The wizard works exactly the same way and the data should come in without a hitch. I've done this with Japanese, Chinese and Korean records and never had a problem.

Also, if you plan to import someone's contacts from Outlook, don't. Or at least be prepared for the consequences. Other than the data often containing hidden characters, CRM will not match the new record to its original in Outlook. Therefore, if the user is using the Outlook client and contacts are being synced, they will suddenly get a duplicate of every contact in Outlook as CRM syncs down the new records to their Outlook contacts. You can delete the originals by viewing your Outlook contacts in a list format and using the different icons for synced and non-synced contacts to work out which is which.

Finally, in a few cases, as you can only import into one entity at a time, you'll hit a 'chicken and egg' problem. The most common example is with accounts and contacts. Before you can link one entity to the other, one of them must be imported into CRM first. The upshot is you can either link the account to its primary contact (by importing the contacts in first) or you can link the contact to its parent account (by importing the accounts first) but you can't do both. Apparently you can get around this with the Data Migration Manager though.


Tuesday, December 30, 2008

So How Do Products and Price Lists Work in Dynamics CRM?

So this is a high level review of how products and price lists work in Dynamics CRM.

Products can be added to Opportunities, Orders, Quotes and Invoices but products can be sold to different customers for different amounts such as the case of retail customers and wholesale customers.

The deal with this, Dynamics CRM has the concept of price lists. Before you can add a product, you need to specify the currency and price list.

Price lists are made up of price list items. Price list items can be thought of as the combination of a product, a unit of measure and a price. For example, if I am selling soft drink, I can sell it wholesale or retail and I can also sell it by the can, six-pack or pallet. For each combination, we would assign a price and the individual combination make up a price list item.

To complicate matters further, each price list can only be assigned one currency. So if we sell our products in two countries and we want a wholesale and retail price list for each we need to create four price lists.

Just when we think we have a handle on all this we can also assign a currency to a product to signify the currency we buy it in. This, on the surface, sounds quite powerful. We can buy our cans of drink in, say, US dollars and sell them in Australian dollars. his is true but if you expect CRM to calculate a sell price based on a markup of the cost price you can only do this if the product and price list currencies match. CRM is not smart enough to take a US dollar buy price, convert it to Australian dollars using the exchange rate in the system and then apply a margin.

So let's put all this together. I sell cans of soft drink. I buy them in US dollars and sell them in Australian and New Zealand and, for those of you that didn't realise, both countries have their own currency. First of all I add my products to CRM. If I want to calculate my sell price based on some sort of margin of the cost price, I'm out of luck. If I was selling in one currency, I could convert the buy prices to this currency and then import the products with this converted cost price but as I'm selling in two currencies I will have no choice but to manually specify the sell price.

I also need to specify my units of measure. In this case we will say cans, six-packs and pallets.

I now set up my price lists. I'm selling in two currencies and I need a wholesale and retail list for both countries so this is four price lists: Australian wholesale, Australian retail, New Zealand wholesale and New Zealand retail. I assign them the appropriate currencies and then I add price list items. Essentially the number of price list items for a given price list will be the number of products you're selling multiplied by the units which you sell it in.

Let's say I sell four different soft drinks (cola, lemonade, orange and lime) using my three units of measure. Therefore each price list will have 12 price list items. Can of lime, six-pack of lime, pallet of lime etc. For each of these combinations, I'll assign a price in the currency of the price list.

So I've now set up my four price lists. Each one has its 12 price list items and I want to add a product to an opportunity. I create my opportunity and assign it a currency. The currency of the opportunity MUST match the currency of the price list. If this is the case, I can then add a product and the unit of measure and CRM will do the rest in terms of the price charged.

Incidentally, a lot of the heavy lifting for setting up products, price lists and price list items can be done via the import wizard.

Monday, December 29, 2008

How do I get the GUID in CRM?

So this is a quick and dirty trick for getting the GUIDs for a set of records in Dynamics CRM v3.

1) Create a view of the records you want the GUIDs for
2) Export as a Dynamic list
3) Right click on A2 and select 'Edit Query...'
4) A bunch of boxes will pop up. Close them as soon as they open
5) You will now see in your workbook an extra column has magically appeared on the end. These are your GUIDs

In v4 things have been made much simpler. Simply export as a dynamic worksheet and unhide the hidden column at the end which is your GUIDs.

Dynamic CRM's Address Book is broken

I discovered this about 6 months ago and I'm yet to see a fix for it. The theory goes that if you're running the Outlook client and you're writing an email, you can click, say, the 'To...' button, go to the CRM Contacts address book and add contacts directly to your email.

It almost works. If in the CRM options, on the address book tab you opt to only sync the contacts that you own to the address book, you will see a big fat nothing. A few of the other address books populate such as the CRM Users but nothing in the Contacts address book.

The only way to make it work is to sync down ALL contacts in CRM. This can be problematic if your CRM DB has millions of Contacts but, for the moment, this is the only option you have.