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