Conditional groups across different linked entities in FetchXML

In CDS, the Advanced find gives us a great tool for generating FetchXML files, but even it has some limitations that can be worked around. Today we will focus on more complex FetchXML queries, that require logical OR or AND groups that depend on fields that are not on a single entity.

Let’s look at an example: We can query for a contact with the surname “Pečená” easily using the Advanced find like so:

And the result of the generated FetchXML (using XrmToolBox FetchXml Tester) give us the correct contact we have been looking for:

After that, we can also query for every contact with surname “Pečená”, which belong to users with the surname Prokop.

This doesn’t return any contact, because the way the FetchXML is generated by CDS is sequential, meaning that it first looks for Pečená, and then filter the subset for the owner with required surname. But what if we want to get Pečená OR Prokop Owner? When selecting both of those filter conditions and pressing the Group OR button, CDS pops up with the following message:

According to the error message, the group button will only work on conditions that are under the same entity, and that just won’t do. Let’s change the FetchXML text ourselves, so we can use these groups  even on filters from different entities:

Here, you can see the final FetchXML. The highlighted area shows us that we can download linked entities before we do any filters on them. The way CDS generates it is first generating linked entities and running filters in the same XML tag. The way we do it here is get information on the linked entities separately (This will work with multiple levels of linking, you can get linked entities of linked entities in these XML tags) from the filters. After we have linked all the necessary information, we can reference it with the “entityname” attribute, which will be the same as the “alias” we have chosen when linking. When left empty, the entityname will point to the entity returned by the FetchXML, in this case contact. When executing this XML, we get the following result:

As you can see, we got both the contact ending with “Pečená” and the contact whose owners’ surname is “Prokop”, meaning our OR group worked.

To recap, you want to separate the link-entity tags away from the filter itself, allowing you to reference multiple different entities in the same filter, grouping them as necessary.