Filtering Power Pages lists by values on related entities using FetchXML filters

When using classic lists in Power Pages, we can select any view from a table to display in our portal, and this view can even be using values from related tables.

The problem

When creating a LookUp set filter on a list in Power Pages, we cannot use values from related tables, even if they are added to the table (as in the example below, we added the accounts that relate to the Portal User field in a ‘Support Tickets’ table):

The options when configuring a LookUp set only bring fields from the current table:

Similarly, for Dynamically LookUp sets, we can only use columns from the primary table.

Prerequisites

The configuration for the sample below is done on the Power Pages Management/Portal Management App.

The simpler solution: Replicate the lookup filter set with a Link-Entity on filters

Here is the trick, we can use a FetchXML filter, and use a link-entity to the accounts table to filter it.

In the example below we have 2 hardcoded filters for 2 different companies (this would be similar to the LookUp set filters we use in Power Pages):

FetchXML code to copy:

 <link-entity name="contact" from="contactid" to="pnp_portaluser" link-type="inner">
      <filter type="or" adx:uiname="Company Name">
        <condition attribute="parentcustomerid" operator="eq" value="5a2b5163-c34d-ef11-bfe2-0022488317e7" uiname="Fabrikam Global" uitype="account" />
        <condition attribute="parentcustomerid" operator="eq" value="2db20c7c-db49-ef11-bfe2-0022488317e7" uiname="Contoso Global" uitype="account" />
      </filter>
    </link-entity>

Each of the options you add as a condition to the filter will appear as a value to be selected. The uiname property dictates how the value labels are displayed, so you can change it to anything you want.

Unfortunately, even if we change the adx:uiname property of the filter it does not reflect on the UI. After saving the filter it will get the display name from the table and not the entity and that is not what we want:

To fix this, we need to add some JavaScript to this list to rename it.

Each filter added into a list gets a ‘name’ property which matches it’s index, So we’ll use it to find the label for it and rename it. For the label for a filter it matches it using the data-filter-id attribute, and for the control itself it matches it using the name attribute.

The filter index is zero index based, so if it’s your third filter in the list, the index will be 2, for example:

JavaScript code for the list JavaScript (add it under the options tab):

$(document).ready(function () {
    // Rename the label to "Company name"    
    $('.entitylist-filter-option-group-label[data-filter-id="2"]').text("Company Name");   
});

After saving the list, the new filter will appear with the updated label:

Results:

When applying the filter, it will filter correctly by the accounts of the contacts associated with the records:

Similarly, we could also use a similar approach for a dynamic filter (for example, automatically generating a dropdown based on the current accounts in Dataverse), so we don’t need to have values hardcoded in the Fetch Filter.

More advanced solution: Dynamic Filter

Instead of using the filter set with options on the Link-Entity filter, you can use a dynamic filter as below:

 <link-entity name="contact" from="contactid" to="pnp_portaluser" link-type="inner">
      <filter adx:uiinputtype="dynamic">
        <condition attribute="parentcustomerid" operator="eq" value=""  adx:uiinputtype="dynamic" />
      </filter>
    </link-entity>

Even though the reference to the inputtype values contains the adx reference, it still works fine on both standard and enhanced data model site types.

In this instance, you will need to inject a dropdown with the values manually, since the dynamic filter does not list any of the values. In the same way, add the JavaScript code sample below to get the accounts and generate a drop-down with them automatically (setting the filter index as a constant now to be used across more code pieces, change the value to your filter index when using this):

$(document).ready(function () {
    const customFilterIndex = 2;//Your filter index as explained on the other sample
     // Rename the label to "Company name"    
    $(`.entitylist-filter-option-group-label[data-filter-id="${customFilterIndex}"]`).text("Company Name");   
    // 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;
// Call the function to populate the dropdown, passing the name attribute dynamically
            populateDropdown(results, customFilterIndex);
        },
        error: function (xhr, textStatus, errorThrown) {
            console.error("Error fetching accounts:", xhr);
        }
    });
    
    function populateDropdown(accounts, filterId) {
        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
        accounts.forEach(account => {
            const accountId = account["accountid"]; // Account ID (GUID)
            const accountName = account["name"];   // Account Name (Text)
            // Create the option element dynamically
            const option = `
                <option value="{${accountId}}" label="${accountName}">
                    ${accountName}
                </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);
    }
});

Prerequisites: To run the code, you need to enable the accounts table for WebAPI and the user needs to have table permissions granted. And also the page where the list is located needs to have the Web API wrapper code added.

After the change is applied, you can see a new filter with all the accounts the user has access to:

When filtering by a company, the records display based on the contacts for that company as expected:

Conclusion

Using a FetchXML list filter with some JavaScript tricks, we can create filters with predefined values or dynamic filters based on related tables and overcome the limitation of using filters only on fields on the current table.

References

How to use the Portal Web API – Microsoft Learn

The post Filtering Power Pages lists by values on related entities using FetchXML filters appeared first on michelcarlo.

Original Post https://michelcarlo.com/2025/01/25/filtering-power-pages-lists-by-values-on-related-entities-using-fetchxml-filters/