Takin' care of business... with Dynamics 365 (CRM)!: 2009

Tuesday, December 15, 2009

A short list of Dynamics CRM gurus

Wednesday, November 18, 2009

Formatting output from CrmDiagTool4

I'm sure you know about the utility that will interrogate a CRM server and export CRM configuration details such as server details, Registry keys, CRM website details, TCP/IP, Active Directory settings, etc. The utility goes by the name "CrmDiagTool4".

To make the output of the utility more readable, I put together this short VBA script that you can run in Word 2003 or 2007.

First, run CrmDiagTool4.exe and export the system report. Then copy and paste the resulting report text into Word. You'll probably want to set the page orientation to Landscape, the margins to .25-inch, and the font for all text to Courier New 8pt.

Next, paste this script into the VBA editor and run it. It simply changes the formatting of the major report sections (lines beginning and ending with several dashes) to the Heading 1 format.

Sub FixDiagToolOutput()
   Dim headingText As String
   Dim range As range
   For Each para In ActiveDocument.Paragraphs
      If Mid(para.range.Text, 1, 10) = "----------" Then
         headingText = Replace(para.range.Text, "-", "")
         Set range = para.range
         range.Text = headingText
         range.Style = ActiveDocument.Styles("Heading 1")
      End If
   Next para
   MsgBox "Done"
End Sub

Cheers,

-Tim

Tuesday, October 27, 2009

CRM documentation tool: Fixing scripts

In a previous post I mentioned that the CRM 4.0 Documentation tool can export scripts from Dynamics CRM forms and attributes. One of the problems with the tool, though, is that it copies the JScript code verbatim from the exported CRM customizations xml file into Excel, complete with escaped greater-than and less-than characters. For example, a less-than character will appear in the script as < instead of "<".

To fix the script code once it's in Excel, search and replace the following:

Replace < with <
Replace > with >

-Tim

Wednesday, August 26, 2009

NullReferenceException: Microsoft.Crm.ObjectModel.OrganizationUIService.LabelLoaderPublished.LoadMetadataLabel

WARNING: Back up your CRM database if you decide to follow the steps below!

If you see the following error in the CRM trace/log file (as I did today on a VPC… for no apparent reason):

Stack Trace Info: [NullReferenceException: Object reference not set to an instance of an object.]
at Microsoft.Crm.ObjectModel.OrganizationUIService.LabelLoaderPublished.LoadMetadataLabel(Int32 entityType, String attributeName, ExecutionContext context)

The problem might be that there’s an old attribute being referenced in the entity’s form. To determine this and remove the bad form from the form's xml definition, open the OrganizationUIBase table and look for the object type id for the problem entity (e.g., 10016). Copy the FormXml to notepad and save the file with an xml extension. Open the file in IE or other xml viewer/editor and look for an attribute that no longer exists. In notepad or in your xml editor, remove the problematic node, copy the new xml, and paste it into the FormXml in the OrganizationUIBase table. Restart IIS and then see if you can now export, publish, or do other work with the entity.

Of course, any direct change to the CRM database is not supported by Microsoft and should only be done as a last resort. Since I was working on a non-production VPC I went ahead and made the change... and it did the trick. Again, I have no idea how the problem occurred. The problem entity was fine the last time I loaded the VPC and today I couldn't publish, remove attributes, or export. Strange, very strange.

-Tim

Wednesday, August 12, 2009

Bingdown

I coined a new term today: Bingdown

Definition: A depressed state one reaches when Bing doesn't deliver what HAS to be out there.

Today's Bingdown: azure "dynamics crm" asp.net authentication token site:microsoft.com -faq

Another Bingdown (well, a partial Bingdown): "how to lose weight while programming"

Sunday, August 9, 2009

Enhancing the Dynamics CRM 4.0 Documentation Generator

Like me, Phil Edry (also with Altriva Solutions) is constantly on the lookout for Dynamics CRM development tools, blog posts, etc. that help us to become more productive for our clients. And we return the favor to the community by blogging, sharing code, and participating on forums.

Today, I want to share some code. But first you need to know about the gem of a CRM utility that Phil discovered last week on CodePlex: Microsoft Dynamics CRM 4.0 Documentation Generator.

After the 2 minute install of this tool and 2 minute export of your CRM application's customizations (XML), you can produce (in Excel) documentation that provides a list of all attributes on each form, the script of all objects on the forms, picklist values, and more. This can save hours in documentation time for anyone responsible for the configuration or maintenance of a Dynamics CRM 4.0 system.

One of the benefits of having this customization information in Excel is that it opens up the possibility to write VBA code to further analyze the information. That's what I've done with the code shown below.

This VBA code loops through each entity (listed on the Form worksheets) and for each attribute it determines whether the attribute is referenced in any of the JScript code on the form or any of the attribute onChange events. When the attribute is identified in a code line, it places the code line number(s) on the attribute's detail row. For many Dynamics CRM installations, this could save additional hours of work.

First, run the Documentation Generator tool. Then, open up the Excel VBA code editor, double-click the ThisWorkbook object, paste this code and click Run. Within 10 seconds you'll have additional details in your Excel entity/attribute report.

Sub IdentifyFieldsReferencedInScript()
Dim columnOrdinalForScriptLineNumbers As Integer
Dim formWorksheetName As String
Dim formWorksheetRowNumber As Integer
Dim formWorksheetAttribName As String
Dim scriptWorksheetName As String
Dim scriptWorksheet As Worksheet
Dim scriptWorksheetRowNumber As Integer
Dim scriptWorksheetBlankLineCounter As Integer
Dim scriptLineNumbersForAttribute As String
Dim scriptText As String
Dim currentEntityName As String

Application.ScreenUpdating = False

columnOrdinalForScriptLineNumbers = 6

'* Loop through each worksheet. Process worksheets that end with "-Form".
For Each xlSheet In ActiveWorkbook.Worksheets
If Right(xlSheet.Name, 5) = "-Form" Then

formWorksheetName = xlSheet.Name
currentEntityName = Left(formWorksheetName, (Len(formWorksheetName) - 5))
scriptWorksheetName = currentEntityName & "-Script"

'* Check for existence of a Script worksheet for the current entity.
On Error Resume Next
Err.Clear
Set scriptWorksheet = ActiveWorkbook.Worksheets(scriptWorksheetName)
If Err.Number <> 0 Then
MsgBox "At least one script worksheet is missing. Execution halted."
Exit For
End If

formWorksheetRowNumber = 5
scriptLineNumbersForAttribute = Empty

'* Loop through each attribute on the current form
Do
formWorksheetAttribName = xlSheet.Cells(formWorksheetRowNumber, 1)
If formWorksheetAttribName <> "Field" And Left(formWorksheetAttribName, 7) <> "Section" And formWorksheetAttribName <> Empty Then
scriptWorksheetRowNumber = 1
scriptLineNumbersForAttribute = Empty
scriptWorksheetBlankLineCounter = 0

Do
scriptText = scriptWorksheet.Cells(scriptWorksheetRowNumber, 6)
If InStr(1, scriptText, formWorksheetAttribName) Then
If scriptLineNumbersForAttribute = Empty Then
scriptLineNumbersForAttribute = CStr(scriptWorksheetRowNumber)
Else
scriptLineNumbersForAttribute = scriptLineNumbersForAttribute & "" & CStr(scriptWorksheetRowNumber)
End If
Else
If scriptText = Empty Then
scriptWorksheetBlankLineCounter = scriptWorksheetBlankLineCounter + 1
Else
scriptWorksheetBlankLineCounter = 0
End If
End If

scriptWorksheetRowNumber = scriptWorksheetRowNumber + 1
Loop While scriptWorksheetBlankLineCounter < formworksheetrownumber =" formWorksheetRowNumber"> Empty

End If
Next xlSheet

Application.ScreenUpdating = True

MsgBox "Process complete."

End Sub

There's lots more that can be built on top of the documentation generator utility. For example, VBA can be used to build a table of contents for easier access to the worksheets. Also, for a given attribute, you could run dynamic SQL queries to determine, for example, how often the attribute is populated (% utilization) and how often picklist options are selected. Or, through CRM API calls, it would be useful to know the names of attributes that are not on the entity's form -- this would help to provide a total picture of each entity.

If I build more functionality on top of the documentation tool's output I'll be sure to post the details here. Meanwhile, let me know if you have other ideas for how to make this tool's output more useful. It's great as it is, but there's definitely potential to make it even better.

Cheers,

-Tim

Wednesday, July 29, 2009

Custom calendar in CRM UI

While working on a project for a client at Altriva, a need emerged for a custom event calendar within the CRM UI. Using a pre-built calendar along with some custom code to store events (maintained in CRM) into an XML file, I was able to get this functionality in place pretty quickly.

Please read more about the options for implementing a custom calendar in Dynamics CRM 4.0 in the Altriva Blog.

Tuesday, March 17, 2009

MSCRMKeyGenerator - Expired key

Yesterday I mentioned that I received a working backup of customer's CRM 4.0 system in a VPC. This morning, when attempting to load the CRM Web UI I was confronted with an error, which led to this information in the Application Event Log:

Source: MSCRMKeyGenerator
Event ID: 18949
Error (partial): Current active key (KeyType : CrmWRPCTokenKey) is expired. This can indicate that a key is not being regenerated properly.

The most common fix for this problem is to restart the Microsoft CRM Asynchronous Processing Service but the service wouldn't start for me. Phil Edry, my esteemed colleague here at Altriva, said "try Rollup 2... that might fix it".

I owe him a beer... it worked.

Exactly why the "active key" expired and what Rollup 2 did to fix it still remains a mystery to me, but I have too much work to do to investigate it now. If you have some more details on this, though, please let us all know. Thanks, -TD

Monday, March 16, 2009

Plugin Registration Tool FileNotFoundException

If you run into this error when attempting to register a plug-in...

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'PluginRegistration, Version=2.2.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified. at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

... you might read elsewhere that you need to copy microsoft.crm.sdk.dll and microsoft.crm.sdktypeproxy.dll to the same folder as the Plugin Registration Tool's executable. Although that might work for you, it didn't work for me today. I'm working on a VPC with a customer's instance of CRM 4.0 restored onto it and attempting to register a plug-in led to the unhandled exception. (I tried version 2.1 and 2.2 of the registration tool.)

The "fix" was to load the PluginRegistrationTool.sln solution (Visual Studio 2005) and recompile. The application then allowed me to register the plug-in. It apparently found itself!? :-)

-Tim

Monday, January 12, 2009

Infragistics WebGrid and CRM 4.0

A common request I hear from our clients is for inline editing of data within grid views in CRM. Many of them would love to see this functionality in Dynamics CRM 5.0. Occasionally, though, one of our clients has a strong enough need for this capability (e.g., for rapid data entry, Excel-like functionality, etc.) and asks us to provide it.

On a recent project, I worked with a company to replace an Excel-based data collection process (where Excel files are distributed, collected, and consolidated) with a web-based application. The goal was to provide the same general capabilities provided by Excel (ease of data entry, grid-based interface, calculated fields, drop-down picklists, data validation, etc.) but provide it over the web and connect the application with the company's Dynamics CRM 4.0 system. And, of course, the page couldn't post back to the server after entering data in a cell so the use of Ajax was mandatory.

Our client had already invested in ASP.NET components from Infragistics for other projects so I downloaded the components and took a close look at the capabilities, particularly the Ajax, grid, and inline editing features. Fortunately, I became convinced that the components would meet the needs of the project very well so I proceeded with the application's design, estimates, and prototypes.

(Infragistics offers a 30-day trial of their components, so you can give them a test drive before forking over the thousand bucks.)

Downloading and installing the ASP.NET controls was simple. Being new to Infragistics components, though, I was initially tripped-up by their use of three names for their two grid products. Here's the deal: the "UltraWebGrid" and the "WebGrid" are the same product. This isn't at all obvious when first looking at the documentation. The "DataGrid" is the latest grid component, but we didn't use it due to its lack of support for hierarchical data (e.g., ADO.NET DataSet with multiple related DataTables).

To get started with the WebGrid, simple drag it onto the web form from the NetAdvantage 8.3 Web toolbox section in Visual Studio (we used Visual Studio 2005).

The easy way to get data to appear in the grid is to bind the grid to a data source. For example, you can use Visual Studio to create a DataSet that uses the ADO.NET SqlDataAdapter to query one or more CRM Filtered Views. The WebGrid provides a style editor that you can use to set inline editing rules, column widths, fonts, alternating row coloring, etc.

Connecting the WebGrid to a SQL query is fine for display purposes, but you can't (or shouldn't) write data back to CRM directly to the CRM database tables. So you won't get the "quick-and-dirty" benefit of binding the WebGrid to CRM because the grid won't be able to dynamically update the database when inline changes are made.

Although I had to write a lot of code to get data into the grid and write updates back to CRM, it was surprising easy to get all the parts working. The WebGrid provides a rich object model that allows you to configure and access all parts of the grid object, from the height, width, and overall style of the grid down to what should happen after someone types data into a cell.

Here's a screenshot of the application. I had to keep it small to protect the data (I'll try to upload a bigger data-wiped version later) but I mainly wanted to show the overall appearance of the grid and provide an example of the inline popup WebCombo control.

Here's a quick rundown of the architecture for this application:

Upon application startup, I gather the default filter criteria from the filter options (e.g., date range, owner, etc.). The Infragistics date picker control came in handy for date filtering.

I then pass the filter conditions to a method that dynamically builds the SQL WHERE clause for the main grid data. The data is stored in related DataTable objects. The WebGrid supports hierarchical datasets, so once you've populated the DataSet you can bind it to the grid and it takes care of rending parent/child rows.

Once I have the data, I call a custom method to configure the grid columns. This involves setting the type of control for each column. Essentially, you can tell the grid whether a cell should provide a text field (with optional masking/formatting), a dropdown box, a WebCombo control, date picker, image, calculated value, etc.

With the data in the DataSet and all columns configured, I then bind the data to the WebGrid. That gets the data on the screen, but that's only the beginning. The next part of the project involved writing a lot of JavaScript to handle the various actions that can happen in the grid. For example, when the user clicks into a cell, I sometimes want an Infragistics WebCombo to appear to allow the user to select from a popup grid.

Saving data from the WebGrid back to CRM involves 1) Creating a Save button (turn-off the auto-postback functionality), 2) When Save is clicked, construct a DynamicEntity (it helps to have some wrapper/helper code for this part to build the SOAP) and set the name of the target attribute and its value, and 3) Use XmlHttpRequest to call the CRM Web Service with either a Create, Update, or Delete command in the SOAP XML.

One of the biggest challenges I faced on the project was using Ajax to dynamically filter the contents that appear in the Infragistics WebCombo objects used in the grid. For example, when the user selects an option on a parent row, I needed to filter the available options in the WebCombo for a cell that resides on a child row. The way I did this was 1) Create a JavaScript event handler for when the user clicks into a cell that needs a filtered combobox, 2) Formulate a CRM SDK query and use Ajax to get back a collection of CRM entitiese, 3) Clear the contents of the WebCombo that's linked to the clicked cell, and 4) Use the Infragistics WebCombo API to dynamically create option rows.

Infragistics provides an abundance of online samples, knowledge base articles, and sample applications to help you ramp-up on their controls.

All in all, the Infragistics ASP.NET controls provided us with a great collection of tools to provide an Excel-like interface for our client. They can now update CRM data from a web grid, which provides them with the rapid data-entry interface that they needed.

CRM 4.0 Development Tools

Here's a list of freeware development tools for Dynamics CRM 4.0 that you might find useful. Thanks to all of the authors and publishers of these apps!

CRM 4.0 Diagnostics Tool - Exports server and CRM details to file. It's a good idea to run this periodically and keep backups to help with system troubleshooting.
CRM 4.0 FetchXmlWizard (stunnware) - Helps build source code to call the CRM SDK. This was written for 3.0 but it still (mostly) works for 4.0.
CRM 4.0 Form (Entity & Attribute) Reporter - Builds a Word document from exported CRM customizations.
CRM 4.0 JavaScript Export Tool - Exports JavaScript (JScript) from forms. This is especially helpful for keeping track of code changes in a source control system.
CRM 4.0 Metadata Viewer (stunnware) - Browse CRM metadata including entities, attributes, data types, relationships, etc.
CRM 4.0 Trace Log Viewer (stunnware) - Helps with viewing CRM trace log files.

Please let me know if there's a tool you've found useful that's not on this list. Thanks!