Today I’m going to discuss one of my favourite Interview Questions on Microsoft Dynamics CRM. While the questions is fairly simple, very few consultants are actually able to give the correct answer.

How can I – as an end-user – obtain a list of all Accounts without any related Contacts?

[bra_divider height=’1′]

Basically this questions boils down to how can I perform a “Not In” query in Dynamics crm?

When drawn in a Venn diagram like the one below… I want the blue bit of the All Accounts collection:

The most common answers I get include:

• Use the XrmToolBox and perform outer joins using the FetchXML Builder.
• You’ll have to use custom code for that.
• Export to Excel and user VLookups.
• Write SQL-statements.
• You need this add-on.
• You’ll have to use custom code for that (yes, this one is mostly said twice, trying really hard to convince me).
• It’s impossible.

All of the answers above are valid answers and will get you as an experienced Dynamics CRM Consultant the result you need… but they are just too complex for an end-user.

Typically an end-user does not have the knowledge to write custom Console Applications, complex SQL queries or FetchXML statements – although there are exceptions. In those cases where the end-user requires a no-code solution, you can achieve the desired result using the Marketing List Add or Remove Members functionalities provided by Dynamics CRM.

How does it work? Easy. The solution for this is actually shown in the Venn Diagram above. First, since you can’t perform the Not In query, you want to have a collection of All Accounts and later on remove the Accounts that have contacts related to them.

Create our very own “Not In” Query

Ready? Let’s go. Open up Dynamics CRM and Create a new Marketing List, targeted at Accounts.
Next, click the  -icon next to the Members grid to add accounts and select Add using Advanced Find. Click on any of the screenshots below to see the image in fullsize.

Construct an Advanced Find that returns all (active accounts). Hit Find.

When adding these results to the marketing list, be sure to select the option Add all the members returned by the search to the marketing list.

Cool. You’ve now created a Marketing List with all accounts in your CRM system. Now comes the ‘magic stuff’, click the  -icon next to the Members subgrid again (don’t worry, we know what we’re doing ;-)). This time select the option Remove using Advanced Find.

Again, we’ll construct an Advanced Find. Because we are going to remove members with this query (and create our “Not In” query), we now have to define the subset of records that do not match our criteria. Hence, create an Advanced Find that returns all Accounts with contacts related to it.

Almost finished. Make sure you select the option Remove all the members returned by the search from the marketing list and click the button Remove from Marketing List.

Awesome, you nailed it! You’ve created a Marketing List which contains all Accounts without Contacts in CRM. This list can now be used to create Views on in CRM, Export to Excel, … Similarly you can now create views to obtain other, probably more relevant, “Not In” queries in Dynamics CRM like “Contacts which haven’t been emailed in the last 6 months” or “Accounts which haven’t been visited by a sales representative this year”.

While I do think this is a pretty cool trick, which allows business users to creatively get the information they need, it does have some limitations:

• Only Accounts, Contacts and Leads are supported
• Users need permissions to create Marketing Lists
• Might be relatively slow when working with large data sets.

Be sure to let me know what you think and leave any feedback you might have!