The Elusive “NOT IN” Query Option

If you've used Dynamics 365 you've likely encountered the need to create a query only to find records that do not have related records of another type. Think: "Find all accounts that don't have a contact," these are common queries that are used for cleansing data or identifying a list of accounts which need follow-up. Until now, there has not been an easy way for users to create these queries on their own using the Advanced Find tool in Dynamics 365.

A new option has been added that now makes this not only possible, but also easy for any user who is comfortable with creating Advanced Finds.

Example: Search for 'Accounts' which do not have any activities

  1. Start a new Advanced Find
  2. Select the primary entity that you are looking for (eg. Accounts)
  3. Add criteria, if desired, to the primary entity (eg. Status Equals Active)
  4. Select the related entity that doesn't have any records (eg. Activities)
  5. Choose 'Does Not Have Data'

*Note: While you can add additional criteria for the primary entity, you cannot include additional criteria for the related entity. In our example above, we could have searched for accounts based on the status, but we could not find accounts that didn't have activities in the last year. For a more detailed query, you will still rely on previous options for "Not In" queries.

Learn other tips and tricks that may help you stand out from the rest by subscribing to our blog!

Happy Dynamics 365'ing!