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.

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.