I think one of the coolest features in Workflow Elements is being able to run queries against CRM – but not just standard Advanced Find views – you can limit your query to just records related to the record the workflow is on. You can get a table of results back for an email or even do aggregation through FetchXMLand use the results in a Check condition. The possibilities are endless. A few examples I can think of off the top of my head:

• A workflow on an invoice – get information about the products for the invoice for an email.
• A workflow on a contact – get the total amount of their orders over the past year and use the result in a check condition.
• A workflow on system user to get the number of open opportunities, and their average value.

The possibilities are as endless as you are creative.

To set up a view and have it only bring back records related to the workflow, add a link in your query to the entity type which the query runs on, then add a condition {entity} Contains Data. Hat tip to Mike Ochs for coming up with such a clever scheme to build “Does Not Have” queries in Dynamics CRM Intelligent Query.

Examples

Query for all invoice products for the current invoice

This is the Receipt example – when an invoice is completed, send an email with an itemized list.

Start by querying for the record you want in the grid (Invoice Products), then under Related Records link to Invoice (Invoice ID is the field), then add the condition Invoice – Contains Data. This will bring back many results, but when Workflow Elements runs the query it will substitute in the ID of the invoice, so you’ll get just the products for the invoice.

Query for all completed invoices for a contact, that are greater than $100.00

In this example, you want just large, paid invoices. This illustrates how you can add any conditions or add any other links you’d like, as long as you can link back to the workflow record.

For this example, we want Invoices so that is the entity being searched for. Then I added my conditions for status and total, linked to Order, linked to Contact (via Customer), then filtered on Contact – Contains Data. That’s it!

Use FetchXML to get invoice total for all invoices (> $100) for a customer

This will be handled just like a typical advanced find. I started with an advanced find to add all my links and conditions, hit Download Fetch XML and made a few tweaks:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” aggregate=”true”>
<entity name=”invoice”>
<attribute name=”totalamount” alias=”totalamount_sum” aggregate=”sum” />
<filter type=”and”>
<condition attribute=”totalamount” operator=”ge” value=”100″ />
</filter>
<link-entity name=”salesorder” from=”salesorderid” to=”salesorderid” alias=”ae”>
<link-entity name=”contact” from=”contactid” to=”customerid” alias=”af”>
<filter type=”and”>
<condition attribute=”contactid” operator=”not-null” />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>