Suggested Wed, 06 Jul 2011 06:00:00 GMT by Peter HörnellPlanned 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 (40)
  • It is not at all encouraging to see that the suggestion is over 2 years old and has not been implemented—or had its non-implementation justified—despite it being the suggestion here with the most votes (370 at this time). It appears that Microsoft ignores the feedback we leave here. The limitations of FetchXML have probably been our biggest disappointment so far with Dynamics CRM Online, especially in light of how custom SQL queries are not allowed in reports. Being able to select something via negative logic seems like such a basic feature to me that I cannot understand why MS have not fixed this long ago. (Note that the workarounds suggested here are not satisfactory.)
  • I wanted to have an Activity view of all past due activities. I used past x years. However, I don't want it to include today's activities. Past x years includes today. I need "not today".
  • I would like to see all accounts that don't have a contact record.
  • I think the NOT IN clause is important not only here but also in the service area wherein an organization would like to raise new service requests who have service contracts and no service has been rendered in the entire year.
  • Have anyone noticed that since Rollup6 you can get a view of "users without assigned security roles". I just wonder/hope that might indicate that Microsoft have listened and that changes to the search are on the way! The mystey about the view is that when I click advanced find I can't see the filter, so something seems hidden. Just have to wait and see then, don't we :)
  • I'm glad you found it important. If this would be implemented along with my other suggestion, the possibilities with views in CRM would be awsome. This is the other suggestion I'm referring to: https://connect.microsoft.com/dynamicssuggestions/feedback/details/712745/give-views-ability-retrieve-columns-deeper-than-just-one-level-related-n-1
  • This is often asked for by our user team. It's only proved an issue when you are starting from a parent entity, and looking for examples where there is no child record at all, or e.g. created in the last 30 days. This is straightforward via SQL so our IT team can provide custom reports, but it would be a substantial enhancement to the advanced find capabilities.
  • I agree: this is long overdue and would be huge. It's especially important for CRM Online users, since you cannot use SQL and must use FetchXML for things like SSRS reports. I mean it's bad enough not being able to do this in Advanced Find, but for CRM Online not being able to do it in a custom SSRS report is even more limiting. Adding a NotExists subquery and a NotIn query to FetchXML is essentially what we would need as I understand it. Thanks for submitting this important enhancement request!
  • Yes I am. I'll try to give you some examples: Just a few day ago I got another request from a customer who wants to search for clients who doesn't have certain products x, y or z. (search for customers who has no orderproducts with a certain product) Give me all clients who I haven't made a call to. (search for customers who has no call activities with me as owner) When I make my advanced find, I can't search for "hasn't got relations to certain entities". Are you feeling my pain? :) There are workarounds but they aren't that pretty.
  • Excuse me for asking, but your are refering to entities here, correct? Could you expand on an example here for me?