Implementing lookup filters in forms

Implementing lookup filters in forms within Power Pages can significantly enhance user experience by allowing users to select values from related records without displaying irrelevant options. Lookup filters ensure that only appropriate records are shown for selection, improving data integrity and reducing errors. Here’s a step-by-step guide to implementing lookup filters in forms:

1. Understanding Lookup Fields in Power Pages

Lookup fields in Power Pages are used to create relationships between two entities. For example, in a form, you may have a lookup field that allows the user to select a related record from another table, such as selecting an Account for a Contact.

By default, lookup fields display all records from the related table, which may not always be desirable. Lookup filters allow you to restrict the records available in the lookup field based on certain criteria.

2. Use Case Example

Let’s say you have two tables in Dataverse:

• Account (contains information about companies)
• Contact (contains information about individuals associated with accounts)

You want to create a Contact form where users can select an Account, but only the accounts that are marked as “Active” should be available in the lookup field.

3. Configuring Lookup Filters in Dataverse (Backend)

Lookup filters can be configured within the Dataverse environment (Power Apps). These filters are applied to lookup fields at the field level. You can create filters based on field values or other criteria.

Step 1: Create a Lookup Field

If you haven’t already, create a lookup field in your form. This field should reference the related table (Account in this case).

1. Go to Power Apps and open the Contact entity.
2. Under Fields, create a new field of type Lookup and set its target to the Account table.
3. Save and publish your changes.

Step 2: Add a Lookup Filter

1. In the Contact entity, open the lookup field (e.g., Account).
2. Under Field Properties, click on Lookup Filter.
3. Add a filter condition. For instance, to only show active accounts:
   • Select the Status field (or another field that represents the account’s status).
   • Set the filter condition to: Status equals Active.
4. Save and publish the changes.

This filter will now be applied automatically in the Contact form, ensuring that only active accounts appear in the lookup field.

4. Implementing Lookup Filters Using JavaScript (Frontend)

If you want more dynamic and custom filtering, you can use JavaScript to filter lookup values based on user input or other form fields. This method is particularly useful when you need to apply more complex filters that cannot be achieved through simple field conditions in the backend.

Step 1: Attach JavaScript to the Form

1. Navigate to the Contact form in Power Apps.
2. Under Form Settings, go to Events and select the OnLoad or OnChange event (depending on when you want the filter to be applied).
3. Add a new JavaScript function in the Library field.

Step 2: Write JavaScript to Filter the Lookup

Now, write a JavaScript function that applies the filter to the lookup field based on the value of another field or a custom condition.

Here’s an example of how you can filter the Account lookup field to only show records where the Status is “Active” and the Industry is “Technology”:

function filterAccountLookup(executionContext) {
    var formContext = executionContext.getFormContext();
    var statusValue = formContext.getAttribute("statuscode").getValue(); // Status field on the Contact form

    if (statusValue === 1) {  // Assuming 1 represents "Active" status
        var accountLookup = formContext.getControl("accountid");  // Account lookup field
        accountLookup.addPreSearch(function() {
            var filterXml = "<filter type='and'>" +
                            "<condition attribute='statuscode' operator='eq' value='1'/>" + // Active status filter
                            "<condition attribute='industrycode' operator='eq' value='100000000'/>" + // Technology industry filter (replace value with actual ID)
                            "</filter>";
            accountLookup.addCustomFilter(filterXml);
        });
    }
}

In this example:

• The script checks if the statuscode field on the Contact form is set to “Active”.
• If it is, the lookup field for Account (accountid) is filtered to only show accounts with an “Active” status and an “Industry” of “Technology”.
• The addPreSearch function is used to add the filter before the lookup field fetches data.

Step 3: Publish and Test

1. Save the JavaScript code in your web resource library.
2. Attach the JavaScript function to the form event (e.g., OnLoad).
3. Publish the form and test it. When you load the form, the lookup field for Account should now be filtered based on the criteria you set in the JavaScript.

5. Combining Backend and Frontend Filters

You can combine both methods for more advanced functionality. For example, you can apply basic filters in the backend using the Lookup Filter settings and enhance them with JavaScript for dynamic, context-dependent filtering.

6. Limitations of Lookup Filters

While lookup filters are powerful, there are some limitations:

• Complexity: Filters based on multiple fields or complex conditions are best handled with JavaScript.
• Performance: Adding multiple JavaScript functions for lookup filters can impact performance, especially for large datasets. Always test the form’s performance.
• Customization: Some customizations may not be possible using only the backend filter options. In these cases, JavaScript gives you greater flexibility.

7. Best Practices for Implementing Lookup Filters

• Use simple filters in the backend for common scenarios (e.g., filtering based on status).
• Use JavaScript for complex filtering that depends on dynamic user inputs or multiple conditions.
• Minimize the number of pre-search filters on lookup fields, as they can impact performance.
• Test thoroughly to ensure that the filters work as expected, especially when using JavaScript.