Advanced Find is one of Dynamics CRM’s best features. It allows us to dig deep into our data without needing to understand the complexities of database relationships, SQL or FetchXML syntax, etc. Advanced Find can’t always replace the aforementioned reporting, but it can get you close and sometimes that's practical. Let's look at a scenario -- it’s important for us to have at least one Contact associated with each Account. Advanced Find can tell us which Accounts have at least one Contact record associated with them, but it can’t tell us which ones do not. It would be nice to know specifically which Accounts don’t so Sales or Marketing can enter the missing data. If we know SQL and use outer joins or other tools we could get that info from Microsoft Dynamics CRM. But for the sake of simplicity, we'll use Marketing Lists to enhance Advanced Find.
Start with simple math
In the scenario below, there is a total of 1,081 Active Accounts. We know 784 of them have at least one associated Contact. Let's do the math: 1,081 – 784 = 297. That means 297 don’t have a Contact. Let's use Advanced Find within Marketing Lists to produce a list of Accounts that meet the first two criteria. Marketing Lists will provide a place ‘to do the math’ and see the leftover 297 records.
Create a Marketing List
Access Marketing Lists from the ‘Marketing’ work area:
Create a new Marketing List and fill in the Name, List Type = ‘Static’, Target As = ‘Account’. We’re ignoring Inactive records for this example. Click on ‘Save’.
Gathering all the Active Accounts
The next step is to populate the new Marketing List with members – members that represent all of the Active Accounts. To do that, click the ‘Manage Members’ button in the command bar.
The ‘Manage Members’ dialog box appear – click on Add using Advanced Find.
In the ‘Add Members’ dialog box, ‘Account’ should already be selected in ‘Look for:’. Select the ‘Active Accounts’ Saved View or another view that fits your own criteria and tweak the filters as needed.
Click on ‘Find’ at the bottom and CRM will return a list of Accounts that meet the criteria you specified.
At the bottom of the dialog box, select the Add all the Members returned . . . radio button and click ‘Add to Marketing List’.
Note that CRM found our 1,081 Active Accounts. You’ll see them all listed as ‘Members’ on the Marketing List form:
Removing the Accounts that have at least one Contact
A great feature of working with Marketing Lists is that you can not only use Advanced Find to add members, you can use it to remove members. On your Marketing List form click on ‘Manage Members’ and select Remove Using Advanced Find. Enter the appropriate filters – in our case, we’re going to return a list of Accounts with at least one Contact by searching for the presence of data in the ‘Created On’ field of each Contact record associated with each Account. That field MUST contain data if the record exists so it will indirectly tell us that there is a Contact for the Account:
Running this returns a list of 784 Accounts that have at least one Contact. Click on ‘Remove from Marketing List’.
That’s it !!!!
Now if we look at the list of members, we’ll see a list of 297 Accounts that no Contact has yet been associated with.
You can easily test the accuracy of your results by selecting an Account and checking to see if it has any Contacts:
A Handy Technique, But Not Perfect
Granted, this isn't the way an experienced CRM Admin or Consultant may tackle this problem. But you’ll have to admit it was pretty easy to get the data and it lets you quickly understand the scope of the situation as well as to see which records are affected. Just about any CRM user can understand and use this technique after putting it in practice a couple of times. If you need to go beyond that or can’t solve the situation from the confines of the Marketing List, you’ll have to rely on more sophisticated methods.
Power to the people!!
By John Clifton, Microsoft Dynamics CRM Consultant with