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 codeplex.com

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...

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

SELECT    *

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:

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.

Dramatization:
"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..."
Google
Research
Attempt
Bang Head On Desk
Repeat

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.

Enjoy...