Thursday, July 21, 2011

Override Created on or Created by during Import

We've all had that need to import historical data, while maintaining Created On date. In 2011 - this has gotten exponentially more simple, and even intuitive!

The Goal:
Import historical data with historical time stamps and creating user information in tact.

The Hangup:
In previous versions the only supported way of doing this was via the Data Migration Manager. This tool requires an additional install, and is often quirky and user-unfriendly.

The Workaround:
I'm happy to report that this one doesn't need a workaround, since the Microsoft Dynamics CRM development team has decided to provide an easy way to handle this requirement using security privileges and the Import Wizard!

Simply update the appropriate Security Role to allow users to Override Created on or Created by for records during data import, include the Created On or Created By columns in your data and import.

Very Handy!

Monday, May 9, 2011

Mail Merge Quote - Grouping Products by Type

As mentioned in a previous post, the Sales Proposal/Quote format requested by my users requires that products be separated into multiple tables, grouped by Product Type. Mail Merge is not the friendliest beast - so this one was a bit of a challenge. The solution this this involves a combination of new categorization fields in the Quote Product form, client-side javascript, and some mail merge tricks. 

The Goal:
Use a Mail Merge template to generate a proposal or quote in CRM, where products contained in the quote are grouped into a predefined (or custom - just to make it even for fun!) set of product type tables.

The Hangup:
In Word Mail Merge, special field codes are required to include information before and after the main set of data being merged - and I did not know of a way to repeat the merge several times within the document while filtering said data to only list those of the current table's product type. I was sure there must be a way... so I took to my good old friend Google.

The Workaround:
As it turns out, the solution is not to repeat the merge with filters; but instead use dynamically defined bookmarks to hold the current product's type for comparison to the next. For each merged item, if the product type changes a new table is created for the new product type. This of course, requires that the data set be sorted by product type so only one table for each type is created. 

Step One: CRM Form Customization
The merge data set is a directory of Quote Products associated with the Quote record in CRM. However, I want to group these products by Product Type, which is an attribute of the Product lookup defined in the Quote Product record. So, I needed to add new attributes to the Quote Product entity that will define their grouping inside the quote document, as well as the preferred sort of the groupings. Remember that although there is a predefined set of preferred product groupings, my users also wanted the ability to provide their own group names on a per quote basis for added flexibility. 
To the Quote Product entity, I added:
  • Product Category (new_productcategory): a picklist including the predefined product group names and an 'Other' entry
  • Group Number (new_groupnumber): an int for the sort ID
  • Category (new_productsubject): a varchar for a write-in group name. 
I added these fields to the Quote Product form. We will use client-side scripting to populate these fields.

Step Two: Client-Side Scripting
I needed to populate my new fields automatically, based on the product associated with the Quote Product record (and it's Product Type). The Group Number is to be assigned based on the Product Category used, and Group Number and Category are disabled by default. If the user changes the Product Category to 'Other', then the Category write-in field is enabled and required. For simplicity within the mail merge template, I wanted the write-in Category field to always contain the Category or group name even when chosen in the picklist so I could avoid an extra IF clause in my merge field codes, so my code prefills the Category varchar attribute with the value chosen in the Product Category picklist.
Quote Product OnLoad
1:  function OnCrmPageLoad() {  
3:    crmForm.all.productid.attachEvent("onchange", OnProductChange);  
4:    crmForm.all.new_productcategory.attachEvent("onchange", OnCategoryChange);  
5:  }  
7:  function OnProductChange() {  
9:    // get current chosen product  
10:    var product = crmForm.all.productid;  
11:    if (product.DataValue != null) {  
12:      // if lookup form was used, get Product Type from lookup columns  
13:      var ptype = product.items[0].keyValues.producttypecode;  
14:      if (ptype != undefined) {  
15:        //Assign category based on Product Type  
16:        switch (ptype.value) {  
17:          case "Controllers & Accessories":  
18:            category = 1;  
19:            break;  
20:          case "Middleware":  
21:            category = 2;  
22:            break;  
23:          case "Enterprise Software":  
24:            category = 2;  
25:            break;  
26:          case "Custom Graphics":  
27:            category = 3;  
28:            break;  
29:          case "Engineering Services":  
30:            category = 4;  
31:            break;  
32:          case "Hardware":  
33:            category = 1;  
34:            break;  
35:          case "Warranty & Services":  
36:            category = 4;  
37:            break;  
38:        }  
39:        crmForm.all.new_productcategory.DataValue = category;  
40:      }  
41:      OnCategoryChange();  
42:    }  
43:  }  
45:  function OnCategoryChange() {  
47:    // Initialize category and group number text fields  
48:    var catPL = crmForm.all.new_productcategory;  
49:    var group = crmForm.all.new_groupnumber;  
50:    var catWriteIn = crmForm.all.new_productsubject;  
52:    // Set group number based on selected category  
53:    var catPLvalue = parseFloat(catPL.DataValue);  
54:    if (catPLvalue < 5) {  
55:      group.DataValue = catPLvalue;  
56:      catWriteIn.Disabled = true;  
57:      group.Disabled = true;  
58:      crmForm.SetFieldReqLevel(, 0);  
59:      crmForm.SetFieldReqLevel(, 0);  
60:    } else {  
61:      // if category is Other, enable write ins and make required  
62:      group.DataValue = null;  
63:      catWriteIn.Disabled = false;  
64:      group.Disabled = false;  
65:      crmForm.SetFieldReqLevel(, 1);  
66:      crmForm.SetFieldReqLevel(, 1);  
67:    }  
68:    // Set category text field based on selected category  
69:    switch (catPL.DataValue) {  
70:      case "1":  
71:        catWriteIn.DataValue = "Hardware";  
72:        break;  
73:      case "2":  
74:        catWriteIn.DataValue = "Software";  
75:        break;  
76:      case "3":  
77:        catWriteIn.DataValue = "Graphics";  
78:        break;  
79:      case "4":  
80:        catWriteIn.DataValue = "Services";  
81:        break;  
82:      case "5":  
83:        catWriteIn.DataValue = null;  
84:        break;  
85:    }  
86:  }  
88:  OnCrmPageLoad();  
Quote Product OnSave
Because we are working with fields that are disabled (read-only) on the form, we must force CRM to save their values.
1:    crmForm.all.new_productsubject.ForceSubmit = true;  
2:    crmForm.all.new_groupnumber.ForceSubmit = true;  
Step Three: Mail Merge Template Design
In simple form, the field codes for the mail merge template look as follows:
{ IF { MREGESEQ } = "1" "
{ MERGEFIELD Quote_Product_Category }

" }{ IF {MERGEFIELD Quote_Product_Quantity } > 0 "{ SET Place1 {MERGEFIELD Quote_Product_Category } }{ IF { Place2 } <> { Place1 } "
Subtotal: $

{ MERGEFIELD Quote_Product_Category }

({ MERGEFIELD Quote_Product_Quantity }) { MERGEFIELD Product_ID }: { MERGEFIELD Quote_Product_Amount }
" "
({ MERGEFIELD Quote_Product_Quantity }) { MERGEFIELD Product_ID }: { MERGEFIELD Quote_Product_Amount }
" }{ SET Place2 { MERGEFIELD Quote_Product_Category } }{IF { MERGEFIELD LastItem } = 1 "
Subtotal: $

Product Total: ${ MERGEFIELD Total_Detail_Amount }

" }
I've colored the sections for easier reading and understanding. Consider a quote with two groups of products: Hardware, and Software. In the above code, the blue fields indicate data displayed for the first group, and the green for and after the second group. If the data was sorted prior to merge so that all hardware products were first, and then all software products after, the output would look like the following:

(1) PART_NUM_X: $300.00
(3) PART_NUM_Y: $2,000.00
Subtotal: $


(2) PART_NUM_Z: $500.00
(1) PART_NUM_A: $1,000.00
Subtotal: $

Product Total: $3,800.00
This is obviously a very crude example, and can be made much more professional and filled out by adding tables, formatting and additional text in between the field codes appropriately. See for more information.

Step Four: Sorting Records During Mail Merge
Finally, when a mail merge is run based on this template, the data needs to be sorted to make sure the product groups are listed together, with the groups in the desired order. This is where the custom Group Number field comes in. When the mail merge is initiated, the user is shown a list of all data to be merged. The user needs to click the Sort link - sorting first by Last Item (to make sure that the last item is always truly last) and then by Group Number.


There is still one hangup that remains with this solution, so it's not as elegant as I had hoped - but is workable. In CRM, the item tagged with the Last Item flag is always the last Quote Product record added to the Quote. I have not found a way of changing this to date. This means, that in order for your sort to work properly, users must make sure that the last product item they add to their quote be of the last sorted product group. So in the above example it would need to be a software product. If this is not done, say the last product added to the quote were a hardware product, you would end up with an additional Hardware list at the end of your quote containing that one last product (not ideal!).

In addition, you may have noticed that the users will need to fill in subtotal numbers prior to delivering the Quote, since there are no subtotal records for these groups to pull from CRM. With the proper Quote Reports made available to the user for reference (I built one that breaks the products out in the same way with subtotals calculated) this one is only minor.

Monday, December 27, 2010

Ode to the Orbit One CRM Updater

Today, I've found yet another reason to love my most recent favorite tool: Microsoft Dynamics CRM 4.0 Bulk Update and Export Tool from Orbit One. Simply put: Bulk Status Updates.

The Goal:
Certainly a Dynamics CRM 4.0 admin could not function without some sort of Bulk Import / Update tool under their belt. For a long time, mine was the MSCRM Import Tool put out by Microsoft and endorsed on their Microsoft Dynamics CRM Blog. With lack of ability to save login credentials and numerous other limitations or problems that I ran into, I eventually went looking for another solution. A few months ago I found the Orbit One Bulk Update and Export Tool and fell in love.

[edit 1/04/10] In UR 9, the update capabilities found in the beta version of CRM 4.0 Import Wizard were finally released making a separate tool less necessary. However, many of us still prefer these separate tools for comfort, ease of use, and extra features.
Solved Hangups:
  • Bulk Edit - not the only tool that can do this, but can be accomplished in the tool's user interface, or by CSV export/import. Even the CRM Import Wizard can update now, but can be finicky.
  • Bulk Delete - people generally have to write their own add-in to take advantage of the bulk delete API available in Dynamics CRM 4.0. Orbit One has been nice enough to include the functionality in this great tool. This is essential for me a few times a year when the price lists get updated - allowing me to delete all price list items and rebuild them after updating the list price on the products themselves.
  • Bulk Activate/Deactivate - I have not found another way of doing this short of, again, writing my own add-in. With this tool, it's as simple as exporting the records of interest, creating a simple ID | statecode | status code CSV file, and clicking Change CRM State. When 17,000 records need to be deactivated (as I needed to do today), doing so 250 records at a time is unbearable. With this tool, it's a no-brainer!
Other Nice Features:
A few other reasons I like this tool over others available...
  • Saved connection & credential information
  • Color coded rows indicating errors / warnings
  • Status information during import / update in the footer bar 
  • Image courtesy of
For me, deployment of this tool was a snap. Although the installation instructions that come with the tool say that manual installation and registration of files is necessary, I run it successfully with no such setup required (directly on the DB Server). Others may not be so lucky, but this was a big plus for me!

Happy Updating...

Wednesday, December 8, 2010

Mapping Custom Oppty, Quote and Order Product Attributes

Recently, I had the need to assign a custom category attribute to Quote Products in order to work around some Mail Merge Quote formatting road blocks (I'll discuss this in a later post). I was setting this attribute using Javascript on product selection when a Quote Product was being added to a Quote. This worked fantastically until...the first time I created a Quote from an Opportunity with products already populated.

:: Bang Head! Should have seen that one coming! ::

Ok, so - no problem - just put the script in the Opportunity Product form too, so that no matter the stage of the process (Opportunity or Quote), the category is set when the item is created - right? Sure, and then just map that attribute from Opportunity Product to Quote Product, and voilá! Mission Accomplished, right?

Unfortunately it was not that easy...

The Goal:
A custom attribute in Opportunity Product, Quote Product, and Order Product Entities that is maintained throughout the life cycle of Opportunity to Quote to Order.

The Hangup:
In Dynamics CRM 4.0, there is no intrinsic way to customize the mappings from Opportunity Product to Quote Product, or from Quote Product to Order Product. The relationship appears not to exist.

The Workaround:
So, off I went to research. Surely I'm not the first person who's had the need to map custom attributes from Opportunity Product to Quote Product. Eventually I found someone doing this from Quote Product to Order Product, and it was suprisingly simple!

As it turns out, a form exists for editing such mappings - there is just no implicit way of accessing it.

The base URL for this form is:

http://[CRMServerAddress]/Tools/SystemCustomization/Relationships/Map pings/mappingList.aspx?mappingId=[GUID]

So, if we can get the GUID of the "mappingList" of interest, we can in theory access those mappings and edit them through this form. For this, we'll go directly to the database. In your favorite SQL client (I use SQL Server Management Studio), run the following queries to get the information you're after:

Opportunity Product to Quote Product
SELECT     *
FROM        entitymapbase
WHERE      targetentityname = 'quotedetail'
Copy the GUID from the opportunityproduct return item

Quote Product to Order Product

FROM       entitymapbase
WHERE     targetentityname = 'salesorderdetail'
Copy the GUID from the quotedetail return item

With the necessary GUID now at your fingertips, simply complete the URL and paste it into IE, and you'll be rewarded with a mapping configuration form:

Monday, December 6, 2010

The Obligatory Opening Post

Over the last several years, while on the quest of becoming a Dynamics CRM Guru (still not there yet...), I've hit lots of roadblocks.

"Oh wow, it'd be REALLY great if the system could do this for us..."
"Yeah! Can't you make it happen Camille?"
"Oh...umm...well, pretty sure that's not generally an option.
Let me look into it..."
Bang Head On Desk

You get the idea.

Well, on occasion I end up with a pretty great workaround that I'm quite proud of. And, since my users generally don't want to hear all the *cool* details about HOW it works behind the scenes - I thought I'd put a boastful I'm Awesome! out there in the form of this blog. I mean... hopefully this will save someone else some time out there.