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. (more…)