Related sites:
Newsletter: Perspectives on Power Platform
Company: Niiranen Advisory Oy
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.
Nice one. Really helping and time saving.
For referencing the accounts in the same business unit, it seems more simple to use the “Equals current business unit” directly from the Owning User. Anyway, that allowed you to introduce the mechanism with teams, that also comprise the default business unit, which I wasn’t aware of. Great post
Frederic, you’re right about the BU reference. Actually since CRM 4.0 didn’t offer the “equals current business unit” option I’ve always used the reference to the user entity under the BU when building such views. Let’s hope that the next version will continue to expand the options available in Advanced Find.
Here is a trixy one related to approach above:
Using the manager field on User, it is easy to create i.e. “Accounts in my Team” view (defined as all accounts owned by me or other owners with same manager).
BUT the manager herself can’t use this view, since you can’t put yourself as manager. Any thoughts on this?
/Henrik
For your example of the product type, can you not just do a grouped or?
Joe, the OR grouping works in scenarios where it’s enough for any single value to be found from a related entity. In fact, if it’s the type of a field that allows you to directly select multiple possible values, like the Product Type option set is, there’s not even a need for the grouping at all.
However, if you would try to build an AND group to search for records that meet more than one criteria at a time, this wouldn’t work directly as no line item could have several Product Type values simultaneously. For these purposes the example shown in the post provides a workaround to enforce the existence of several related records for the parent record being queried.
Does CRM 2011 Online allow the NOT IN search functionality for advanced find?
Mike, the support for Not In queries remains the most requested feature for Microsoft Dynamics CRM. We’ll need to wait for at least the upcoming Orion release planned for this fall to see if enhancements have been made in this area.
I have account and group account in account table, I want to create advance find where contact.parentaccount = account.accountname. How can I create this advance find.
Thanks
Arvinder, I’m not quite sure I understand the scenario. If you build an Advanced Find query that outputs a list of contact records, then should their parent account contain a specific set of accounts or are you looking for records where the contact’s parent account’s parent account has some data?
In account table I have all parent accounts and their subsidiaries, if the field “parent company” is empty then that is parent company otherwise it is subsidiary of the parent company name. I want if anyone open any parent account it should display contacts from parent account and all of it subsidiaries.
Thanks
Out of the box capabilities of Dynamics CRM views will not support this rollup feature for showing records from the lower branches of the hierarchy. It exists on some default views, mainly for rolling up the activities. I’ve discussed this in an earlier post on what’s the difference between subgrids and associated views.
You could always embed a report on the parent entity form and use it to query the child contacts from the account hierarchy in a recursive manner. Another interesting option that has been presented on the Dynamics CRM Team Blog post Deep queries for subgrids would be to modify the FetchXML of the view definition directly in the solution XML file. Neither of these options are exactly point’n’click configuration work, though, so it helps to have a CRM developer around to implement the requested feature.
Regrading the Business Unit/Team members query, is there a way to get the records of not just all records of my Team Members / BU members, but also all the Child BU members?
Thanks!
Sudhir, unfortunately such a query does not appear to be possible in Advanced Find. You can’t access the self-referential relationship between child-parent BU’s. Even if you could, this would limit the query results to the direct children of the parent BU as well as excluding records owned by users in the current user’s BU. Building custom teams to gather all the required users into a single team that can be referenced in AF would be the workaround to use in this case.
I am working with CRM-system for a car company. We have contacts related to vehicles via connections. Some contacts have inactive connections which means they are previous owner of a vehicle. How can I get hold of (VIP) contacts with both an active connection AND in-active connection?
Daniel, I tested the following kind of Advanced Find criteria and it appeared to retrieve the correct results:
Thanks Jukka! That’s solved the problem! Genious of you to back loop like that! Thanks again!!
Hi again Jukka. I have another tricky question: How can query regarding number of sub-entities (or in this case number of connections)? We have Contacts and Accounts with many active connections to Vehicles. How can I query e.g. “give me all accounts with more than 10 connections to vehicles)? Best Regards Daniel
You can’t count records within Advanced Find. To be able to reference the count of records in your query criteria, you’d need to have this value stored in a field on the parent record, from the creation/deletion event of the connection, by either a plugin or a scheduled Windows service (workflows might do the job as well, although they’d be less reliable in covering all the possible changes affecting the record count).
Ok, that what I thought. I’m not able to customize but I can find a un-used field and do it via a workflow. Thanks for your quick reply and really useful blogg =)
Hi, I am trying to use the advanced find to create a list of all contacts who are attached to accounts which have a particular industry and sub-industry, but it is returning zero results (even though we have lots of contacts with this criteria). What am I doing wrong?
Status = Active
Accounts (Primary Contact)
Industry equals Public Sector
Sub Industry equals Education
Melanie, the relationship “Accounts (Primary Contact)” is what links a single contact to one or more (1:N) accounts as the main contact person, i.e. in this relationship the contact is the parent record and the account the child. What you’ll want to use instead is the relationship that links the contact to the parent account (N:1). Depending on the version and customizations of your CRM environment, this relationship could be named “Parent Customer (Account)”, “Company Name (Account)” or something similar to that. After changing the relationship I bet the Advanced Find query will deliver results, as the rest of the criteria looks correct.
Hi Jukka, thank you so much. That worked for me now – the entity was called “Organisation (Account)” on our system. Thanks again!!
Hi again Jukka. Do you know if an ordinary user can create there own “associated views” via advanced find in dynamics CRM 2011? Is there any way of doing that or do I need to order that a system (associated) view? BR Daniel
Daniel, there is only a single associated view for an entity that applies on a global level to the whole organization. While the user can change the views in subgrids or associated views, they cannot change the default associated view, nor can you target this to specific user groups. The same applies for other special views, such as the Quick Find View. A system administrator will need to perform the changes to these views via the customization menu.
The limitations in targeting specific views to different user roles is an important consideration when planning how to support different business lines or units within the single CRM organization. While entity forms can be role based, a large share of the users’ interaction with data happens via views, which don’t have the same capabilities.
Thanks, this blog helped me a lot!
How would I write an Advanced Find to return only Accounts where ALL related Opportunities have a status of Lost.
Joe, first create a marketing list of all accounts that have a lost opportunity associated to them. Then use the “remove members” feature to take out all accounts that have a won or open opportunity from that list.
Hi Jukka,
Do you have any idea how we could build a marketing list of leads who have connections to multiple sectors (excluding people who have a connection to only one sector)? Thanks!
Chris, that’s actually the same kind of question that Daniel presented a few comments earlier. No, setting a criteria on the count of records in Advanced Find is not currently possible. However, with the upcoming CRM 2015 version there will be a new Roll Up Fields feature that allows you to store the count of child records onto the parent record. Assuming this will also support the connections entity, you could then create a custom field called “Connections Count” on the lead entity and build your Advanced Find criteria using this field.
Hi Jukka. Your blog is really helpful.
Do you have any idea how to set default product view per CRM team?
I want all salespersons to see all products, but when they go to Sales-> Products or in a lookup view in opportunities/quote lines, they see a different view depending the team in which they are members. Is there any way to do it through CRM Teams?
MB, since the Product entity is of the organization owned type in CRM, you’d need to set up some related user owned entity alongside it in order to control record visibility in product views. For example, create a “Product Team” entity and associate it N:1 to the standard Product entity. Assigning these custom entity records to the required teams should allow defining the query criteria in Advanced Find. I haven’t tested this myself, but I’d image the filtering logic should be applicable here.
Hi Jukka – thanks so much for this – really useful.
I’m now trying to do something similar by creating a dynamic Marketing List whose members are anyone who is in both/all of selected other Marketing Lists. But a query like the above (eg Marketing Lists > Marketing List = List X > Contacts > Marketing Lists > Marketing List = List Y) returns all and only members of List X.
What am I missing?
I’ve also tried creating a static list, adding List X and trying to remove List Y, but the absence of ‘not in’ as you mention makes that impossible, and I can’t work out how I’d use the temporary marketing lists idea, given it’s marketing lists I’m working on…
Would very much appreciate your thoughts!
James, this is an interesting question and the results given by Advanced Find are indeed a bit surprising. I tested this by adding contacts A & B to List X and only contact A to List Y. If I build a query criteria that first looks at contacts related to a marketing list that has the name “List X” and then continue with another loop back to the contact entity and add a further criteria for “list name equals List Y” then I get back both contacts. If, on the other hand, I do it the other way around and first query List Y, then only contact A is returned as a result.
I first suspected that this was caused by the fact that the relationship of contacts to marketing lists goes via an invisible intersect entity called listmember. However, replicating a similar query with “normal” entities produced similar results. I used account, contact and user (owner) entity to build a query with the same relationship structure as is underlying in the marketing list scenario. Unwanted records are returned in this query also, and I don’t know what exactly is the reason. I compared the Fetch XML query built in these two scenarios and they appear to be identical in their structure, so marketing lists may not be an exception after all.
In the marketing list query it seems that the second criteria (List Y membership) is ignored when filtering the result set. However, if you just set the criteria as “marketing list name = foo”, this will not produce any results. So, there must be some flaw in the presented query logic when applied to this scenario, but right now I can’t figure out how it should be implemented – or if it can be done via Advanced Find at all.
Thanks Jukka – at least I know it’s not just me… I’ll keep playing.
Hi folks,
Just adding to James’ query above. I ran into the same problem; I had:
Account -> Tag -> Account -> Tag (Account:Tag is an N:N relationship)
The first query resulted in an effective “OR” statement, showing all Accounts related to both Tags (“A” and “B”, respectively, for testing purposes). I then reversed the query to “B” and “A” and retrieved the desired result.
I have no idea why. We launch in 5 days so I don’t have time to investigate, but thought I’d post here as food for thought.
i was trying “Multiple Conditions for Related Child Record” on N:N relationship and it doesn’t work. is there any trick that i missed ?
Chadi, I suspect that this is a similar issue as the one discovered by James a few comments earlier in the Marketing List member scenario. Many-to-many relationships don’t seem to allow similar kinds of looping queries as 1:N relationships for determining matching criteria across several different records.
is it possible to apply the multiple conditions for related child record in an OR situation? (where one or the other of the child entities exists, rather than both?) or is there another way to build an AF like that?
Jess, there currently isn’t a way to apply “OR” conditions across related entities in Advanced Find. Please look up a suggestion on Microsoft Connect or post a new one and link back to it here, so we can all vote on it to possible be included as a feature for the Dynamics CRM product roadmap.
Added the System field called Education [educationcode] on the contact form.
Education field which is an optionset with values such as Student, Bachelors, Masters etc..
But this field doesn’t show up in Advanced Find… any idea/comment?
Akshay, I bet you have the field’s Searchable property set to “No”. Change that field in the attribute properties to “Yes”, publish the changes and try to build the Advanced Find query again.
Hi Jukka. We are using 2011 and I got the most simple question from a user. We are generating activities for sales persons about following customers. Some of them acctually doesn’t want to contact the customer the same day which I understand. So they don’t want to see the ones created today.
How can I solve this? I was looking for “Older than X days” but could only find “Older than X months”.
Can you help out with a filter that removes the one day old? “Older than 1 day” filter?
Daniel, these feature has only recently been added to Dynamics CRM Online 2015 Update 1, see this article by Mitch Milam. Once you’ll upgrade to CRM 2016 (or whatever the next on-premises major version will be), then you’ll be able to build such views in CRM. Nothing to be done before that, as far as I’m aware.
We just posted a super simple solution for creating not in queries in CRM using native Advanced Find finctionality that takes advantage of an otherwise useless filter. One down lots to go. http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find
Hello Jukka – I’m trying to use your first example where you use Business Units except with Teams (as you describe as possible. However, I don’t have a related Users(Teams) entity to choose from. I have a generic Teams but that doesn’t seem to work. What I want are CRM Cases that are owned my individuals that are part of the same Team(s) as me. Thoughts?
Hi Jukka,
I need your Inputs on below requirement:
On Lead & opportunities I have a lookup field created to Link to User record. We use this new Owner field along with CRM OOB Owner field on both of these entities.
We need to create views…where filter is done both of these fields…were equal to current user or His teams.
We created few sales teams and added groups of members to them. Instead of creating different views for each team hard coding the Team Name in advance find query…how can we write a generic query as you mentioned in the above example of the Blog.
Your inputs would be very helpful to provide a Generic solution , instead of cluttering CRM with same views again and again by selecting each team.
Req in single line : If user check the view he should be able to see all leads which he is owner and also leads of Users from the team he is part of. Owner = Equal current user or his team is not working….as security model is setup as manager Hierarchy (I am not sure if this is related)
Thank you in advance, waiting for your Inputs.
Santhi, if you’re using a condition like “current user or his team”, then this would return only the results where the owners is either the user or a team records in which he is a member. Having another user from that team as the owner wouldn’t work with these standard Advanced Find filters, if I’ve understood them correctly.
Have you tried building the criteria shown in the “Referencing the Current User” section, where you would reference the team entity instead of the business unit? This should specifically address those scenarios where the owners are always user records and they are connected via a membership in one or more same teams.
[…] of my posts that seems to remain in high demand, based on the site visitor analytics, is Advanced Queries with Advanced Find. Written over two years ago, people still tend to read it far more often than the newer posts […]
Hi Jukka and people following this interesting blog post …
I’ve been following the post since its original creation, and have learned a lot of my creative advanced find capabilities here. But now I struggle with this puzzle, and have given up:
I want to find all contacts working on accounts with online solutions having specific solution modules A,B,D,E,F, but not solution module C.
Have a look at this drawing: http://1drv.ms/1HohQAg
My problem is to exclude the solutions where solution module C appears. It’s classic: my advanced find is true if a solution module A and C exists on the solution, and then the advanced find does not work as wished.
Any clues to the puzzle would be appreciated 🙂
/Henrik
I am looking for a way to build an advanced find to show Contacts on Marketing List A but not on Marketing List B. I do not want to use the marketing list function to add/remove members, I need it to be in personal view format. Any suggestions would be appreciated
Nicole, the only way to build “Not In” queries in Dynamics CRM is via manually defining an outer join in the FetchXML. You’ll find some details in the SDK about this, since it’s not something you can define via Advanced Find’s graphical user interface. Doing a search for “Dynamics CRM” “outer join” will give you blog posts about how these are defined in practice. Now, knowing that the relationship to a marketing list is more complex than a direct relationship between two entities (like “accounts without child contacts”) I’m not 100% sure if the FetchXML outer join will work in this scenario, but it’s wort trying anyway. Do also note that these views which have their FetchXML directly modified cannot be later on edited in Advanced Find, so it won’t be very flexible if you need to frequently change the query criteria definition for the marketing lists to reference, for example.
Hey, Legend !!! We’ve been unable to create an advanced find with multiple criteria for a few years now. Hearing the question again today in the office I made 1 last ditched google and found your post. I can’t believe it was quite a simple answer.
Thanks
Awesome. Thanks a lot!!!
I tried the suggested approach for applying multiple criteria on the same child entity when I wanted to return all Contacts who are members of 2 specific Marketing Lists, so I set up an Advanced Find like this:
Contacts
Marketing Lists
Marketing List = List A
Contacts
Marketing Lists
Marketing List = List B
However, this just seems to return Contacts in List A, regardless of whether they appear in List B as well or not. Am I missing something?
Ian, I believe you’re not missing anything but rather just running into the same issue mentioned in the comments earlier. The query strategy demonstrated in the article doesn’t appear to be applicable for many of the marketing list related scenarios.
Is there any way to display columns from all those related entities?
Costin, not sure what you’re specifically referring to, but the standard rules of the Advanced Find UI are: view columns can be shown from current or directly parental entities.
Hi Jukka, very nice post….
I have a situation where I need to create a view that would pull records by the following criteria:
Either current user created it (created by)
Or current user is the owner of the record (owner)
Or current user is connected to the record (via related Connections entity)
I don’t know if this is possible to join all of them including related entity with OR clause, but I thought I’d ask you just to be sure.
TIA,
-Tony.
Tony, the use of a related entity is not supported in an Advanced Find query criteria with OR statements, so the last part of your criteria cannot be combined with the first 2.
Hi all – I’m somewhat of a novice when it comes to working with Dynamics CRM – 99% self taught by trial & error. I’m faced with an issue where we have two query categories that we use frequently – work orders & cases. I’d like to setup a view that shows both cases meeting certain criteria & work orders that display certain criteria – in the same list. Is that even possible. Any help is appreciated
Theodore, the views will always show records from a single entity only. The only exception being activities, but even there the data is actually pulled from a single activitypointer entity. As a workaround, you could always create a dashboard that would show one view of Work Orders and another view of Cases side by side.
Where have you been. I have been trying to resolve this issue for a while now and I stumbled across your blog after reviewing this for another project. Thank you so much for sharing this post as it has helped me so much,
First of all.. A Big thank you to you for sharing this information.
Currently I am working on a problem:-
I have created a view for Quote Entity ..
Now the requirement is that when a manager logs in to CRM and see this view he should see all his created records as well as his team members records(there are 2 teams associated with every user).
But when a user logs in and if he is not a manager then he should only see his records.
Can you guide me how is this possible?
I have a manager field in related entity too but how can i use?
Kuldeep, you cannot build any conditional “if-then-else” logic into the query criteria of a view in Advanced Find. It sound to me like creating two separate views is going to be needed here. Unless you can restrict the visibilty of the data via security roles, of course.
Thank you for sharing this information.It is indeed helpful.
I am working on a problem where i need to show Quotes according to two different scenarios:-
(1)When a manager logs in to CRM then he should see his team members Quotes as well as his Quotes.
(2) when a team member who is not the manager then he should only see his Quotes.
Can you describe me how can i do this ?
Pooja, see my response to Kuldeep’s question.