Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Perform a Left Join Using FetchXML and Display Results in CRM 2013

Post Author: Joe D365 | November 7th, 2013

As we've covered pretty extensively on the blog lately, Microsoft Dynamics CRM 2013 has a ton of new great features. One of the things we haven't covered yet is the new ability to perform left outer join queries using FetchXML. So let's dive in!

Below you'll see an account record that doesn't have any cases.

Left Join Using FetchXML

Account with no associated cases.

To query for accounts that don't have cases, you can use the following FetchXML query:

Left outer join query between accounts and cases (incident).

The two main pieces to specify are the link-type, which is part of the link-entity, and the operator of the condition of a filter. Set the link-type to outer and the operator to null.

Below is a screenshot of the results when the RetrieveMultiple request is executed.

Result from executing left join.

In order to use this left join inside CRM 2013, use the SavedQuery entity to create a view.

Use SavedQuery to create the view.

Once executed, you can now see this left join view.

View of accounts with no cases.

There you have it—that's how to perform a left join using FetchXML and display results in  Microsoft Dynamics CRM 2013!

Was this helpful? Keep checking back for more blogs covering Dynamics CRM 2013 features, and check out our events page for upcoming CRM 2013 educational webinars.

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

9 comments on “Perform a Left Join Using FetchXML and Display Results in CRM 2013”

  1. Brilliant! Love these posts, thanks for doing them. Curious - does this work with CRM Online? I suspect I'd have to do this in BIDS or something to that end, and then bring back into CRM as a report?

  2. Please please please tell me this is possible in CRM2011 Online! Let me know Joe!

  3. Thanks for the FetchXML guide! This approach also works well by exporting a System View and modifying the View FetchXML node in the customizations.xml file, rezipping, and reimporting. Easier from a code standpoint, assuming a System View is acceptable instead of a Personal View/SavedQuery.

  4. Is there a reason this type of view is not able to be selected as a dashboard list component?

    1. I think it's a bug. You can work around it by doing the following:

      1. Create your dashboard
      2. Create the view as a system view
      3. Add it to your Dashboard
      4. Add it to a new solution
      5. Export the solution
      6. Unzip the solution ZIP file
      7. Unzip the customizations.xml file and modify the FetchXML node for the view to include the left join and null operators as described above
      8. Re-zip the solution and reimport the solution
      9. Check your dashboard, the left join/null operation should be included in the dashboard now

      Lots of messing around, but it has worked for me in a CRM Online environment. Note that you won't be able to modify columns for a system view that you modify in this manner and once you remove it from the dashboard you won't be able to add it back in due to the bug described.

  5. @joenewstrom:disqus An even easier way that is now available is to use the FetchXML Builder for XrmToolBox http://fxb.xrmtoolbox.com where you can open an existing view, use the UI to alter the query as desired, and then write back the updated query to the view! 🙂

  7. Hi,

    I tried the fetchXML provided above, but i am getting the Cases who has the Spares. PFB XML.

    Please help me to get proper results. Thanks in advance.

    Regards,
    Surendra

PowerObjects Recommends