Wednesday, January 8, 2014

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.

No comments:

Post a Comment