Enhancing Dynamics 365 CE /PowerApps Fetch XML with OR Conditions between Linked Entities

FetchXML is a powerful query language used in Dynamics 365 Customer Engagement (CE) and PowerApps for retrieving data. One of the common requirements is to add complex filtering conditions, such as using OR conditions in linked entities. In this blog post, we'll explore how to modify a FetchXML query to include OR conditions in linked entities, making your data retrieval more flexible and efficient.

Understanding the Scenario

Consider a scenario where we need to fetch activities from Dynamics 365 CE and PowerApps and apply filters on related entities. Specifically, we want to filter activities based on a group attribute in two linked entities: contact and account. We need to use an OR condition to match the group attribute in either of the linked entities.

The FetchXML Query

Here's the FetchXML query with the OR condition applied to the linked entities:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
    <entity name="activitypointer">
        <attribute name="activitytypecode" />
        <attribute name="subject" />
        <attribute name="statecode" />
        <attribute name="prioritycode" />
        <attribute name="modifiedon" />
        <attribute name="activityid" />
        <attribute name="instancetypecode" />
        <attribute name="community" />
        <attribute name="regardingobjectid" />
        <order attribute="modifiedon" descending="false" />
        <link-entity name="contact" from="contactid" to="regardingobjectid" link-type="outer" alias="an">
            <link-entity name="xrm_borrowercustomnn" from="xrm_borrower" to="contactid" link-type="outer" alias="ao" />
        </link-entity>
        <link-entity name="account" from="accountid" to="regardingobjectid" link-type="outer" alias="ap">
            <link-entity name="xrm_borrowercustomnn" from="xrm_borrower" to="accountid" link-type="outer" alias="aq" />
        </link-entity>
        <filter type="or">
            <condition entityname="ao" attribute="xrm_group" operator="eq" value="{C907238B-1B14-EF11-9F89-000D3AF2E8B8}" />
            <condition entityname="aq" attribute="xrm_group" operator="eq" value="{C907238B-1B14-EF11-9F89-000D3AF2E8B8}" />
        </filter>
    </entity>
</fetch>

Explanation of the FetchXML Query

Let's break down the key parts of this FetchXML query:

• Entity: We are querying the activitypointer entity to fetch various activity attributes such as subject, statecode, prioritycode, and modifiedon.

• Linked Entities: We are linking to the contact and account entities using outer joins. These linked entities have a further link to a custom entity xrm_borrowercustomnn.

• Filter Condition: The filter element is of type or, meaning it will match any of the conditions inside it. The conditions check if the xrm_group attribute in either the contact or account entity matches the specified value.

Practical Usage

This FetchXML query can be used in various scenarios where complex filtering is required. For example, you might want to display activities related to specific groups in a dashboard or report. By using the OR condition, you ensure that activities linked to either a contact or account with the specified group are included in the results.

Conclusion

Adding OR conditions in linked entities within FetchXML queries can significantly enhance the flexibility of your data retrieval in Dynamics 365 CE and PowerApps. By understanding and applying these techniques, you can create more powerful and efficient queries tailored to your business needs.

We hope this guide has been helpful. If you have any questions or need further assistance, feel free to leave a comment below.