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!
- 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.
- Manage the list’s members by clicking on “Marketing List Members” and then the “Manage Members” button on the ribbon.
- 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.)
- Select all the returned values to add to the list.
And the values are added to the list.
- 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.
- Select the values to remove from the list.
- 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.
- Looking for more tricks for Marketing Lists? Try this post on Adding to Marketing Lists with Workflows in CRM 2011
- Looking for more advanced reporting options? Check out this post on CRM 2011 Reporting Options