Monday, January 27, 2014

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

No comments:

Post a Comment