Power Pages FetchXML List Filters, Link-Entities limitations, and a Power Fx column workaround

A while ago, I wrote an article on filtering Power Pages lists by values on related entities using FetchXML filters. That approach works well, but a recent question from a community member highlighted an important limitation worth mentioning.

The Limitation: Only One Level of Link-Entity

This community member was following my previous post mentioned above, and he was building a list based on a table with multiple levels of relationships. He was able to populate a dropdown correctly with values from related tables, but when applying the filter, nothing happened.

After reviewing his FetchXML filter, the root cause became clear:

Power Pages list filters using FetchXML with dynamic data only support link-entity filters one level deep.

That means you can filter by a related entity, but if you chain further relationships (linking through 2 or more levels), the filter is ignored.

His test confirmed this, when reduced to a single link-entity level, the filter worked perfectly.

A Workaround: Using Formula Columns (Power Fx)

So what can you do if you really need to filter based on a deeper relationship?

One practical workaround is to use Formula columns (Power Fx) in Dataverse.

Here’s how the pattern works:

1. Add a Formula column to your base table.
2. In that Formula, bring over the value you need from the related table, even if it’s multiple relationships away.
3. Once that value is exposed as a column on the base table, you can use it in your list filters directly, without requiring multiple levels of FetchXML.

Practical example

Following the example from my previous post, let’s say you have a lookup field in the Contacts table called Home City, linked to a Cities table, where each city also has another lookup field Country, linked to a Countries table.

Add this as FetchXML filter at the end of the previous sample:

 <link-entity name="contact" from="contactid" to="pnp_portaluser">
      <link-entity name="pnp_city" from="pnp_cityid" to="pnp_homecity">
        <link-entity name="pnp_country" from="pnp_countryid" to="pnp_country">
          <filter adx:uiinputtype="dynamic">
            <condition attribute="pnp_countryid" operator="eq"value="" adx:uiinputtype="dynamic"  />
          </filter>
        </link-entity>
      </link-entity>
    </link-entity>

And also replace the previous list JavaScript code with this new version (handling 2 custom filters) to customise the filters (remember to enable the table on the Web API and give permissions to the user):

$(document).ready(function () {
    const customFilterIndex = 2;
    const customCountryFilterIndex = 3;

     // Rename the label to "Company name" and "Headquarters Country"   
    $(`.entitylist-filter-option-group-label[data-filter-id="${customFilterIndex}"]`).text("Company Name");   
    $(`.entitylist-filter-option-group-label[data-filter-id="${customCountryFilterIndex}"]`).text("Country");   
    // Fetch accounts using webapi.safeAjax
    webapi.safeAjax({
        type: "GET",
        url: "/_api/accounts?$select=accountid,name",
        contentType: "application/json",
        headers: {
            "Prefer": "odata.include-annotations=*"
        },
        success: function (data, textStatus, xhr) {
            var results = data.value; // Extract the value array from the API response

            // Call the function to populate the dropdown, passing the name attribute dynamically
            populateDropdown(results, customFilterIndex,"accountid","name");
        },
        error: function (xhr, textStatus, errorThrown) {
            console.error("Error fetching accounts:", xhr);
        }
    });
     // Fetch accounts using webapi.safeAjax
    webapi.safeAjax({
        type: "GET",
        url: "/_api/pnp_countries?$select=pnp_countryid,pnp_countryname",
        contentType: "application/json",
        headers: {
            "Prefer": "odata.include-annotations=*"
        },
        success: function (data, textStatus, xhr) {
            var results = data.value; // Extract the value array from the API response

            // Call the function to populate the dropdown, passing the name attribute dynamically
            populateDropdown(results, customCountryFilterIndex,"pnp_countryid","pnp_countryname");
        },
        error: function (xhr, textStatus, errorThrown) {
            console.error("Error fetching accounts:", xhr);
        }
    });

    // Function to populate the dropdown
    function populateDropdown(items, filterId,idField,nameField) {       
        const dropdown = $(`<select class="form-control" name="${filterId}" id="dropdown_${filterId}"></select>`);

        // Add a default empty option
        dropdown.append('<option value="" label=" "> </option>');

        // Loop through the accounts and add options to the dropdown
        items.forEach(item => {
            const itemId = item[idField]; 
            const itemName = item[nameField]; 
            // Create the option element dynamically
            const option = `
                <option value="{${itemId}}" label="${itemName}">
                    ${itemName}
                </option>`;
            dropdown.append(option);
        });

        // // Add the dropdown to the DOM       
        $(`li.entitylist-filter-option-group label[data-filter-id="${filterId}"]`)
            .siblings('ul')
            .replaceWith(`
                <div class="input-group entitylist-filter-option-text">
                    <span class="input-group-addon"><span class="fa fa-filter" aria-hidden="true"></span></span>
                </div>
            `);

        //Append the dropdown inside the input group
        $(`li.entitylist-filter-option-group label[data-filter-id="${filterId}"]`)
            .siblings('.input-group')
            .append(dropdown);
            
    }

});

It won’t be filtered correctly, unfortunately. Filtering by Ireland, we needed to see the tickets related to people who live in Ireland, but we are seeing Detroit:

Formula field to the rescue

To overcome this limitation, a simple approach would be to leverage a Power Fx formula field that lists the Country ID of the contact directly in the Support Ticket table, as below:

Note that we are converting the ID value to text, setting it to lowercase and wrapping it in brackets. This is to keep the filter consistent, since the Text formula will convert the GUID to uppercase, and our previous custom filter code uses brackets around the ID to filter (since it’s the standard for lookup filters).

We can then use a simple FetchXML filter directly on the main table like this:

 <filter  adx:uiinputtype="dynamic" type="and">
      <condition attribute="pnp_portalusercountryid" operator="eq" value=""  adx:uiinputtype="dynamic" />
    </filter>

And the custom filter will work as magic:

Conclusion

List filters in Power Pages only work for one level of link-entity. If you go deeper, the filter won’t apply, even if FetchXML works in other tools like FetchXML Builder. But, formula columns with Power Fx are a powerful way to overcome this limitation, by surfacing related values directly on the base table.

References

Work with Formula Columns – Microsoft Learn

The post Power Pages FetchXML List Filters, Link-Entities limitations, and a Power Fx column workaround appeared first on michelcarlo.

Original Post https://michelcarlo.com/2025/08/23/power-pages-list-filters-link-entities-limitations-and-a-power-fx-column-workaround/