Have you ever had the question, "How do I make a view of Accounts without Leads?" or "How do I make a view of Leads without Phone Calls?" or in general "How do I make a view of ______ without any _______?" FetchXML has had some limitations that made it difficult to answer this question, but recently Microsoft has allowed for advanced FetchXML features to grab a more diverse set of data that hasn't been available before. I'm referring specifically to the entityname attribute in a FilterCondition. This in conjunction with an OUTER join will allow query writers to filter data after it is joined into one table.
A little background on OUTER JOINS from Microsoft's documentation (link at end of article):
"A left outer join returns each row that satisfies the join of the first input with the second input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values.
You can perform a left outer join in FetchXML by using the entityname attribute as a condition operator. The entityname attribute is valid in conditions, filters, and nested filters."
Below is a short example from Microsoft's FetchXML Documentation. This are pulling All Accounts that have no Leads:
A couple things to note here, there is a link-entity Node between Accounts and Leads to pull Accounts and Leads. The use of the link-type='outer' makes the "JOIN" between the Account and Lead table retrieve ALL Accounts and ALL Leads. From there, we will filter the results.
The next important piece here is the filter. The filter is applied outside of the link-entity Node and references the results pulled in from that link-entity Node (in this case it is referred to as "leads"). From there, we want to filter the resulting Rows where a LeadId does not exist (operator-null). This pulls in all Rows (Accounts) where there is no LeadId that can populate that column
The reason this is different from filtering at the link-entity level, is because we are putting a filter on the link-entity clause. You can see that we are using the entityname attribute on the condition portion of the filter. This references the results of the link (or JOIN) and puts a filter on those results.
I have a visual example below for Accounts and Tasks using the Query Designer in SSRS. First without any type of Filter on the link-entity:
This is returning Accounts and Tasks, aligning them into rows if they match the relationship stated in the link-entity condition.
Now I will put in our entityname statement to gather Accounts without Tasks
I'll change the entityname filter to 'not-null' and you can see its affects:
You can even add filters on top of this. For a more practical example if I wanted to create the view "Accounts without Tasks in the last 7 Days," it would look like the query below:
Using a Filter inside of the Task link-entity, I am able to only pull Completed Tasks with a Due date within the last 7 days. In conjunction with the entityname attribute, I am only showing Account rows that do not have a Task that meets my filter criteria.
This new ability for CRM 2013 and CRM 2015 is an incredibly powerful enhancement to FetchXML. You can read more here about the entityname attribute and interesting uses for it in Microsoft's documentation.
Beringer is a Microsoft Gold Certified Partner that can solve complex problems like these for your business everyday. Please contact us to see how we can support your business.