Using FetchXML for complex queries

FetchXML is a proprietary query language used in Microsoft Dataverse and Dynamics 365 to retrieve data. In Power Pages, FetchXML is widely used for retrieving complex, filtered, and related data sets — especially when standard configurations like entity lists or views are not flexible enough.

This guide will walk you through everything you need to know about using FetchXML for complex queries in Power Pages, including syntax, integration methods, and use cases.

---

What is FetchXML?

FetchXML is an XML-based query language used to fetch data from Dataverse. It allows:

• Filtering on columns
• Sorting
• Grouping
• Aggregations
• Joins (link-entity)
• Paging
• Aliasing columns

FetchXML is more powerful than standard filters available in Entity Lists, making it perfect for complex conditions and related entity queries.

---

Use Cases in Power Pages

• Displaying filtered data based on user roles
• Fetching related records (e.g., contacts under an account)
• Generating nested views or lookups
• Dynamic charts using filtered datasets
• Custom data-driven widgets
• Portal forms prefilled with specific conditions

---

Step-by-Step: Using FetchXML in Power Pages

Step 1: Write a FetchXML Query

You can write FetchXML in tools like:

• FetchXML Builder (XrmToolBox plugin)
• Power Platform Dataverse Advanced Find (Download as FetchXML)
• Power Automate (when building flows)

Example 1: Simple FetchXML Query

<fetch top="10">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

Example 2: Fetch Related Records (Join)

<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <link-entity name="account" from="accountid" to="parentcustomerid" alias="ac">
      <attribute name="name" />
      <filter>
        <condition attribute="name" operator="eq" value="Contoso Ltd" />
      </filter>
    </link-entity>
  </entity>
</fetch>

---

Step 2: Add the FetchXML to a Web Template or Web File

1. Portal Management App → Web Templates
2. Create a new Web Template (e.g., custom-contact-list)
3. Inside the Web Template, use the liquid FetchXML tag:

{% fetchxml contact_list %}
<fetch top="5">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
  </entity>
</fetch>
{% endfetchxml %}

<ul>
  {% for contact in contact_list.results.entities %}
    <li>{{ contact.fullname }} - {{ contact.emailaddress1 }}</li>
  {% endfor %}
</ul>

This renders a simple list of contacts using Liquid and FetchXML.

---

Step 3: Display Output in a Web Page

• Go to Web Pages → Select the page you want
• In the Web Template field, reference the template you created
• Save and browse the site — the FetchXML query will execute and display data

---

Filtering Based on Logged-in User

You can dynamically pass the logged-in user’s data (contact, account, roles) to FetchXML.

Example: Filter Contact Records Based on Current User Account

{% assign current_user_account = user.parentcustomerid.id %}

{% fetchxml my_contacts %}
<fetch>
  <entity name="contact">
    <attribute name="fullname" />
    <filter>
      <condition attribute="parentcustomerid" operator="eq" value="{{ current_user_account }}" />
    </filter>
  </entity>
</fetch>
{% endfetchxml %}

<ul>
  {% for c in my_contacts.results.entities %}
    <li>{{ c.fullname }}</li>
  {% endfor %}
</ul>

---

Paging in FetchXML

FetchXML allows paging using attributes like:

<fetch page="1" count="10">

Useful for large data sets where only part of the result should load per request.

---

Aliases and Sorting

You can assign aliases and sort by field:

<fetch>
  <entity name="contact">
    <attribute name="fullname" alias="ContactName" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>

Then reference it in Liquid like:

{{ contact.ContactName }}

---

Common Conditions

Operator	Usage
eq	Equal
ne	Not equal
like	Wildcard match
in	List of values
null	Value is null
not-null	Value is not null
gt/lt	Greater than / less than
on	Date is exactly on
last-x-days	Relative date filter

---

Best Practices

Tip	Reason
Use FetchXML Builder	Easier visual query design
Test FetchXML first	Validate query output
Don’t fetch more than needed	Performance issues
Secure with web roles	Protect sensitive data
Avoid writing raw XML in HTML views	Use Web Templates
Cache where possible	Avoid re-executing complex queries

---

Limitations

• FetchXML has no support for nested link-entity beyond two levels
• Cannot query external data sources (only Dataverse)
• FetchXML in Web Templates has limited error debugging
• Cannot perform write/update — it’s read-only