Microsoft Dynamics CRM: Not Quite Multi-Tenancy

Multi-Tenancy or Bust
One of my challenges with the current project I'm working on is to make our solution behave for each franchise owner as if they are in a system of their own, completely separate from each other. Yet, allow the home office to easily roll-up data from each franchise, push leads and other commonly shared data to each franchise, and run scheduled and manually initiated workflows for every franchise.

By far the easiest and most effective way to keep franchise owners separate is to use a multi-tenant solution. If each franchise were in their own CRM Organization, they cannot see the other franchise data. This also lends itself to developing a common core solution, applied to all organizations, yet allow each franchise to make modifications above and beyond the core solution.

However, a multi-tenant solution complicates the home office solution. You can easily end up with disparate and incompatible solutions that are impossible to merge or roll-up.

We've decided to use the built in business unit structure in Microsoft Dynamics CRM 2011 to keep each franchise separate. Each user role has been duplicated to give the franchisees roles that limit their data access to their respective business unit. For the most part this appears to solve our problem. We can easily roll-up data to the home office root business unit level and enforce a common core solution for each franchise. Business rules can be easily deployed and enforced across the network. The rate situation where a franchisee needs to create additional entities, forms, and workflows can be handled and controlled with business unit specific modifications.

Um, now what?
We ran into a situation that, at first, looked like we weren't going to get around. In a multi-tenant environment, this would have been somewhat simpler to implement, but would have involved denormalization of the data. We have hierarchical accounts where the topmost account represents a national service provider, an NSP. The home office would like to track the business each franchise conducts with the local NSP accounts. At the same time, each franchise wants easy access to common NSP account information and contacts without maintaining their own version.

Altered Access
This sounded like a pretty simple problem, I just need to create a commonly accessible NSP account at the home office level and allow each franchise business unit read-only access to it. They can pick it as the parent for their local NSP account.

In order for the franchise business units to see the home office NSP account, I altered their security role giving them Organization level "Read" and "Append To" access to the Account entity. This wasn't quite what I wanted. Each franchise business unit now had read-only access to each other's accounts.

Let's Share
After undoing this, I changed the sharing permissions on the home office NSP account, allowing each franchise business unit "Read" and "Append" access to the account. It's not quite as easy to maintain, I'll have to write a workflow activity to automate sharing NSP accounts, but it's not bad.

... but not this much!
At first, this looked like it was going to work. However, once you select the home office NSP account as the parent account, any franchise business unit could read the local NSP account. The read permissions on the home office NSP account were applied to the franchisee's local account. Not what I was expecting at all.

It's all about Relationships
After further digging, I ran across Cascading Team Permissions in Microsoft Dynamics CRM, a blog post by    Benjamin Whitestone, a CRM Analyst at Armanino McKenna. In it, he explains how the default Parental Account to Account relationship behavior causes the user rights of the parent record to be applied to the child record. Fortunately, Microsoft allows you to customize this behavior.

Change the Type of Behavior from Parental to Configurable Cascading. Then change Assign and Reparent from Cascade All to Cascade None. This prevents selecting the home office NSP account as parent from cascading the organization level read privileges to the franchisee child account. 

Now, when the home office NSP account is selected as the parent of a franchisee's local NSP account, only the home office business unit and the owning franchise business unit can see it. On to the next challenge.

Microsoft Dynamics CRM: Oh The Pain - Dialogs

Don't get me wrong. Microsoft Dynamics CRM is an awesome framework. But like any framework, it's a ... framework. If you're given a hammer, everything better be a nail or you're in for trouble.

I'll periodically update this post with whatever I happen to be ranting about at the moment. It's meant to be a cathartic, but not entirely.

The Process/Dialog Editor
I once had a job in the early 90's working for a company that sold bar code printers and developed data collection software. I primarily wrote payroll processing software in C that ran in conjunction with a "user friendly" application. The application was constructed using a 4GL so our clients could make changes.

4GLs R US
A 4GL is supposed to allow humans an easy and friendly way to develop code without writing code. It attempts to solve the same problem COBOL was designed for. How can we let non-geeks develop software solutions to problems without hiring expensive geeks? You usually "programmed" a solution by selecting statement types from a drop down box, and then filling in placeholders with missing values or selecting variables, constants, or operators from other drop down boxes. As long as you could stay within the bounds of the 4GL, it could actually accomplish something.

However, for a computer programmer, working in a table driven 4GL can be frustrating at best and damn near impossible most of the time.

We Cheated
The programmers unfortunate enough to work in it cheated and edited the underlying text file rather than use the 4GL editor. It did nothing about the 4GL limitations, but at least we didn't have to use the crappy editor.

Back to the Future
The MS CRM process/dialog editor makes me feel like I'm reverting back to my earlier experience and I'm not diggin' it. Not at all.

Clicking my way through a gazillion dialog boxes to write a simple expression is painfully slow. Actually, everything is painfully slow.

Substituting variables in a dynamic query is a convoluted painful dead-end process. 
Once you've done it, you cannot edit the dynamic query. The FetchXML is locked in a read-only text box that you cannot edit. All you can do is alter the value parameters or delete it and start over. So close, yet so far away.

The limited logical expression syntax forces excessive statement nesting.
There is no mechanism for re-arranging nested statements. If you need to wrap a condition statement within a condition, you cannot. Don't invest a lot of time and effort within the condition statement blocks because you're going to have to delete them and write click them all over again.

Linking to another dialog is a one way proposition.
Once you leave, the calling dialog is finished. To use a more advanced parlance, think GO TO not GO SUB. Yes, BASIC is more advanced. Sure, you can link to the same dialog. Although this is billed as a recursive dialog, it is not. True recursion would return to the calling dialog and continue execution at the point it was called, not leave and never return.

Option Sets are not query-able. 
Who designs a database system without representing look up values in a table? Hello, every heard of third normal form? You could at least provide a query wrapper around them Now I have to manage a hard coded list in two or more places.

A possible work around?
Or maybe I can add a table that parallels the option set with an automated process to keep them in sync. That didn't work. You cannot use values from a custom entity. You can't substitute an integer or text variable for the value either. At first glance, this makes sense from a referential integrity perspective. There isn't a relationship defined between the custom entity and the field using an option set. However, since the actual option set that does define the valid values for the field cannot be used, and you are forced to define your own "option set", what's the point of preventing this? Referential integrity isn't protected if I have to hard wire up the valid values.

Let's try a custom workflow activity that converts an integer into an option set value. 

It's a convoluted solution, but it worked.

This post by @devkeydet gave me the clue I needed. He wrote a simple workflow activity to convert an integer into a two option type. 

What you need to do is write, compile, and register a custom workflow activity. In the activity, you create a class that takes an int as input and outputs an OptionSetValue.


    public sealed class IntToLeadSourceCode : CodeActivity
    {
        [Input("Integer Value")]
        public InArgument<int> InputValue { get; set; }

        [Output("Option Set Value")]
        [AttributeTarget("lead", "leadsourcecode")]
        public OutArgument<OptionSetValue> OutputValue { get; set; }

        protected override void Execute(CodeActivityContext executionContext)
        {
            var integerValue = InputValue.Get<int>(executionContext);
            OutputValue.Set(executionContext, new OptionSetValue(integerValue));
        }
    }

After you compile and register the assembly in CRM, you can call the activity to convert the integer value from the selected user value from a query which is based on a custom entity that substitutes for the option set (e.g. Lead Source Code).

Now all I have to do is write a workflow activity that executes when changes are made to the custom entity so I can synchronize it with the option set. It should also be easier to maintain the option set this way.

Got it? I'll break it down into the details in a separate blog post.

Is there a way out of this mess?  
What's this? The MS CRM SDK has sample code to create, retrieve, update, and delete a dialog programmatically? Cool! Editing workflow XAML by hand isn't going to be fun, but at least I can use a text editor. If I'm feeling ambitious, I could write a DSL and compiler. Add a little intelli-sense and we'd have something akin to a modern IDE.

A moment of silence please...

According to this, creating and updating a dialog outside of MS CRM is unsupported. I'm not sure what "unsupported" actually means in this case given the sample code from the SDK. Most likely, I'm probably stuck with the "user friendly" GUI editor. Sigh...

Microsoft Dynamics CRM 2011: Field Formats

This is my first post in a series titled "Microsoft Dynamics CRM 2011". I'll post issues and how I resolve them as I work on a CRM project I'm managing. Oh, and rants as well.

The field data types and formats are rather limited in MS CRM. As a programmer, I'm accustomed to having my cake and eating it too. One of the unfortunately frustrating aspects of using a solution framework is needing to work outside the borders. Things the framework handles natively, are inherently easy to do and a great time saver. Once you move out of that box, it can get dodgy. 

What I need is a positive decimal integer, not a number. What's the difference? A decimal number, in it's simplest form, is expressed only with digits - no group or decimal separators or signs. The Whole Number format in MS CRM uses the global number format settings and cannot be overridden at the field level. 

The workaround is to use a single line text field (the normalized database guru in me cringes) with a client side function that strips out anything other than digits from the form field. I don't care for it, but it's the best solution I've found so far.

At least in 2011 there is a mechanism in place to support function libraries. Schnell Kontakt, from Dynamics Consulting München, blogged how to call a function from a library of common JavaScript functions on the change event of a form field to handle this:

Re-usable JScript Libraries in Dynamics CRM 2011 Introduction: Web Resources and JScript Libraries 

MB2-866 Microsoft Dynamics CRM 2011

SBI is pretty big on Microsoft certifications. The director of IT has at least a dozen and it's one of my quarterly goals. Today I took and passed exam MB2-866 Microsoft Dynamics CRM 2011, Customization and Configuration.

It wasn't very difficult to study for. I've been reading Microsoft Dynamics CRM 2011 Unleashed by Marc J. Wolenik, Damian Sinay, and Rajya Vardhan Bhaiya and taking practice exams. It's an introductory book and doesn't really go very deep, but it covered the material pretty well. I wish Microsoft would provide a test kit for the CRM exams.

The last time I took an exam (70-100 Analyzing Requirements and Defining Solution Architectures) was about 14 years ago. It's way out of date, but the Microsoft Solution Framework information from it is still relevant.

One of the really convenient things SBI does is pay up front for the exam and provides study material. Sweet Systems did almost the same thing. They would reimburse up to three attempts to pass an exam.