Microsoft Dynamics CRM’s Advanced Find feature is a powerful tool for searching and sorting through the data and records in your CRM. On a basic level, all you have to do is specify some criteria and run the Advanced Find query to return all of the records that meet those criteria. However, things can get a bit tricky when it comes to the Advanced Find tool’s specific requirements and limitations, and what seem like basic tasks end up being more complicated than expected. This blog post outlines queries and workarounds for three such needs:
- Creating a View that shows records in the current User’s Business Unit.
- Return parent records that have multiple specific child records of the same Entity type.
- Generate a list of records that fail to meet specific criteria.
Creating a View that References the Current User
Many businesses have CRM set up so that Users are in Business Units with other Users with whom they share records; for example, Contacts. While each User can easily see their own records in the My Contacts view, it would be beneficial to give them an easy way to view all Contacts owned by any User in the same Business Unit.
This can be accomplished by creating an Advanced Find that returns all Contacts with an Owner who is in a Business Unit that contains the User who is currently running the Advanced Find. This is how this looks in Advanced Find:
Now all you need to do is save this as a View and each User can easily view every Contact in their own Business Unit.
Parent Records with Multiple Specific Child Records of Matching Entity Type
Another common need that business have is return parent records that have multiple different child records of the same entity type. For example, you may need to see a list of every Contact who been sent both Email A and Email B. One common misconception here is that you could simply search for all Contacts that have a related Sent Email, as shown below:
However, this is actually searching for Contacts who have a related Sent Email record where the Subject is both “Subject Line A” and “Subject Line B.” This is clearly impossible, as the Subject field cannot contain both simultaneously. Instead, you can accomplish this by creating an Advanced Find that searches for Contacts with a related Sent Email where Subject equals “Subject Line A,” then reaching back to the associated Contact and searching for a related Sent Email where Subject equals “Subject Line B.” You can continue looping back to that owning Contact to specify all of your necessary criteria.
The query looks like this in the Advanced Find:
Return Records that Fail to Meet Criteria
One very common need is the ability to generate a list of records that fail to meet certain criteria, or that do not have a certain related record. For example, every Contact who has not been sent any emails in the last six months. Unfortunately, CRM does not allow you to simply run an Advanced Find for all Contacts who do not have a Sent Email record any time in the last six months.
The workaround for this is to generate a static Marketing List containing all potentially relevant Contacts, and then remove every Contact who has a related Sent Email from any time in the last six months, thus generating a Marketing List containing every Contact who has not been sent an email in the last six months. Assuming you have already populated your static Marketing List with the full list of potentially relevant Contacts, you would run the following “Remove Members” query:
After removing every Contact who meets this criteria, you will be left with a static Marketing List containing every Contact who has not been sent an email in the last six months.