The Microsoft Business Applications Practice at HCLTech is thrilled to be at HIMSS23 in April. That’s right, we’re back in person and excited to be one of the select partners to share a booth with Microsoft.
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.
To query for accounts that don't have cases, you can use the following FetchXML query:
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.
In order to use this left join inside CRM 2013, use the SavedQuery entity to create a view.
Once executed, you can now see this left join view.
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.
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?
Please please please tell me this is possible in CRM2011 Online! Let me know Joe!
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.
Is there a reason this type of view is not able to be selected as a dashboard list component?
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.
@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! 🙂
We created a solution that enables the user to preform left outer joins, allowing to do "Not In" queries, without ever having to touch the fetch xml.
Ya, but unfortunately it kills the CRM Mobile App. Any update on this?
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.