Saturday, April 13, 2013

Three Limitations of Using Advanced Find

I got asked a couple of days ago if I knew of a blog which listed the limitations of using an Advanced Find query. I knew of a couple of the limitations off of the top of my head but could not find a blog summarising them. So here it is.

Outer Joins

In my opinion, other than being able to do aggregate calculations, this is the biggest limitation of Advanced Finds. So what is an ‘Outer Join’?

Let us say we have two tables in a database, the Account and Contact table. Advanced Find allows to ask questions like ‘Show me all Accounts which have a Contact whose first name is John’ or ‘Show me all Contacts where their Account is in the Mining industry’. In these cases a record exists in both tables e.g. an Account record linked to a Contact record whose name is John. This is called an ‘Inner Join query’.

However, if we ask ‘Show me all Account with no Contacts’ we cannot do it. In other words, if we ask questions where there is a record in one table and none in the other table we will find it impossible with Advanced Find. This is an ‘Outer Join query’. Other examples are reports showing neglected leads (leads with no activity for six months) which cannot be done with Advanced Find.

In the case of Accounts, Contacts and Leads, we can get around the problem using a Marketing List. In the above example, we can add all Accounts to the Marketing List and then remove those with a Contact, leaving behind the desired list. For other types of records, the only option is to have a flag field to help us. For example, we can have a ‘Contact Flag’ field on the Account which is populated when there is an active Contact associated to the Account. We can then use this flag to return ‘All Accounts with an unticked Contact Flag’, satisfying our Outer Join query.

Titles

Titles are NOT restricted to just the search entity. For example, if we are searching for all Contacts in a certain industry, we can bring in fields from the Account entity, by dropping down the entity selection when adding columns.

image

However, we can only go one level up. So, for example, if we want to ask ‘Show me all Appointments regarding Opportunities where the Account is in the mining industry’, we can display Appointment fields as columns and Opportunity fields as columns but we cannot browse up to the Opportunity’s associated Account and show their fields. In this case, the only workaround available is to replicate the key fields from the Account onto the Opportunity and then reference these copied-fields.

Grouping Conditions Across Entities

Let us say we want to know ‘All Accounts where the Account is in Sydney OR the Account has a Contact in Sydney’

image

We can write the above query but this asks for Accounts which are in Sydney AND has a Contact in Sydney. Normally we would use the ‘Group OR’ button at the top but this can only apply to conditions within the same entity and therefore we cannot group them. In this case the best we can do is run the query twice (once for the Account rule and once for the Contact rule), export to Excel and combine manually.

Conclusions

Advanced Find is one of the most powerful and accessible features of Dynamics CRM and any site not making full use of this function is missing out. Even those sites using it every day may not discover the limitations listed above. However, now you are aware of them, if you do find yourself coming up against one of them, you have some workarounds or the opportunity to rethink to see if you can gain insight through an alternative, supported, query.

19 comments:

George said...

I'm surprised that the absence of NOT IN clause is not in your list (punt intended)

Rob Dawson said...

How about this one. Find records that have related child records of this (lookup) AND that (lookup). The only option we have, as we cannot add the same related entity again, is to select both in the one related lookup. Annoyingly this treats the selection as OR not AND.

Example.
Find Car records that have related Optional Extra records of Bling Wheels AND Fuzzy Dice.

Leon Tribe said...

George: Sub-selects would be nice, I agree :)

Leon Tribe said...

Rob: You're quite right, I have also come against this one in the past. If we had dynamic parameters, like with Dialogs, we could do it but this is not yet available for general Advanced Find queries.

DM said...

I wanted to create a view which was going to be used for a chart which showed all targets due in 2 weeks (2 weeks is dynamic, not a fix date). There is no way to do this.

The lack of dynamic fields is a weakness.

Leon Tribe said...

DM,

Can't you use the Next x weeks option for dates?

Leon Tribe said...

Hi Simeon,

There are lots of excellent materials online. I'd recommend reviewing CRM SDK, reviewing blogs such as Amreek Singh's and looking at YouTube.

DM said...

Hi Leon, what I meant is there is no way to have a single dynamic future date. For example: return all targets where the date is 2 weeks in the future (but not 1 week in the future). The 'Next x weeks' will return all dates.

Leon Tribe said...

DM,

With Advanced Find, no. The simplest option is probably filtering a Dynamic Excel export. Another option may be a Dialog (which can query with dynamic values)

Dario said...

Hi Leon. By this way you can create just a static list and not a dynamic list. Is it true?
thanks

Leon Tribe said...

Hi Dario,

Yes you can create either a static list (fixed set of members) or a dynamic list (rules-based membership)

Unknown said...

I realize this is an older blog post, but I found it informative today. A related blog post from the customer effective blog shows a fairly simple xml editing (not OOTB, but easy enough) method to create the outer join and save it back to your advanced find for future use.
Dynamics CRM Mission Advanced Find Impossible?

Unknown said...

CRM Intelligent Query is a free solution that will allow you to do "Not In" queries directly in advanced find.
http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find

Unknown said...

I have been told that there is a limitation in what data can be returned in the results of an Advanced Find however I've not been able to confirm or refute it. It isn’t mentioned here as a limitation so I was wondering if you are able to confirm if in fact it is definitely possible to display data from fields in more than just one other related entity.

Leon Tribe said...

There is a limitation of grouping across multiple entities but you can definitely display columns from multiple entities, as long as the additional entities are parents to the primary one.

Unknown said...

Is there any way to solve the third limitation using JavaScript

Leon Tribe said...

My guess is you can group queries across entities in pure FETCH queries, if this is what you mean but I am not 100% sure. It may be possible to do two queries and then manipulate the results through code. Again, my coding knowledge is limited in this regard.

Anonymous said...

For Rob Dawson's (Find Car records that have related Optional Extra records of Bling Wheels AND Fuzzy Dice) you can cycle back through again and get it to work.

Car
Options
Option Type = Bling Wheels
Car
Options
Option Type = Fuzzy Dice

Anonymous said...

Lost the formatting. So once you filter for the first Option Type use "select" under Option Type and go to Related Entities and grab Car and filter down again.