Removing duplicate rows from a SQL server DB

I've been working on data imports lately and have run into this issue several times. If you data isn't clean when you import from a spreadsheet or csv or wherever, you can end up with duplicate rows in your tables. This isn't a really big deal if the rows have differing keys, but if ALL the data is identical, including the keys, then how do you delete the extra rows?

It turns out that the answer is pretty simple. Write your query to delete the rows but before you run it, set the rowcount to 1, this will prevent SQL server from performing your query on more than a single row. Don't forget to set rowcount back to 0 afterwards, or all your subsequent operations will only return 1 row which, at least for me, would be really frustrating :)

Here's a nice explanation of the process from Microsoft: http://support.microsoft.com/kb/139444.

Adding Labels/Arbitrary Text to MSCRM 2011 Forms

So, you say you want to add your own text to a MSCRM form huh? Well, Microsoft doesn't make it immediately obvious how to perform such a seemingly innocuous task. I was able to find a blog post here which did the trick: http://blogs.infinite-x.net/2011/05/20/adding-form-labels-using-html-web-resources/

Thanks to "Chris C" clarifying how to re-style the WebResource so that it takes up only the amount of space that you want it to take up instead of what MSCRM does by default.

I've taken the liberty of reproducing his comment below in case the blog post eventually goes "poof".

Chris C says:

April 26, 2012 at 6:34 pm

Hmm, looks like the HTML messed up in my last post! I'll remove any formatting which is going to mess it up this time :)

The reason you need to specify 2 rows is that the CSS isn't removing all the margins around the text and as such it's taking up more space than it appears.

I can successfully use 1 row with the following CSS changes:


body style="border: 0px 0px 0px 0px;
margin:0px 0px 0px 0px;
padding: 0px 0px 0px 0px;
font-family: Segoe UI, Tahoma, Arial;
font-size: 11px;
font-weight: bold;
background-color: #f6f8fa;"

Hope that helps!

MSCRM 2011 Problem Pricing Order Product Items

So, you've created your products, your price lists, your price list items and they're all nicely in the system. Now you go to add some products to your order and BAM! You get a pricing error. What gives?

We have a situation where we need unpriced or free items in our system. The gotcha isn't that you can't do it, it is HOW you have to do it. It seems that the only way CRM will be happy calculating a $0 price is if your pricelistitem uses the "Currency Amount" pricing method and an "Amount" of $0.

My experience has been that if you use any of the other methods with various values for Percentage and your product's referenced cost (List, Current Cost, Standard Cost) = $0 that an error will be displayed when the system attempts to calculate the Order Total.

Lesson Learned:
If you want to have an order-product to show up with a $0.00 price, set the order-product's pricelistitem "PricingMethod" to "Currency Amount" and the "Amount" to $0.00

Importing Data to MS CRM 2011 Online

After much struggling and gnashing of teeth trying to use MSCRM 2011's new feature that allows importing from .zip files, I discovered that zip files don't seem to support re-importing (updating). I was attempting to update the "Decimals Suported" field on all of our products (over 1000) and kept hitting a brick wall with the mapping. CRM wasn't recognizing the file as an import, it was trying to create the records and couldn't map the checksum fields. On a whim, I attempted to import the uncompressed file and viola, it worked. Silly me for trying to use the zip file to make the upload faster.

Lessons learned:
Use .zip files to create entities in CRM. This allows faster uploads for large files and the ability to associate records during the import process.

DON'T use .zip files to update existing products in the product catalog. (I have yet to determine if .zips will work to update other entities.) Use the uncompressed, modified file that you exported from CRM.

Sharepoint 2010 Foundation - Create nested folders from workflows

Problem: Create a nested folder structure in response to an event without writing code in Sharepoint 2010 Foundation.

Why: We have a need for this functionality on a hosted Sharepoint site where we don't have the ability to write custom code other than what can be done in Sharepoint Designer.

Research Approach:

1 - Look through Sharepoint Doc

Nothing useful found

2 - Google it!

Very little useful found

3 - Beat head against wall until a solution manifests

YEAH!

Solution: Interrelated Workflows.

The user would like to track a set of "projects." A project is a collection of status' as well as documents in a nested folder structure. I decided to create several sharepoint artifacts to represent the structure.

1 - "Project Details" list which holds the Project and associated fields/columns.

2 - "Projects" Document Library which holds Project Folders, Project Sub Folders and Project Document

3 - "Project Folder"

This is a Folder object with additional columns and holds objects associated with the project

4 - "Project Sub Folder"

This is derived from Project Folder and is used to nest structure in a Project Folder

5 - "Project Document"

This is a Document object with extra, project specific columns

There are also several list workflows:

1 - Update Project Manager Name - Project Details List

Copies a lookup field's contents to a text field so it can be carried along with a site column

2 - Create Project Folder - Project Details List

Creates a Project Folder in the Projects Document Library when an item is created in the Project Details list.

3 - Update Project Folder Status - Projects List (Document Library)

Updates the status of the Project Folder so that the Create Project Sub Folders workflow knows to run

4 - Create Project Sub Folders - Projects List (Document Library)

Creates the Sub Folders for a new projects.

There were several problems that needed to be solved. The main one is how do I create objects in a new, arbitrary list?

My first approach was to attempt to create a workflow that fired when an item was added to the Project Details list that created the items in another list. This worked fine to create the folder in the Projects list, but I couldn't go any farther because creating objects in an arbitrary list isn't straight-forward.

I created another workflow that ran when a Project Folder was created to create a folder in the "current" list which worked to create a sibling of the new Project Folder, but not a child. BUT, after a lot of experimentation, I determined that I could create a sibling of the current item but code the name as follows: [%CurrentItem%]/NewFolderName

This worked to create a child of the current folder! After I got this working, I was able to run the workflow manually and life was good. However, as soon as I changed the workflow to run automatically at the creation of a Project Folder, Sharepoint Designer would no longer let me save the workflow. It complained about errors in the create-list-item operations. I was able to get around it by setting the workflow to fire on changing a Project Folder rather than creating it. Surprisingly, that worked.

Now my problem was: how to fire the workflow at object creation. I decided to create a status field on the Project Folder and have a workflow that fires at Project Folder create time to change the status so that the Project Sub Folder creation workflow would run. So, I created a "SubFolders" column in the Project Folder. When the first workflow creates the Project Folder, it sets this value to Waiting. The workflow that runs at Project Folder create time changes this value to Creating which triggers the Create Project Sub Folders workflow. This workflow creates the Sub Folders and then changes the SubFolders column to Created.

That's it!

I'll give some more details on the objects, workflows, and interactions soon.

OpenVPN, Snow Leopard, and 64-bit

Since my MAC adventure began early this year, I discovered through a friend (thanks Jon) a tool called TunnelBlick which is a nice little GUI that sits on top of OpenVPN which is what one of my clients uses as their VPN. Anyhow, Snow Leopard's 64-bit kernel doesn't play nicely with TunnelBlick unless you also download the latest Tun/Tap driver which is available here:

http://tuntaposx.sourceforge.net/download.xhtml

I downloaded and installed it and TunnelBlick is back running like a champ.

This also brings up a point that by default Snow Leopard does NOT start using the 64-bit kernel extensions. You can force it by holding down the 6 and 4 keys during boot, or download this cute little tool which lets you select your startup mode and forget it. You can still override it by holding down the 3 and 2 keys or 6 and 4 keys during boot.

http://www.ahatfullofsky.comuv.com/English/Programs/SMS/SMS.html

Have fun all.

MacBook Pro upgrades

I purchased a 15" 2.53 GHz Unibody MacBook Pro with a 320GB drive and 4GB of RAM back in January of 2009 and recently upgraded the drive to a 7200RPM 500GB Seagate Momentus. The process was much more disruptive than I had expected it would be and took me about 2 days. My next few posts will document the adventure.