A guide to help you determine the best solutions for you.
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:
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:
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.
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.
In this example, we’re returning the number of opportunities associated to each account.
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!