If you’ve used Dynamics CRM you’ve probably run across a limitation that has plagued the product since it’s introduction. It is the number 1 most voted item on the Microsoft Connect community site and although it’s status is currently “Resolved” it is still not possible to build a “Not In” or “Does Not Have” query in the Advanced Find tool that comes with Dynamics CRM, until now.
Cobalt’s CRM Intelligent Query gives you the ability to create these queries with a free, lightweight solution that uses native Advanced Find capabilities to infer a “Not In” or “Does Not Have” query when you use a specific filter on a child record. The idea is similar to this solution, but has been expanded to work on all entities and allows for nested links and more complex conditions.
How It Works
The simplest type of “Not In” or “Does Not Have” query is asking Dynamics CRM to retrieve All Accounts with No Cases. Take the following case for example. I am in charge of the support staff at my company and I want to find Accounts in CRM that have never submitted a support case, so I can reach out to those accounts to find out if they are using our product and if they are satisfied with it. This can be achieved using CRM Intelligent Query by building the following Advanced Find.
Notice that we are specifying that the primary key of the entity we’ve linked to, “Does Not Contain Data”. This is the trigger that CRM Intelligent Query uses to infer a “Does Not Have” or “Not In” condition. Because this query would always return zero results it is useless under normal circumstances. You can try it yourself on any type of entity in CRM. However, once you’ve installed CRM Intelligent Query, it will infer that what you intended by this query was to return all Accounts with No Cases, since retrieving all Account tied to a Case where there is no Case doesn’t make sense.
Using Nested Links
Now let’s go a little deeper. Let’s say the Account record, in this case, doesn’t get me all of the information I want because I’d like to know more information about the Primary Contact associated with this Account. We can take this Advanced Find a step further by using nested links to either a look-up value or a child record and build the following “Not In” Advanced Find.
Now, instead of finding the Accounts with no Cases, we are retrieving the Primary Contact of all Accounts with no Cases. But wait, it gets better.
Filters and Groups
Continuing with this example, lets say we not only wanted to find information about the Primary Contact of Accounts with no Cases, but also Accounts with cases that had a Satisfactory value of “Very Dissatisfied”. With CRM Intelligent Query you can include additional filters and group them using an “Or”. So, in the Advanced Find below we are asking CRM to show us the Primary Contact of Accounts who have never submitted a case or have a case with which they were “Very Dissatisfied”.
Intuitive and Efficient
Intelligent Query opens up a whole new way of looking at your data in CRM when building ad-hoc Advanced Find searches. There are alternative solutions to this problem (like this one), but often they require you to export FetchXml, modify it and upload it back to CRM as a read-only view. This is not only time consuming but non-intuitive.
CRM Intelligent Query takes advantage of an otherwise useless query and turns it into something useful. It does so by finally bringing “Not In” searches to users in the native CRM Advanced Find tool that they use everyday.
To download CRM Intelligent Query click the button below and let us know what you think or ask us questions. To see more experiments from The CRM Lab @ Cobalt head over to our experiments page.