Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Accounts With No Contacts and Fun With Subqueries

Post Author: Joe D365 |

A client recently asked me how we could use advanced find to give us a list of the Accounts that do not have any Contacts associated with them. I have been asked this question a few times in the past, and I had always assumed that it would be straightforward and a breeze to create. Unfortunately, this is not the case, but fortunately for my client, I have built a pretty nice workaround to solve this business problem.

Underneath the Advanced Find interface is a 'FetchXML Engine' that powers a lot of the Dynamics CRM solution. While we can create a number of queries in the Advanced Find area that span multiple entities, there are a number of things we CANNOT do with Advanced Find. Chief among the things not supported are 'NOT IN' sub queries.

I had resigned myself to the fact that I was going to be writing SQL statements for customers interested in obtaining 'NOT IN' type information, until a solution smacked me in the face one day: There is no need to do these things in SQL. We can accomplish a 'NOT IN' subquery by using a little CRM hacking -- leveraging a combination of Advanced Find and Marketing Lists to extract this information!

1. Create a Marketing List and Populate it with All Active Accounts. (Remember, we can create a marketing list directly from an advanced find by choosing 'Add to Marketing List' from the 'More Actions' drop down menu when viewing the results of our find)

AF Results

2. In the 'Manage Marketing List Members' area, Select Manage Members; and select "Use advanced find to Remove Members"

Remove Members

-In the query creation window, select active account, linked to contacts, from the search list. Click Find, then select "Remove from Marketing List".

Search

Remove

3. Our Marketing List will now only consist of Accounts WITHOUT any contacts, something we cannot do with Advanced Find alone.

AF Results

We can do a number of things with this information. We can export this list directly to Excel, or, we can run another Advanced Find, this time searching for Accounts that are members of the Marketing List we just created. We can than use the results of that advanced find to mass edit, mass delete or do whatever action the business situation calls for.

Joe CRM
By 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.

6 comments on “Accounts With No Contacts and Fun With Subqueries”

  1. Thank you thank you thank you! You just solved a problem that a $220 per hour Dynamics expert couldn't fix for me.

  2. It's too bad that we couldn't do this with Dynamic lists! Or set up an "Include" and then a "Remove" query to be run...

  3. Even with CRM 2013 I could not achieve this.
    Thanks for your great article, really nice an easy solution!!

PowerObjects Recommends