Workflow: Comparing Two Null Fields

You can use the Check or Wait Condition in workflow to compare one field to another field. However if both fields are null then the comparison evaluates to false. For example, say we compare "street 3" of the contact to "street 3 of the parent contact record - if both of these fields are not populated then the comparison evaluates to false rather than true. The following workflow snippet illustrates this phenomenon:

Typically that's what happens when comparing nulls from a database perspective because when you compare null to null you are essentially comparing an "unknown" value to another "unknown" value rather than "nothing" to "nothing" (nothing = nothing; whereas an unknown value cannot be said to equal another unknown value). CRM workflow behaves accordingly. I'm not sure that I would have designed it this way as most humans would look at two empty address fields and you would have a hard time convincing them that they in fact have different values which is what the workflow evaluation is essentially telling them. However this is how it has been designed, so null values need to be taken into account when designing such comparisons.

Out of the box  workflow does not have the equivalent to the SQL isnull conversion function which allows you to specify what null "means" and therefore perform a simple comparison (e.g. isnull(field1,'x') = isnull(field2,'x') will evaluate to true if both field1 and field2 are null since in that event they are both converted to "x" and of course x=x).

So one option is to create a workflow plugin that will perform this logic.

In the absence of such a plugin, you could use the following workflow logic to essentially implement this isnull comparison feature. It does tend to make the workflow a little longer... especially if there are multiple conditions to be evaluated as then you need to perform this comparison block for each field be compared. But it does tend to do the trick.

The basic comparison logic is that when the condition evaluates to true then "do nothing". If it evaluates to false, then perform the action associated with a false evaluation and stop processing. At the end of the comparisons, perform the action associated with a true evaluation as if the workflow got to this point, it must have passed all comparisons.

CRM Online Bulk Update Options

Having previously discussed the challenges with running bulk updates in CRM Online, I thought it would only be right to follow up with a post that provides some guidelines for doing so in the event that this is required. For simple updates you can obviously use the multi-edit feature and for slightly more complex requirements you can obviously use the "re-import from Excel" technique whereby you can export the data, making changes to the data and then import it back into CRM. And finally, if you have third party tools available such as Scribe Online or Scribe Insight you can obviously use those to cover pretty much any update requirement you have.

But what options do you have in the absence of such tools in cases where the update is beyond the complexity of the simple tools provided by CRM without having to resort to programming techniques to achieve the objective?

One alternative is to combine the power of workflow with the "re-import from Excel" technique.

CRM workflow is pretty powerful these days providing a lot of capability of manipulating CRM data using standard workflow actions and freely available codeplex solutions. So assuming your update requirement can be constructed via a workflow all you need to do is to "trigger" that workflow to run against all the records you want it to run against.

This can be achieved as follows:

• Create a custom text attribute called something like "Temp Workflow Update" 
• Create your workflow to trigger off the "field change" of this field
• Construct an Advanced Find query that retrieves the subset of records you want to update and include the "Temp Workflow Update" field in the columns

• Export the rows for re-import

• Go into the Excel file and add some text value to your "Temp Workflow Update" field, copy down, save and re-import

• This re-import should now trigger the workflow which will carry out the update that you configured it to perform.

CRM Online vs. CRM On Premise

The CRM Online platform has really come along way over the years coming closer and closer to its on premise counterpart. There is however one aspect which poses significantly more challenges than the on premise version. Specifically this is the limitation that the CRM online data cannot be accessed directly via SQL.

This limitation typically impacts the following two fairly common-place requirements:

1. Reporting

For CRM Online you are forced to use FetchXML to retrieve data rather than SQL. And while most report requirements can be satisfied using this technology it requires a little more effort than standard SQL reporting. Frankly, it is far easier to construct a query in SQL than FetchXML and the comparative difficulty between the constructs only increases with increasing complexity of the query required (or you can break the report down differently to avoid more complex query requirements when using FetchXML - "tricks" you wouldn't normally need to consider when using straight SQL). And of course for a complex reporting requirement, FetchXML may not be flexible enough to extract the data in the way that is required making it necessary to perhaps "flatten" the data using plugins or using a tool such as Scribe Replication Services before reporting against. This as compared to on premise reporting where you can use techniques such as dynamic SQL where the sky is really the limit.

2. Bulk Updates

In the on premise version of CRM, you have the ability to easily make bulk updates to your data. Though running direct SQL updates is officially "frowned" upon by Microsoft, this is done as a form of CYA. Allow me to explain - the integrity of the CRM database is maintained via the web services business layer. The minute someone is allowed to "tamper" with the underlying data directly all havoc can break loose. There simply is no way to protect against someone who is intent on corrupting the database. Not that anyone will do that intentionally of course but someone who is insufficiently knowledgeable in SQL and/or the CRM database may unwittingly introduce "problems" by trying their hand at SQL. And Microsoft of course cannot control who would be performing such updates so therefore they issue a blanket statement saying that the direct updates to the CRM database is unsupported (I once had the rather amusing case with Microsoft support where the resolution required running a script that they provided with a cautionary note that direct SQL updates are not supported - in the same email!).

However if you are sufficiently knowledgeable and take the necessary cautionary measures - it is difficult to argue against performing direct SQL updates with a straight face. The first reason is due to the relative ease that such updates can be constructed and run. And the second and often more valid reason is due to the speed in which it will be applied. If you need to run an update in a table with millions of rows - the difference will be between a few minutes and potentially a few days via web service.



Please don't get me wrong - I am not at all down on the CRM Online option. In fact, I think it is actually a pretty amazing platform and has some significant upsides from the on premise version - especially for smaller companies where the playing field is levelled in that they also have access to an enterprise level strategic software solution at a very affordable price and without the necessity of having their infrastructure and/or IT department. The barrier to entry was significantly lowered when CRM first offered its online solution. However, when evaluating the merits of going on premise vs. online for a given company's requirements - I would definitely factor in the above against the stated long term requirements of the undertaking.

CRM 2013 Insufficient Form Permissions

We had some users who encountered a strange error in their upgraded CRM 2013 environment. We saw this happen on the account and contact form whereby the users were presented with an "insufficient permissions" error when they attempted to open the form.

The resolution proved elusive because the issue itself was very inconsistent. A little hard to explain or understand but the following example illustrates the type of behavior that was being experienced:

1. User experiences permission issue
2. Assign system administration role to user
3. User able to open forms
4. Remove system administration role from user
5. User still able to open forms
6. Later on user again experiences permission issue

So without changing anything the problem mysteriously goes away. Only to resurface at a later point in time (usually after logging out/in but not even that was consistent). So somehow this issue seemed to "cache".

This was despite the fact the user roles assigned did indeed have the necessary read permissions for these entities. And the users could in fact see the views for these entities but were blocked with the message above when trying to open any record.

The first thing to track down was to ensure that the users had permissions to the forms in question. After all, we had created a custom form to replace the built in version as part of the 2013 upgrade merge effort. But this also passed the test with the form being accessible to everyone (and enabled as fallback even though this was irrelevant since this was the only active Main form for the entity).

So this issue had to somehow be permissions related. After a bit of trial and error the issue seemed to hinge around the following "Process Configuration" read permission. This permission existed in CRM 2011 but for some reason (bug?) CRM 2013 is now requiring it to open these forms.

CRM 2013 Notes Control only showing notes

CRM 2013 has a handy new feature for displaying activities, notes, and posts in a single control. This is very easy to configure by just selecting the "Notes" control in the form designer.

You can subsequently also define the default view for this control in the properties. As an aside, it's also recommended to uncheck the "Display label on the form" check box as it will display better without this.

So far so good. Except for one small wrinkle - the "Activities" and "Posts" tabs for this control do not show...

I found a solution to this issue which is outlined in this blog post. Although it does appear to be an issue even for forms that have already been "upgraded". Case in point - the form that I was working with was a new form created in CRM 2013 for an existing entity and the issue still appeared.

Anyway, as described in that post, the issue is fixed by modifying FormPresentation node for the form in question from 0 to 1.

After importing and publishing, the 3 tabs for this control display as expected.

CRM Manipulation Library Date Calculation Issue

Speaking of the CRM Manipulation Library solution, I encountered an issue with the date calculation logic. Specifically with regards to the somewhat misleadingly named "Add Days" function. Misleading in that if you look at the function, you will notice that in addition to the capability of adding days, you can also use this to add years, months, weeks, hours and minutes to a given date.

This is an important clarification as adding a month to date is not always going to be the same as adding 30 days to a date. Similarly for years (as in the case of a leap year). And obviously being able to specify the more granular hour and minute parameters is also helpful. We probably could have worked around not having the week parameter but it does make life a little easier by not having to perform additional calculations.

Anyway, the issue that I encountered is that ironically if you do not specify a non-zero "Days To Add" value and just supply one of the other parameters, the calculation will not work. That is, if we were to specify parameters in the screenshot above (where we're trying to add years to the date) it would not work.

Luckily there is a simple workaround solution. Below is the same function call except we trick it by subtracting a day value and then cancel it out by passing in the equivalent days in the week parameter.

If you needed to use the week parameter, then you could similarly pass in 24 hours and cancel it out by passing -1 to the day parameter.

Workflow Plugin CRM Online Compatibility

There are a lot of useful free add-ons available for Dynamics CRM that can add some serious customization features. The issue in many cases is that these add-ons were only released for the on-premise version of CRM.

For example, take the very useful CRM Manipulation Library which contain some important calculation capabilities. I especially find the Date Utilities to come in handy as there is often a requirement to calculate an off-set of days or business days as part of workflow logic. But when you try import the solution into a CRM Online instance you will receive an error that looks something like the following:

The good news is that this can be made compatible with CRM Online by following these steps:

1. Unzip the solution into a folder
2. Open the customizations.xml file
3. Perform a search for the IsolationMode node
4. Change IsolationMode value from 1 to 2
5. Update the original zip file with the modified file

Once you have done so you should be able to import it into CRM Online. Applying this trick does not necessarily mean that the plugin will work as it might fail on something when trying to execute. However it also just may function without a problem which is indeed the case with the CRM Manipulation Library. So it's definitely worth a shot. If your plugin does not work after performing this tweak, you'll have to do some debugging at the code level.