In Microsoft Dynamics CRM, searching for records that meet specific criteria is really straightforward. However, people often want to search or report on records where data does not exist. The most common example is prospects without Opportunities. There isn’t a direct way to search for the absence of the Opportunity record, but with a few extra steps and the use of a marketing list, users can easily create this list. Without further ado, here is how you can search for accounts or contacts without Opportunities in CRM 2011!

  1. Create a static marketing list for the correct entity for your prospects. This could be Accounts or Contacts depending on how your organization works.

    Static is the default setting for marketing lists. What this means is that this list will not reference changes to your search criteria. (Opportunities added after this list is created will not be reflected. This is a snapshot in time.)

    In my example, the prospects exist in the Account table.

    Searching for accounts or contacts without opportunities in CRM 2011

  2. Manage the list’s members by clicking on “Marketing List Members” and then the “Manage Members” button on the ribbon.

  3. Use Advanced Find to add all the possible prospects to the list. In my example, I’m looking for all active Accounts. If you are looking for specific criteria like Customers in Texas without Opportunities, then this is where you would specific Texas as part of your possible prospects.

    (Note: If you selected “Contact” as the member type is Step 1, this search should be against Contacts instead of Accounts.)

  4. Select all the returned values to add to the list.

    And the values are added to the list.

  5. Remove the Accounts with Opportunities.

    As mentioned previously, it’s easy to search for criteria where the data is present, so in this case, users can search for the values with Opportunities and subtract them from the list.

    1. Press the Manage Members button on the ribbon again

    2. Select Use Advanced Find to remove members.

    3. When selecting the entity, the related Opportunity will be at the bottom of the entity list under the “Related” heading.

  6. Select the values to remove from the list.

  7. The selected values are removed.

Congratulations! The list that remains is the list of prospects without Opportunities. It looks like this super sales team has open Opportunities for about half their customers.

Happy CRM’ing!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.