Rollup 7 breaking workflow publishing?

I can't be certain, but I think Update Rollup 7 for CRM is breaking my ability to publish workflows that contain email steps. So far I have encountered this twice, for two different customers, and it is really annoying. Fortunately, I have found the solution!

The error that appears in the trace file (if you do a trace while attempting to publish the workflow) comes up something like this:

>Crm Exception: Message: Workflow compilation failed:
WF1399: Activity 'SendEmailStep5_policy' validation failed: Property "RuleSetReference" has an invalid value. Rule set is invalid. Rule "main" validation failed. Type System.Globalization.CultureInfo is not marked as authorized in the application configuration file.
WF1399: Activity 'SendEmailStep7_policy' validation failed: Property "RuleSetReference" has an invalid value. Rule set is invalid. Rule "main" validation failed. Type System.Globalization.CultureInfo is not marked as authorized in the application configuration file., ErrorCode: -2147201023

The error actually provides a clue as to the problem, but it wasn't entirely obvious to me. There is a line missing from the web.config file which, when added back, resolves the issue. The line is:

<authorizedtype assembly="mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" namespace="System.Globalization" typename="CultureInfo" authorized="True"></authorizedtype>

I've just been adding it to the end of the list of authorized types for mscorlib. You don't even have to restart IIS - it just begins working as soon as you save the file.

UPDATE: I had to put in the HTML escape characters for the brackets to show up in here - my apologies for the line not appearing before!

SQL functions to convert between bases

So I had an application where I wanted to convert between decimal and hex values, and while I found a few ways to do this with SQL, they didn't really give me what I wanted (the varbinary datatype is NOT what I was looking for). The largest issue was that the way the javascript on my CRM form converted to hex and the way SQL converted to hex were apparently not the same, and it was very annoying.

Thankfully, I found a couple of fantastic blog posts that help with this. Mr. Caldwell has created 2 functions for converting into and out of different bases, and they work wonderfully (and the same way that javascript does):
Convert Decimal to any other base
Convert any base to Decimal

Note that these are designed for SQL 2005 or above - I was able to apply one of the functions to SQL 2000 by changing the varchar(max) to varchar(255) (which was large enough for me.

Cool script to display associated records in an iFrame

I found a few of these scripts that applied to CRM 3.0, but for 4.0, this blog post has a simple and easy to configure script for displaying related records (like the contacts under an account) in an iFrame. You only need to change the values at the top and the script does the rest!

http://mscrm4ever.blogspot.com/2009/05/crm-40-show-associated-view-in-iframe.html

CRM Email Router and Forms Authentication

One of the single-most annoying issues when trying to get the CRM email router up and running is when you are trying to get the incoming connector to work and the OWA site in Exchange is set up to use Forms Authentication (which is nice for end users and doesn't work at all with the email router). The easiest solution I have found for Exchange 2007 is to simply create a second OWA site using Integrated Authentication and just use that for the email router, leaving the standard OWA site for end users and Forms Auth.

To this end, I will refer to a blog post here: http://blog.networkfoo.org/?p=195

This post has a list of handy PowerShell commands you can run to create the new site and all the information you need to configure it with the email router. This site has helped me a number of times, and I'm posting it here mostly as a bookmark for myself so I don't lose it again!

In case this post ever goes away, the meat of the article is the following 4 commands (create a new IIS website FIRST):

• New-OwaVirtualDirectory -OwaVersion:Exchange2007 -Name “owa” -WebSiteName “OWA-CRMRouter”
• New-OwaVirtualDirectory -OwaVersion:Exchange2003or2000 -Name “Exchange” -WebSiteName “OWA-CRMRouter” -VirtualDirectoryType:Mailboxes
• New-OwaVirtualDirectory -OwaVersion:Exchange2003or2000 -Name “Exadmin” -WebSiteName “OWA-CRMRouter” -VirtualDirectoryType:Exadmin
• New-OwaVirtualDirectory -OwaVersion:Exchange2003or2000 -Name “Exchweb” -WebSiteName “OWA-CRMRouter” -VirtualDirectoryType:Exchweb

There is also more information on this page:
http://technet.microsoft.com/en-us/library/bb124811.aspx

Mappings between product line items

So this is a problem I've had to look up every time I have it, so I'm posting it here as a way to keep track. In order to map attributes between product line items (opportunity product to quote product to order product to invoice product), you have to access the secret mapping URL to set it up. Steps to perform:

1. Find the GUID of the mapping you need to edit. In the Org_MSCRM database, run this query:
select * from entitymapbase where targetentityname = 'salesorderdetail'
Obviously, you might need to change the target entity value - the above will return all of the relationships where the order line item is the target. This returns a column that shows the "source entity name", so for a mapping from quote product to order product, pick the line where the source is "quotedetail". Copy the EntityMapId from this row.

2. Enter the following URL into IE (modified with your servername):

http://[servername]/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=[GUID]

Be sure to replace your server name for CRM, and put the GUID from step 1 at the end.

3. Create mappings and repeat as necessary!

Unable to publish workflows after update rollups

I just encountered an interesting problem today where I was unable to publish a workflow - the friendly error message said "An error occurred when the workflow was being created. Try saving the workflow again". I found a handy discussion here: http://social.microsoft.com/forums/en-US/crm/thread/78a7f940-50de-4f83-a38d-54425dd0ec1c/ that explains the solution. This seems to have started with Update Rollup 2 (and in this case I just applied UR 3 last week) and just requires adding an assembly line to the web.config in the section :



I didn't even need to reset IIS and the workflows started publishing again.

SharePoint folder integration

CRM and SharePoint are natural companions - both are web-based applications, both use the same workflow engine (WinWF), and SharePoint provides a natural completion to CRM's abilities by providing document management. A great way to use this - documents attached to a specific CRM entity, such as agreements on an account, or perhaps customer-delivered documentation attached to specific opportunities. Most recently, I was asked to integrate with CRM campaigns, and use SharePoint as a location to store the creative, graphics, etc. that go along with a specific campaign. This way, when a user accesses a campaign in CRM, all of the relevant files will simply appear in an additional tab on the form, hosted inside an iFrame.

In this case, I decided to store the documents for the campaign inside folders in the SharePoint document library - a folder for each campaign, all within a single library. I also decided that users won't need to access these folders from outside of CRM, so the name of each folder is simply the campaignid GUID to avoid duplicates or other problems. I also set up a special page to use in the iFrame that eliminates the SharePoint "chrome", giving me a bit more screen real estate to display the actual documents.

So now to create the folders dynamically - I need to ensure a folder is created for each campaign automatically. The "standard" method for doing this was grabbed from someone else (sorry, I don't have a link) and involves using an ActiveX FileSystemObject control. This method accesses SharePoint via UNC path, checks to see if a folder by the right name is in the library, and creates one if it is not there. An example is here:

var theIncidentId = crmForm.ObjectId;
var oShell = new ActiveXObject("Scripting.FileSystemObject");

if (! oShell.FolderExists("\\\\Netshare\\IncidentAttachments\\"+ theIncidentId) )
oShell.CreateFolder("\\\\Netshare\\IncidentAttachments\\"+ theIncidentId )
else{
}

Of course, you can use this code to access any fileshare - this is certainly not limited to SharePoint only. However, the fact that SharePoint document libraries are accessible via UNC means you can use this same functionality with SharePoint and it works great...

...Unless you can't get UNC access to SharePoint. This might occur for a few reasons - if SharePoint is not installed on port 80, or maybe if you use a proxy between the end computer and SharePoint, or if using an IFD, or if you are using SharePoint Online. In my case, I couldn't get to SharePoint for an unknown reason via UNC, so rather than troubleshooting SharePoint (which I don't really have control over) I decided to look into an alternative - using the SharePoint web services.

My inspiration came from here: http://blogs.msdn.com/crm/archive/2006/10/23/creating-folders-in-sharepoint-document-libraries.aspx . Their example used a callout, but this proved that the functionality was available in SharePoint. I had another example that involved finding a user's roles using a call to the CRM webservices (one example is here - there are others) . I realized that both ends of the process were there - I simply needed something to bridge the gap and access SharPoint via webservice to create the new folder. Lucky for me, I was able to find a helpful blog by Darren Johnstone that had created all of the javascript needed to talk to SharePoint - I just needed to modify it a bit and use it with CRM. Since Mr. Johnstone had broken the real work out into his classes, I had to do a bit of reverse-engineering to pull it back into an in-line function to make it work with CRM. The end result creates the folder in SharePoint with the webservices just like the FileSystemObject would, but now it will work with SharePoint anywhere! The only issue I haven't worked out yet is how to CHECK for the folder before attempting to create it, so right now I just let it error itself out (SharePoint won't create a duplicate folder) and ignore it. This could become a larger problem later, but it works for me for right now.
The final code is below:

// Only run code on Update forms
if(crmForm.FormType == 2)
{
var objectId = crmForm.ObjectId;
objectId = objectId.replace(/[{}]+/g,'');

// The service URL should go to the site with the document library
var varServiceUrl = "http://sharepoint/SiteName/Marketing/_vti_bin/lists.asmx";

// This function takes the doc library name and the name of the new folder
// You can also pass a third parameter to specify a root folder
var res = createFolder("CRMIntegration", objectId);

// Error catching, which I am currently ignoring
// if (res.status == 200)
// {
crmForm.all.IFRAME_SPDocs.src = "http://sharepoint/SiteName/Marketing/CRMIframe/CampaignCreative.aspx?RootFolder=%2fSiteName%2fMarketing%2fCRMIntegration%2f" + objectId + "&FolderCTID=&View=%7bE2F9780A%2d0546%2d4254%2d92EA%2d31B1A584914E%7d";
// }
// else {

// alert("error creating folder: " + res.statusText);
// }
}


// Functions to support SharePoint folder creation

// Creates batch XML file for new folder
// (Includes optional rootFolder parameter - see Darren Johnstone's blog for usage)
function createFolder(listName, folderName, rootFolder)
{
var batch;
batch = " if (rootFolder != null)
{
batch += " RootFolder='" + rootFolder + "'";
}
batch += ">";
batch += ""
+"1"
+"" + folderName + ""
+""
+"";
return updateListItems(listName, batch);
}

// Delivers message to SharePoint
// (Eliminated dependance on javascript classes)
function updateListItems(listName, updates)
{
var oXMLHttpRequest = new ActiveXObject("Microsoft.XMLHTTP");
var result = null;
var resultName;

oXMLHttpRequest.open("POST", varServiceUrl, false);
oXMLHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
oXMLHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems");

var packet = ["",
"",
"",
"",
"",
listName,
"",
"",
updates,
"",
"",
"",
""
].join("");

oXMLHttpRequest.send(packet);

resultName = "UpdateListItems";
var resBatch;
var status;
var statusText;

status = oXMLHttpRequest.status;
statusText = oXMLHttpRequest.statusText;

if (status == 200)
{
// Check for SharePoint error code
resBatch = oXMLHttpRequest.responseXML.getElementsByTagName(resultName);

var codeEl = oXMLHttpRequest.responseXML.getElementsByTagName('ErrorCode');

if (codeEl != null && codeEl.length > 0)
{
var spStatus = parseInt(codeEl[0].childNodes[0].nodeValue);

if (spStatus != 0)
{
status = 0-spStatus; // Note we make this -ve to prevent confusion with the HTTP code

var messageEl = oXMLHttpRequest.responseXML.getElementsByTagName('ErrorText');
if (messageEl != null && messageEl.length >= 0)
{
statusText = messageEl[0].childNodes[0].nodeValue;
}
}
}
}

result = {
status : status,
statusText : statusText,
responseXML : oXMLHttpRequest.responseXML,
responseText : oXMLHttpRequest.responseText,
resultNode : (resBatch == null || resBatch.length == 0 ? null : resBatch[0])
};
return result;
}

Recurring Appointments

Anyone who has been using CRM for a while knows that there is no support (at this time) for recurring appointments. However, with the fantastic workflow engine in v4.0, it is possible to simulate the functionality of a recurring appointment, without actually creating them in the same fashion as Outlook allows.

The first step in creating this functionality is to create a couple of extra fields on the appointment - one to specify the recur timeframe, and one to hold a date value. The recurrence field in my case was a picklist of yearly, quarterly, monthly, weekly. When set, this triggers the workflow. The date value will help us later in preventing a runaway workflow process.

Now we can create our workflow. For my sample, I triggered the workflow when the appointment was created, and checked for a value in the recurrence field - if nothing is there I know this is not a recurring appointment and the workflow stops. Next, I checked my date field. The purpose of this field is to hold the date (and time) of the original appointment record - the one that is triggering the recurring appointment. Because I don't want this workflow to enter an endless loop, I wanted to wait for the date of the originating appointment to pass before I create my next appointment in the future. This prevents each subsequent appointment from triggering its own workflow and continuing ad infinitum.
So my next step is to see if my custom date field contains any data. If it does not, I know this is the first appointment in the series, and I can create the next in the series and stop. If it does, then I need to wait for that date to pass before creating the next in the series.

Finally we get to the obvious part - for each value in my recurrence picklist, I simply have to specify the criteria for creating the next appointment. For example, if this field is set to "weekly" then I need to add 7 days to the appointment date fields when creating the new appointment. I also have to remember to set the original appointment date into my custom date field for use above. Everything else I copied over directly.

The end result is that I now have a rolling cycle of 2 appointments in my series. The next to occur has already completed its workflow (it created #2 in the future) , while the second in the series is waiting for the date of the first one to pass before it creates the next in the series and stops, and so on. As each appointment passes, the second in the series becomes the next, and it creates its own successor to repeat the process.

This workflow functions pretty well, but there are of course some limitations. If you need to make a change to the appointment, you have to make sure to change the second in the series, or that change won't get copied into any new appointments. You are also limited to only these two appointments in the series - if you have a weekly recurring appointment you will only see the next two weeks. Checking your calendar 2 months in advance won't show the appointment at all. You also have to set the recurrence field from the CRM appointment window and on a new appointment for anything you already have set up in Outlook (the Outlook client will not allow recurring appointments to be tracked at all). Limitations aside, however, this is a pretty decent workaround.

Enjoy!

Yes, another Microsoft CRM blog

I have been working with Microsoft's CRM product since version 1.2. Over the years, I have been excited with each new release of the product and the capabilities provided to me as a customizer as well as to the businesses that use the software. In its current incarnation, version 4.0, CRM has become a very powerful relationship management tool. More than simply tracking customers and sales (essentially all you could do in v1.2), v4.0 is simply a convenient and easy-to-use interface for a relational database - and you can use this database to track any information your business needs. With this most recent version, the database aspects are, in my opinion, essentially complete - you can map out your data in almost any way you desire. The next version(s) of the product simply need to focus more on displaying and working with that data in even more ways to meet the needs of an even greater segment of the business world.

I am beginning to blog about CRM because I would like to document and share my experiences with the product, both from a business standpoint and from a technical perspective. I think Microsoft CRM is a fantastic product that can do almost anything, and I intend to prove it here!