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)
2. In the ‘Manage Marketing List Members’ area, Select Manage Members; and select “Use advanced find to 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”.
3. Our Marketing List will now only consist of Accounts WITHOUT any contacts, something we cannot do with Advanced Find alone.
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.