Back when Microsoft released CRM 2013, they introduced a new feature to allow outer joins in FetchXML . You can find more details in this blog post about Left Joins in CRM 2013. The annoying thing is that this feature has not been surfaced through the Query designer for advanced find within CRM. It’s not a big issue, as most scenarios for this type of joins are intended for reports and you don’t use the designer for that.
However, I’ve come across a number of scenarios where we wanted to define Dynamic Marketing Lists with queries that required this type of functionality – e.g. accounts with no activities in the last x weeks. The good news is that you can use the APIs to manipulate the Dynamic Marketing Lists definitions and change its FetchXML programmatically. So, even if the Advanced Find designer doesn’t support outer joins, you can still modify the query programmatically and the Marketing List will function as expected.
Obviously, doing this requires a bit of effort and some code which makes it impractical. A couple of years back I decided to create a tool to handle this, and the obvious choice was to use the fantastic XrmToolBox framework and create a plugin there. So the Marketing List Manager was born. This tool allows to easily manipulate any Dynamic Marketing List query and modify the FetchXML definition.
As a bonus, I’ve also added another piece of functionality that I was missing in the CRM UI. The ability to get an accurate count of the number of members on the marketing list. This tool will also allow you to check the number of records included on the list as a result of using the query.
I’ve finally found some time to revisit this tool, and I’ve just updated the plugin to support the latest version of the XrmToolBox and its fantastic Plugin Store mechanism. So, if you open your XrmToolBox you should be able to get this new tool.
Hope it helps. Please feel free to leave any suggestions here or on the GitHub project.