Suggested Wed, 06 Jul 2011 06:00:00 GMT by PETER HORNELLPlanned Short Term
Category: Client-Side Programming

A very common request from customers is to find records WITHOUT a certain related record. Today I need to make a custom report for that. I think it would be great to be able to create views etc based on NOT IN statements in an advanced find. That could easily be used for customers to set up even more dynamics marketing lists too.

Status Details

Thank you for your feedback. This is a great suggestion! We will consider this in our roadmap.



Neeraj Nandwana

PM, Microsoft 

Comments (37)
  • I would like to see this issue resolved with an Inverted Results -button next to the Results button. I don't know how this would be accomplished in the transaction level though. I am aware it's not possible with any single SQL clause. But I think many users would be willing to wait a few minutes to get the results of this powerful new capability. There could be a warning: "This will take a few minutes, are you willing to wait", or something like that. Maybe all of the In-records could be marked with some kind of marker and after that return all the records that aren't marked. This would make it possible to have any search used as such or as inverted.

  • I'm new here but I'm really surprised. Why none of the ideas, like this, that are massively supported by the community is either in development or planned even in the long term???

  • Ouch, this was marked as Resolved at some point? Crazy...

    What's odd is that SQL Server actually allows this. The EXCEPT clause in SQL does what we need and would seem practical to utilise in an Advanced Find because it allows you to use a select clause after it.

    With CRM built on top of SQL Server you'd think this would be entirely practical, and it's been in SQL Server for years now.

  • How should this be solved in CRM 2016 (Update 1)? In no way this has been solved because it is only available for hierarchical enabled entities. What if I don't have such relationships? Michael
  • MSFT - This is not resolved like other users have said. Please provide an fix for this very annoying and well-known problem. @Mike I am just curious what has been released instead, that causes this issue to be marked as resolved? It could be some other hidden feature that could be useful for us to know, since the problem mentioned in this bug is certainly not fixed.
  • The MSFT Solution is not sufficient for key users that are creating their own advanced find queries. There is a tool from COBALT will help: http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find This topic is NOT resolved in Dynamics CRM, from my Point of view.
  • @MSFT - This** is helpful but not sufficient. You all might be working on making this available *in advanced find* and, if you are, that's great news! But until then, the requirement is not met. End-users need to be able to do these queries in the UI. **https://msdn.microsoft.com/en-us/library/dn531006.aspx
  • Here is a common one I hear often. Return a list of Contacts who have bought Product A but not Product B. Product is a lookup on an entity called Sales History, which has a N:1 relationship to the Contact entity.
  • "Resolved as Tracking" means that this idea has moved into Microsoft's internal feature tracking tool and is somewhere in the backlog. Unfortunately, we don't know when this feature might make it into CRM. The good news is that until then, there is a free add-on called Intelligent Query available from Microsoft partner, Cobalt, that enables NOT IN searches from within Advanced Find. http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find
  • He's a workaround for dynamics marketing lists (at least). Using the FetchXMLBuilder in the XRMToolbox it's possible to create outer joins and not exists (i.e. is null) joins and update an existing marketing list. Not an end user friendly solution, but possible for an administrator to use.