Performing Outer Join in Advanced Find

It is currently not possible to perform left outer join using Advanced Find, even though that capability exists in CRM2015. For e.g. if you want to get a list of contacts, that don’t have an invoice, you cannot do it using an ad-hoc Advanced Find.

This limitation is usually overcome by creating a system view that does a regular inner join, and then updating the view’s fetchxml using a tool like FXB or even editing the customisation file directly. There is also the rollup field approach described in http://crmtipoftheday.com/2015/04/27/not-in-for-the-rest-of-us/. But if you are going to do an ad-hoc advanced find query, you really don’t want to create one system view for each entity, for which you want to do an outer join.

I present to you a solution for this problem: AdvancedQueryOuterJoin

This is a plugin that runs on the pre-stage of RetrieveMultiple, and alters the fetchxml with the correct left outer join condition. This means you can do an Advanced Find Query like this one below.

OuterJoin

The above query is for Account, who have child contacts, but don’t have any opportunities. When the plugin sees a link entity in the query, with a null primary key condition, it replaces that condition with a left outer join on the parent entity. The code itself is very simple, and you can have a look at the github repo to understand how this works.

You can download the solution from https://github.com/rajyraman/AdvancedQueryOuterJoin/releases. Download the solution file that works best for your CRM instance i.e. 7.0 or 7.1. Please also use the github issues area, for entering details about any problems that you encounter.

EDIT (01/06/15): Using this plugin will not correct the fetchxml you download by clicking the Download Fetch XML in the Advanced Find window. If you are going to run your downloaded query in FXB, make sure you change the Output type to Grid.

FXB

If you use FetchResult, you won’t see any results, as the plugin would not have executed in the background.

EDIT (02/10/15): Refer to the newer post https://dreamingincrm.com/2015/09/14/bookmarklet-advanced-find-outer-join-a-k-a-not-in-condition/ for doing this using bookmarklet.

Advertisements

8 thoughts on “Performing Outer Join in Advanced Find

  1. Hi Natraj, thanks for this wonderful solution. Does it work in case of N:N, say for e.g. I want to get all the contacts which are not part of any marketing list.

    Thanks,
    Nishant Rana

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s