Workflow FetchXml Query

Out of the box, workflow does a pretty good job of allowing you to specify test conditions to perform the appropriate branching logic. For example, say you wanted to determine whether a contact's parent account has a credit hold - well... configuration-wise requirements don't get much simpler than this.

Now let's add a wrinkle. Let's say you have an account hierarchy such that you wish to determine whether the contact's grandparent account (i.e. the parent account of the contact parent account) has a credit hold in order to make the necessary branching decision. This minor change in requirement of just pushing the test condition up one level cannot be achieved with the out of the box workflow tools. This is because the workflow Check Condition (and for that matter Wait Condition too) can only interrogate attributes of the current record and of parent records. Any relationship beyond that definition cannot be queried using the standard CRM workflow UI tools.

Fortunately we have a way of breaking out of this box using workflow plugins. I have created a plugin and published it on CodePlex that allows you to pass in a FetchXml query that will be evaluated. The plugin will return to the workflow whether the said FetchXml query returns results and can therefore be used to take necessary branching logic.

Let's use the example above to illustrate how this works.

First define a FetchXml query that will perform the evaluation that you are looking to do (you can use the Advanced Find "Download Fetch XML" to help you with constructing this query). In our case this would be as follows:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">  <entity name="contact">    <attribute name="fullname" />    <attribute name="telephone1" />    <attribute name="contactid" />    <order attribute="fullname" descending="false" />    <filter type="and">      <condition attribute="contactid" operator="eq" value="{0}" />    </filter>    <link-entity name="account" from="accountid" to="accountid" alias="ae">      <link-entity name="account" from="accountid" to="parentaccountid" alias="af">        <filter type="and">          <condition attribute="creditonhold" operator="eq" value="1" />        </filter>      </link-entity>    </link-entity>  </entity></fetch>

NB: If you wish to pass in the current record's context as one of the filter criteria make sure that the following is included in FetchXml's condition clause (the attribute should be primary key of the entity that this workflow is running against):

<condition attribute="contactid" operator="eq" value="{0}" />

Now create a workflow and add the FetchXmlQuery step:

Pass in the FetchXml as a parameter to the FetchXmlQuery plugin:

And then define the rest of your branching logic based the results of this query.

If you have configured this correctly, you should be able to see the following results:

• When the contact's grandparent account has a credit hold

• When the contact's grandparent account does not have a credit hold

Error running a report via Outlook Client

Update from Microsoft (3/25/14):

          The issue appears to be fixed:

I received confirmation that the team has processed a change with the NLB system used by CRM Online. As a result, this incident with receiving script errors when running reports from your CRM Online organization should now be resolved.

Update from Microsoft (2/20/14):

After working with my team further, we were able to reproduce this problem internally (not using CRM Online). The problem is specific to a setting on the load balancers used by CRM Online. As such, it appears that this will affect Office365 based organizations (which to be honest makes more sense now). The setting on the load balancers was something recently applied in the move to CRM 2013, however our internal teams are getting approval to undo that change which should resolve this problem. They gave me an approximate 2 week timeframe to get proper approval and undo that change. We’re currently about 1 week through that, so we should be close to having this problem fully resolved.

Update from Microsoft (1/31/14):

The core problem is still being investigated, however a “workaround” was discovered that ties in with the new ‘Process Isolation’ feature of the CRM Client for Outlook. There is a registry key that can be applied on client systems to disable the feature. Testing on our side so far looks promising as the script errors on reports have ceased after disabling the feature. This is by no means a solution to the problem and our teams are still actively investigating. If you wish to test this, I’ve included the registry key below to implement on a client. If you confirm it also works from your side, we can discuss pros/cons of this workaround and if it’s feasible for your organizations.

Key Path: HKEY_CURRENT_USER\Software\Microsoft\MSCRMClientKey (DWORD): UseWebFormProcessIsolationValue: 0

-----------------------------------------------------------------------------------------------------------

In one of our CRM 2013 online instances we are receiving errors whenever trying to run a report from the Outlook client. The error looks something like in the examples below (errors occur one after the other).

This does not happen when running reports via a browser so clearly some kind of platform issue. It is somewhat sporadic too and seems to be related to browser cache. So it might go away and then come back at a later point in time. For example, one solution I tried was to delete/recreate the report as I figured that it was perhaps due to some upgrade issue from 2011. This solved the issue for a while but then it returned.

And this was confirmed by Microsoft who are currently troubleshooting the issue. The following is the current guidance provided by Microsoft:

Per our phone conversation, this issue with running reports in the CRM Client for Outlook against a CRM Online 2013 organization on our CTP authentication platform is currently a known issue that has recently been brought to our attention. We are actively investigating this problem and I’ll keep you posted as I know more. Currently, we believe this may be a server side setting – but the investigation has not yet yielded the exact cause of the problem. Unfortunately there is no workaround for the web client other than using the CRM Web Client (i.e. Internet Explorer).

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.