One of the more powerful features of Microsoft Dynamics CRM has to be the Advanced Find tool. What may initially seem like an intimidating maze of menus to a new Dynamics CRM user unfamiliar with the underlying data model, Advanced Find may quickly turn into an invaluable tool for anyone who needs to be able to retrieve specific sets of data from CRM, be it for marketing campaign target groups, ad-hoc data analysis or simply streamlining the usage of the system with saved views for surfacing frequently needed information from the database.
Here are a couple of examples that show you how Advanced Find can go beyond the typical queries and deliver results that you might have not initially thought of being possible with the tool.
Referencing the Current User
Suppose you have a Business Unit structure set up in Dynamics CRM to reflect organizational units where the users generally work together on the same accounts. However, you’ve not restricted their visibility to records from other BU’s, so their view of all active accounts displays the complete contents of the database in a long list. While the users can easily filter their own records by using the My Accounts view, you’d like to offer them an option to see just the accounts owned by any user from their own Business Unit.
Sure, you could create a long list of system views that are dedicated to a particular business unit (“Accounts from Finland”, “Accounts from Sweden”, “Accounts from Cayman Islands” etc.). The problem with this approach, apart from the number of view variations you need to create and present in the view list, is that it’s not dynamic by nature. Since you can’t centrally set different default views for different user groups in Dynamics CRM, all the users would have to know how to navigate to the “View” tab and click on the “Set As Default View” button to select the view specific to their business unit.
Instead of all that manual labor, why don’t we build a query criteria with Advanced Find that says “show all accounts where the related owner is in a business unit that has a user that is me“. Yes, it’s not exactly the way you would formulate the sentence when communicating with human beings, but this is the language that works with Advanced Find. If you don’t believe me, just try the query below for yourself:
How about if you’re using Teams and would like to create a “my team’s records” type of a view? No problem, you can use the exact same method as with Business Units. Just reference the Team record under the Owning User entity and then add the same “user equals current user” criteria under the related user entity.
In fact, since starting from CRM 2011 all business units also have a default team where the BU’s users are automatically added to as members, the team approach actually covers both the “my business unit” and “my team” scenarios. If, on the other hand, you’d like to only reference custom teams and not BU teams, include a criteria for the team records that says “Is Default equals No” to exclude the default business unit teams from your view results.
Ok, so we get the results we were after, but what is the underlying logic that makes this query work? What we are doing in the Advanced Find query criteria definition in the above examples is referencing the relationships through this type of a pattern:
The key takeaway here is that there’s no need to limit your queries to only traditional one-to-many (1:N) type of hierarchies. In this example you start from the N side, then go through a “1” and spread out back into the N. Due to the flexible nature of the Advanced Find query designer in presenting all the available relationships, we are free to explore multiple different types of connections between the same entity in a single query. We pass through the user entity more than once but approach it from different relationships in order to define the final filter for the query.
Multiple Conditions for Related Child Record
Taking the exploration further, here’s a query method that may seem even less intuitive but is actually a more common requirement than the team/BU membership example. Sometimes you need to search for parent records that have two or more specific child records underneath them, meaning that the single query will have to find several different matches from the child records in order to qualify the parent record into the query results. Examples of such a scenario could be:
- Accounts that have child contacts with the roles Decision Maker and Influencer
- Customers who have bought both product A and B
- Contacts that have attended events in the years 2011, 2012 and 2013
- Orders that included line items for both Sales Inventory and Services
When building such queries the problem you may face is that the results include parent record that meet any single criteria, when what you’re interested in is only the records that meet all the different conditions. Taking the last example of searching for line items of several different product types and using the Opportunity and Opportunity Product entities, if we’re including the Sales Inventory and Services values into a single condition for the Product Type field, any opportunity that has either Sales Inventory or Service products will be retrieved.
How about if we add several different lines for the Product Type and specify each criteria independently? Unfortunately that won’t work either. Effectively what you’re searching for in such a query is for records where the Product Type field has both values Sales Inventory and Services at the same time. Since it’s a standard CRM option set that only takes one value at a time, your query won’t return any results since the condition you’ve specified cannot exist on any CRM record. It’s like searching for the Schrödinger’s cat that’s both alive and dead at the same time – unfortunately Advanced Find doesn’t support such quantum mechanics.
I used to be under the impression that you just can’t build a query like this with a Advanced Find. Then I saw David Jennaway post an elegant solution that in fact does allow you to apply multiple conditions for the child records and get the right search result. The trick is in continuing to build the query conditions downwards from the child entity but actually reaching back to the parent entity and branching back down to the child again. Like this:
What we’re doing here is first retrieving the opportunities that have Sales Inventory line items, then further limiting the result set down by saying that the parent opportunity of that line items must also have another child record that is of type Service. Instead of specifying the values to be searched for all in one go, we loop through each different value for the child record and return back to the parent record between each value. The image below illustrates the query logic:
If you need to search for more than two different values, just keep adding new layers underneath the previous query condition lines. I haven’t tested how many levels deep you can build the conditions before the Advanced Find UI explodes or the query gives incorrect results, but at least four different loops worked just fine in my experiment.
Beyond Advanced Find’s Capabilities
A follow-up question that may well come to your mind after this example is “can I also use this technique to search for parent records that do not have a particular child record?”, meaning in our case opportunities that have Sales Inventory line items but no Service items. The answer to that is unfortunately still “no”. You can’t construct a “not in” clause with Advanced Find, as there always needs to be a record found in the database to evaluate the query conditions against. This is the number one most requested Dynamics CRM feature on Microsoft Connect (login here first before trying to access the page) and it will be very interesting to see if the upcoming Orion release (“CRM 2014”) will include new functionality to address this need.
In the meantime, the workaround for building “not in” queries in Dynamics CRM is to use temporary marketing lists as a holding area for your query results and use the Manage Members feature to remove members from the list. As an example, when you want to find customers who’ve bought product A but not product B, first you should create a marketing list of accounts with orders on product A, then perform a query for product B and remove the result set from the marketing list. The one caveat here is that marketing lists don’t support records other than account, contact or lead, so performing a “not in” query directly with opportunities like we did in the previous example won’t be possible.
You might also want to review some of the other limitations of Advanced Find to keep you from banging your head against the wall while trying to build a query that simply isn’t supported. On the other hand, like the two examples shown in this post demonstrate, sometimes a little bit of creativity with the query conditions and especially exploration of the different relationships available between records in the Advanced Find query designer will allow you to achieve results that initially seemed impossible.
Leave a Reply