CRM Intelligent Query (IQ) is the free Dynamics Solution for CRM 2013, 2015, 2016 and Dynamics 365 we made available earlier this year on AppSource for creating “Not In” and “Does Not Have” queries using the standard Dynamics Advanced Find interface. Soon after we published the solution to AppSource, Microsoft announced NOT IN support for queries in Advanced Find in Dynamics 365 v9. The Dynamics 365 version of Not In queries adds a lot of the same functionality that the initial release of IQ did, but doesn’t quite handle a lot of the more complicated outer join query types that IQ does. Let’s take a look.
With Dynamics 365 v9 you can specify the type of join on the relationship that you join to with the following updates to the Advanced Find interface.
The one nice thing about the built in Dynamics 365 support is that you can download your NOT IN query as Fetch XML with the outer join condition included. IQ doesn’t currently allow for exporting the related Fetch XML with the included updates to the markup. However, that’s pretty much all you can do because what’s immediately obvious is that additional conditions for your join are not supported. Also, again this is only available for v9 online currently while IQ works for CRM 2013, 2015, 2016 and Dynamics 365 on-prem or online. The same query using CRM Intelligent Query uses a special filter on the join since the filter on join itself isn’t available but would be achieved using the following query.
Slightly different interface, but you get the same results (i.e. cases with no tasks), but consider for a second that what you actually want are cases that have no completed tasks which could be even more valuable since those are the cases for which no one has followed up with the customer. With the latest additions to Advanced Find this still isn’t possible while it is as simple as adding another filter to the query above to achieve this with IQ.
This is where IQ has a huge advantage. Not only can you do the same type of query as above using IQ you can also add filters to the join to further filter your outer join and create true “Not In” queries that allow you to filter on the joined entity as well. I assume Microsoft will expand on this functionality in the future, but for now apart from very basic “Not In” queries the feature is truly limited.
CRM Intelligent Query has a lot of other great features not covered here including being able to be used for Dynamic Marketing Lists as well as whole host of possible query configurations for getting at the data you need in Dynamics. Check out more here and here and of course the #1 feature just like the updates to the product CRM Intelligent Query is 100% free to download from our site or from AppSource.