Anyone who has used Microsoft Dynamics CRM for a significant amount of time has run across a limitation that has plagued the product since its introduction. It is the number 1 most voted item (https://connect.microsoft.com/dynamicssuggestions/Feedback/Details/678568) on the Microsoft Connect community site, and although its status is currently "Resolved", it is still not possible to build a "Not In" or "Does Not Have" query in the native Advanced Find interface. With the release of Microsoft Dynamics CRM 2013, Microsoft added the ability to execute a left outer join using FetchXML, but there is no reasonable way for an end User to leverage this new functionality.
However, there are several workarounds:
- You can programmatically create views in Microsoft Dynamics CRM (www.powerobjects.com/2013/11/07/perform-left-joinfetchxml-display-results-crm-2013), but this requires a developer’s assistance or an external application to modify and upload the XML so you cannot create or update views on the fly.
- You can create a marketing list that contains all of the records for the entity you’re searching for and then filter out the records you don’t want using a second query. Unfortunately, this will limit you to only accounts, contacts, and leads and doesn’t provide you with the ability to have the view change with time.
Seeing the limited options available and the vast need for a better solution, The CRM Lab @ Cobalt decided to come up with a solution that was flexible and accessible for end Users. We’ve named the solution Intelligent Query for CRM (www.intelligentquery.com), and it’s free to the Microsoft Dynamics CRM community. Intelligent Query (IQ) gives you the ability to create these queries using the native Advanced Find capabilities to infer a "Not In" query when you use a specific filter on a child record. The solution works out of the box on both custom and standard entities and allows for nested links and more complex conditions. It also works with Marketing Lists as well as ad-hoc Advanced Finds.
How It Works
The simplest type of "Not In" query is asking the system to retrieve All Accounts with No Cases. For example, you are in charge of the support team at your company and need to find all Customers (accounts) in CRM that have never submitted a support case so you 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 and building the following Advanced Find.
Welcome to the User Group for Dynamics 365 & CRM [D365UG|CRMUG], we’re so glad you’re here! While we realize you may be here to troubleshoot a technical issue or simply learn new tips and tricks, we’d love the chance to share with you the incredible benefits the User Group has to offer [and don’t forget, if you’re interested in an incredible in-person learning opportunity - join us in Nashville for our Summit event]!