Share

Understanding FetchXML Outer Joins in Dynamics CRM

I’m often asked about Outer joins and there seems to be a little bit of confusion about them. As you can see from prior posts, I’m a big fan of using FetchXML to access data from Microsoft CRM. So today, I wanted to go over the outer join feature and show you an example of where I recently used one.

Our First Example

To get started, let me show you where I recently used an outer join and why:

image
This is a widget on a dashboard I recently put together that shows the user’s associated contacts. The images can come from three different sources: LinkedIn, Facebook, or CRM. In my case, the CRM image is an attachment on the contact.  (I know most of you are thinking that Edward Anderson is one good looking dude, but let’s focus on the Fetch)

Let’s take a look at the Outer Join:

image
Notice, at the end of line 20 you’ll see the “link-type” attribute. This specifies our outer join. If this was an inner join, then only contacts with a “Thumbnail” attachment would be returned. We want contacts with AND without a “Thumbnail” attachment which an outer join provides.

Another Example

With CRM 2011, Outer Joins also provide aggregate counts. For example, let’s say you want a list of accounts with the number of opportunities associated to each account. This is quite easily done with an outer join.

image
In this example, we’re returning the number of opportunities associated to each account.

In a case similar to this (slightly different fetch though), we displayed the results on an HTML web resource. We sorted ascendingly and then via JavaScript only showed accounts where the opportunity count was zero. We were displaying a widget to show all accounts without an opportunity.

Going Forward

When I first was programming with CRM, I mainly used Query Expression. Once I started using Fetch, I’ve never looked back. If you aren’t familiar with Fetch, I strongly recommend learning it as it’s my preferred method for accessing data from CRM. Outer joins and aggregates are just the tip of the iceberg.

In our case, we had two pretty basic examples; however, I hope these conjured up some ideas for some of the problems you are trying to solve in your environment. I hope you enjoy!

Free Guide
On-Premises vs. Cloud CRM

A guide to help you determine the best solutions for you.