Using Excel to generate picklist XML for MSCRM

In a recent post, Mitch Milam asked me to provide an Excel based solution to generating the customization XML for MSCRM picklist values. So, here it is: it uses a small VBA macro to loop through a named range of cells to generate the XML (see figure).

The range name is entered into A2 ("CountryList" in the example) and this defines the set of picklist item values. The option offset number to start with is entered into B2 ("10" in the example), it becomes the value of the first generated option. Use one as the 'Start option number' when adding items to an empty picklist.

I have added a button control to run the GenerateCustomizationXml method that generates the customization XML:

Sub GenerateCustomizationXml()

Dim namedRange As range
Dim outputCell As range
Dim listName As String
Dim xml As String
Dim ctr As Integer

listName = range("A2").Text
ctr = range("B2").Text

Set namedRange = range(listName)

xml = "<options nextvalue=""" & ctr + namedRange.Cells.count & """>"

For Each listItem In namedRange.Cells

xml = xml & "<option value=""" & ctr & """><labels><label description=""" & listItem.Text & """ languagecode=""1033"" /></labels></option>"

ctr = ctr + 1

Next

xml = xml & "</options>"

Set outputCell = range("B4")
outputCell.Value = xml

End Sub

The macro generates the XML with the correct option value numbers, calculates the nextvalue number, and puts the result into B4. Mark the B4 cell and click CTRL-C to copy it to the clipboard. Follow the steps outlined in Mitch's blog to import the new picklist values into MSCRM. Refer to the SDK for more details.

The macro is generic and can be used for any named range of cells in the worksheet. Just enter the range name in A2 and run the macro. Naming a range of cells is as easy as selecting the range of cells and typing in the name in the 'name box' in the upper left corner of the worksheet.

MSCRM 3 country field: apply a standard picklist

In a previous post, I referred to the DHTML solution by Michael Höhne that uses JavaScript to convert the country field from a text box to a picklist. The drawback of that solution is that the list of countries is provided by an inline JavaScript array, and is thus not very easily maintained.

I prefer that a MSCRM super user can maintain the content of the country picklist like any other picklist in the system. This is, actually, easy to achive by modifying the script a little bit: instead of using an array, just copy it from a hidden custom picklist field that contains all countries. In addition, I will show how to make it work with 'quick create'.

Start by adding a new field (attribute) of type 'picklist' to e.g. the account entity. Give the new field the name 'adminCountry' and add the list of countries. Save and close the added field.

Then open the account form and add the new 'adminCountry' field to the 'Address' section. Make the field read-only and hide the label.

Then click on 'Form Properties', and select 'OnLoad' in the 'Event list' and click 'Edit'. Turn on 'Event is enabled' and use this JavaScript to copy the values from the 'new_adminCountry' picklist:

//************************************************************
//Original author: Michael Höhne
//source: http://www.stunnware.com/crm2/topic.aspx?id=JS1
//************************************************************
//The lookup field to change. You can use this code for any field you like.
var fieldName = "address1_country";

//I'm saving the current field value to set it as the default in the created combobox.
var defaultValue = crmForm.all.item(fieldName).DataValue;

//This is the TD element containing the text box control. We will replace the entire innerHTML to replace
//the input type="text" element with a select element.
//KJELLSJ: replace the INPUT itself
//var table = crmForm.all.item(fieldName + "_d");
var input = crmForm.all.item(fieldName);

//This is the beginning of our new combobox. It's a standard HTML declaration and all we need to do is to
//fill the appropriate options. You should check the original HTML code to get the appropriate values for
//req (field required level) and the tab index.
var select = "<select req='0' id='" + fieldName + "' name='" + fieldName + "' defaultSelected='' class='selectBox' tabindex='1170'>";
//KJELLSJ: build options separately
var options = "";

//KJELLSJ: hide the 'new_adminCountry' picklist
var picklist = crmForm.all.item("new_adminCountry");
picklist.style.display = "none";
//KJELLSJ: inject countries from hidden 'new_adminCountry' picklist
options = picklist.innerHTML;
options = options.replace(/selected/i, ""); //remove selection
options = options.replace(/value=\d+>/g, ">"); //remove numeric values
options = options.replace(/>([\w ]+)</g, "value='$1'>$1<"); //use name as value
var defaultValueFound = false;


//Here's the part that ensures that an existing entity will always display the stored value of the
//country field, no matter if it is included in the option list or not. If it is set and it was not found
//in the previous loop, then defaultValueFound will still be false and we have to add it as a separate
//option, which is also SELECTED.
if ((defaultValue != null) && (defaultValue.length > 0) && !defaultValueFound) {
//KJELLSJ: add selected country as first option
options = "<option value='" + defaultValue + "' SELECTED>" + defaultValue + "</option>" + options;
}

//Close the open select element.
//KJELLSJ: concatenate the select and options
select = select + options + "</select>";

//Finally, I replace the entire definition of the text box with the newly constructed combobox. IE is very
//smart and will instantly update the window. You now have a combobox with a list of all available countries
//in the world and it will be saved directly to the address1_country field.
//KJELLSJ: replace the INPUT itself
//table.innerHTML = select;
input.outerHTML = select;

I have changed the script to use .outerHTML on the <INPUT> element as the 'quick create' form mode does not have a named <TD> element. Note also the use of regex to transform the standard list of options into a picklist that will store the name of the country instead of the picklist value number.

Save the modified account form and publish the customizations (Actions-Publish). Test the script by opening an existing account and by creating a new account. Also remember to test that the customization works correctly in 'quick create' form mode for the entity. Use Fiddler to see the source of a 'quick create' web-dialog.

I have made a simplification to the 'selected country' logic by just adding the existing country as the first item in the picklist. I prefer that the current value is the top value in a picklist, the same way I prefer that the most used values are at the top of the list.

It is rather simple to extend the script to find the correct country in the options string: use .indexOf() to find the correct option element, then use .replace() to inject the "SELECTED" text into the options string.

This solution combines the best of the DHTML approach with the ease of the replacement-country-picklist approach, while avoiding the need for an OnSave/OnLoad script to keep the standard country field in sync with the selected item in the picklist.

Manually entering all countries in the world to the picklist is not fun, but this tool at Mitch Milam's blog should make things simpler. Alternatively, it should be rather trivial to use Excel to generate the XML from a range of cells. Most customers provides the set of picklists as Excel worksheets, afterall.

BBoM: Golden Hammer, Hammer-Nail Metaphor

The Golden Hammer and "When you have a big enough hammer, everything starts to look like a nail" are two metaphors that are related to the Big Ball of Mud pattern. A central part of BBoM is that developers (coders) stick to the tools and technologies that they know, independent of the actual problem at hand.

This kind of thinking is more common than you might believe. A while ago, I did some work assisting on an integration project that exported data from a RDBMS to an FTP server. As this was a typical ETL process, I recommended using DTS on one of the client's existing SQL Server 2000 servers. DTS is perfect for fetching data, transforming and writing it to files, and then transferring them using FTP.

One of the developers, however, was not familiar with DTS; but he knew how to write Windows services in VB6. And so he implemented his part of the ETL as a service - running on his PC (on his own, doing his "thing"). In addition, there is no 'deployment & configuration' documentation (also very typical for BBoM solutions). The 'truck factor' for his solution is one, and now he is gone/unavailable for six months; and there is a huge note on his PC: "DO NOT LOG OFF OR TURN OFF THIS COMPUTER. PRODUCTION CODE IS RUNNING HERE". Say no more . . .

Another of my favorite phrases is "Assumption is the mother of all fuck-ups" (from the Steven Seagal movie Dark Territory). I apply it anytime a programmer coder tells me that something need not be tested/checked/verified.