Filtering Dynamics 365 Lookups Four Ways

The Dynamics 365 and Power Platform model driven apps lookup control has received some love recently in the form of the new Advanced Lookup feature, if you haven't seen it already check out my recent post about it here.

Coincidently I also had a requirement this week that meant I needed to do some customisation to a lookup field which comes up fairly frequently so I thought it worth sharing.

The requirement was to be able to filter a particular lookup so that the user could only select from certain valid records rather than from every record available.

So let‘s take a look at the options available when it comes to filtering lookups:

Set a Default View

If you only need to offer a filtered set of records that doesn't need to change dynamically, you can accomplish this quite easily through some simple configuration.

First you’re going to need to set up a system view that you can use within your lookup.

In the Power Platform maker portal find the table that your lookup looks up to, open the views tab and click the "Add View" button, in the dialog that opens give your new view a name and description and then click "Create".

The screen that opens is the view editor where you can add columns to your view and more importantly for this requirement, filters, which you add by clicking "Edit filters..." and building your required filter criteria.

Once you’re happy with your view you can save and publish it so it’s available for the next step.

With your view saved, back in the maker portal, head over to the form where your lookup resides. Click on the lookup control in the form so that the properties window for the lookup appears on the right hand side.

In the "Default View" drop down you can now select the view that you created in the previous step. Also, uncheck "Allow users to change view" to prevent users from changing the view and selecting an invalid record that way.

Now when users use the lookup, they will only be able to select records that are available from the filtered view you created.

Related Record Filtering

If a filtered static list of records doesn't satisfy your requirement, then there are a few other ways that you can dynamically filter a lookup, the simplest of which is related record filtering.

This feature allows you to filter records where the record you are looking up to is already related to the record you are looking up from. A good example of this is the Primary Contact lookup on the Account table, where you can only select Contacts where the Company Name matches the Account you are looking up from.

To enable this you need to access the classic form editor, so again in the maker portal open the form that your lookup resides on and click "Switch to Classic". Now, double click the lookup field you want to change and in the “Display” tab, scroll down to the “Related Records Filtering” section. Here you can define how you want to filter the records in your lookup.

Again if you want to enforce the filter, make sure you uncheck “Users can turn off filter“.

Dynamically Create a Custom View

If neither of these two options suffice then we need to make use of the Client API and some JavaScript. While the next two options are more complicated these are by far the most flexible, the first of which is addCustomView.

With this function we can build a whole custom view dynamically when either the form loads or when some other event occurs on the form. We can use data from the form or data that we've fetched from the Web API to build our view, which makes this far more flexible than the previous two options.

In the example below we are constructing the various parameters required for the function, including the fetchXML which determines the records that are returned by the view and the layoutXML which determines how those records are displayed, and then calling the addCustomView function passing in our parameters.

This particular example is for the Primary Contact field on the Account form which will show Contacts that have the same post code as the Account we are on.

function addCustomViewToPrimaryContact(context)
{
    var formContext = context.getFormContext();
    var viewId = "{00000000-0000-0000-0000-000000000001}",
            entityName = "contact",
            viewDisplayName = "Filtered Lookup View",
            fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>" +
                "<entity name='contact' >" +
                "<attribute name='fullname' />" +
                "<attribute name='parentcustomerid' />" +
                "<attribute name='address1_postalcode' />" +
                "<attribute name='contactid' />" +
                "<filter>" +
                "<condition attribute='address1_postalcode' operator='eq' value='" + formContext.getAttribute("address1_postalcode").getValue() + "' />" +
                "</filter>" +
                "</entity>" +
                "</fetch>",
            layoutXML = "<grid name='resultset' object='1' jump='contactid' select='1' icon='1' preview='1'>" +
                "<row name='result' id='contactid'>" +
                "<cell name='fullname' width='150' />" +
                "<cell name='parentcustomerid' width='150' />" +
                "<cell name='address1_postalcode' width='50' />" +
                "</row>" +
                "</grid>";

    formContext.getControl("primarycontactid").addCustomView(viewId, entityName, viewDisplayName, fetchXML, layoutXML, true);
}

Dynamically Apply a Custom Filter

This last option allows you to apply additional filters to the lookup, which gets applied whenever a user opens or searches within the lookup. So rather than adding your own custom view you are applying an additional filter to all of the existing views used by the lookup. For the official documentation check out addCustomFilter.

Below is the same example where we are applying a filter that only returns the Contacts that have the same post code as the Account we are on, but instead of building an entirely new view our applyCustomFilter function is instead adding just the filter condition to our lookup control.

In order to apply the custom filter, however, you need to use the addPreSearch function and pass in our applyCustomFilter function, so in order to actually apply our filter we would need to call our applyPreSearch function from the OnLoad form event or from an OnChange event.

function applyCustomFilter (context) {
    var formContext = context.getFormContext();
    var filter = "<filter>" +
             "<condition attribute='address1_postalcode' operator='eq' value='" + formContext.getAttribute("address1_postalcode").getValue() + "' />" +
             "</filter>";
    
    formContext.getControl("primarycontactid").addCustomFilter(filter);   
}

function applyPreSearch (context) {
    var formContext = context.getFormContext();
    formContext.getControl("primarycontactid").addPreSearch(applyCustomFilter);
}

Happy filtering!