Configure lookup field filters on a SharePoint form with JavaScript

You can configure simple filter conditions for lookup fields and lookup controls using out-of-the-box functionality of the editor, selecting the field you want to filter by in the editor, check out our How to filter lookup by another field: Choice, Lookup, or Person article.

However, sometimes you might need a more complex filter which cannot be applied with available settings, and you can write any filter yourself with a little bit of code. In this article, we’ll provide all the necessary examples, which you can adjust to meet your needs, so it will be possible to write a custom filter with minimum effort.

All the filtering queries are based on the OData URI request schema.

Available comparison operators are:

lt - lesser than
le - lesser or equal
gt - greater than
ge - greater or equal
eq - equal
ne - not equal

Filter by one field

Here, you’ll find how to filter search results in lookup field or control by a single column of the source list: Choice, Number, Yes/No, Date, another Lookup, or User or Group.

Filter by text or choice field

You can filter a lookup field or control by a text or choice field with a string, like this:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "SingleLine eq 'Example string'";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "SingleLine eq 'Example string'";
        control.refresh();
    });
});

This value can be retrieved from a field on the form, for example:

Filter lookup field

function filterLookup(value) {
    fd.field('FilteredLookup').filter = "SingleLine eq '" + value + "'";
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when SingleLine field changes
        fd.field('SingleLine').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        filterLookup(fd.field('SingleLine').value);
    });
});

Filter lookup control

function filterLookup(value) {
    fd.control('FilteredLookup').filter = "SingleLine eq '" + value + "'";
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when SingleLine field changes
        fd.field('SingleLine').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        filterLookup(fd.field('SingleLine').value);
    });
});

This can also be a partial string comparison, for example, you can look for values containing a string:

Filter lookup field

function filterLookup(value) {
    fd.field('FilteredLookup').filter = "substringof('" + value + "', SingleLine)";
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when SingleLine field changes
        fd.field('SingleLine').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        filterLookup(fd.field('SingleLine').value);
    });
});

Filter lookup control

function filterLookup(value) {
    fd.control('FilteredLookup').filter = "substringof('" + value + "', SingleLine)";
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when SingleLine field changes
        fd.field('SingleLine').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        filterLookup(fd.field('SingleLine').value);
    });
});

Filter by number or currency field

You can filter a lookup field or control by a number or a currency field with a number, like this:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = 'Number gt 100';
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = 'Number gt 100';
        control.refresh();
    });
});

Filter by Yes/No field

You can filter a lookup field or control by a Yes/No field comparing it to 0 or 1, like this:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = 'YesNo eq 1';
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = 'YesNo eq 1';
        control.refresh();
    });
});

Filter by Date field

You can filter a lookup field or control by a Date field comparing it to date in EDM format, like this:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "Date gt DateTime'2023-10-12T00:00:00'";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "Date gt DateTime'2023-10-12T00:00:00'";
        control.refresh();
    });
});

This value can be retrieved from a field on the form, for example:

Filter lookup field

function filterLookup(value) {
    if (value) {
        let date = value.toISOString();
        fd.field('FilteredLookup').filter = "Date lt DateTime'" + date + "'";
    } else {
        fd.field('FilteredLookup').filter = '';
    }
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when Date field changes
        fd.field('Date').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        filterLookup(fd.field('Date').value);
    });
});

Filter lookup control

function filterLookup(value) {
    if (value) {
        let date = value.toISOString();
        fd.control('FilteredLookup').filter = "Date lt DateTime'" + date + "'";
    } else {
        fd.control('FilteredLookup').filter = '';
    }
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when Date field changes
        fd.field('Date').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        filterLookup(fd.field('Date').value);
    });
});

Filter by single selection lookup field

You can filter a lookup field or control by a lookup field, but in this case you need to specify if you’re filtering by ID or another field:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = 'Lookup/Id eq 1';
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = 'Lookup/Id eq 1';
        control.refresh();
    });
});

This value can be retrieved from another field or control on the form, for example:

Filter lookup field

function filterLookup(value) {
    let lookupId = value && value.LookupId || value || null;
    fd.field('FilteredLookup').filter = 'Lookup/Id eq ' + lookupId;
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when Lookup field changes
        fd.field('Lookup').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Lookup').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter lookup control

function filterLookup(value) {
    let lookupId = value && value.LookupId || value || null;
    fd.control('FilteredLookup').filter = 'Lookup/Id eq ' + lookupId;
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when Lookup field changes
        fd.field('Lookup').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Lookup').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter by multiple selection lookup field

You can filter a lookup field or control by a lookup field that allows selection of multiple options.

Filter lookup field

function filterLookup(value) {
    if (value.length > 0) {
        fd.field('FilteredLookup').filter = value.map(i => 'Lookup/Id eq ' + i.LookupId).join(' or ');
    } else {
        fd.field('FilteredLookup').filter = 'Lookup/Id eq ' + null;
    }
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when Lookup field changes
        fd.field('Lookup').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Lookup').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter lookup control

function filterLookup(value) {
    if (value.length > 0) {
        fd.control('FilteredLookup').filter = value.map(i => 'Lookup/Id eq ' + i.LookupId).join(' or ');
    } else {
        fd.control('FilteredLookup').filter = 'Lookup/Id eq ' + null;
    }
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when Lookup field changes
        fd.field('Lookup').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Lookup').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter by person field

You can filter a lookup field or control by a person field, but in this case you need to specify if you’re filtering by EMail or another field: Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "Person/EMail eq 'jsmith@mycompany.onmicrosoft.com'";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "Person/EMail eq 'jsmith@mycompany.onmicrosoft.com'";
        control.refresh();
    });
});

This value can be retrieved from another field on the form, for example:

Filter lookup field

function filterLookup(value) {
    if (value && value.EntityData && value.EntityData.Email) {
        fd.field('FilteredLookup').filter = "Person/EMail eq'" + value.EntityData.Email + "'";
    } else {
        fd.field('FilteredLookup').filter = '';
    }
    fd.field('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        // filter FilteredLookup when Person field changes
        fd.field('Person').$on('change', value => {
            filterLookup(value);
            field.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Person').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter lookup control

function filterLookup(value) {
    if (value && value.EntityData && value.EntityData.Email) {
        fd.control('FilteredLookup').filter = "Person/EMail eq'" + value.EntityData.Email + "'";
    } else {
        fd.control('FilteredLookup').filter = '';
    }
    fd.control('FilteredLookup').refresh();
}

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        // filter FilteredLookup when Person field changes
        fd.field('Person').$on('change', value => {
            filterLookup(value);
            control.value = null;
        });

        // filter FilteredLookup when form opens
        fd.field('Person').ready(field => {
            filterLookup(field.value);
        });
    });
});

Filter by multiple fields

Here, you’ll find how to filter search results in lookup field or control by multiple columns.

Using and operator

It’s possible to make sure that multiple fields are included in the filter, and all of them match the conditions:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "SingleLine eq 'Example string' and Number gt 100";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "SingleLine eq 'Example string' and Number gt 100";
        control.refresh();
    });
});

Using or operator

It’s possible to make sure that multiple fields are included in the filter, and only one of them has to match the conditions:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "SingleLine eq 'Example string' or Number gt 100";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "SingleLine eq 'Example string' or Number gt 100";
        control.refresh();
    });
});

Using both and and or operators

It’s possible to combine both operators:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = "(Choice eq 'Option A' and Price gt 100) or (Choice eq 'Option B' and Price lt 1000)";
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = "(Choice eq 'Option A' and Price gt 100) or (Choice eq 'Option B' and Price lt 1000)";
        control.refresh();
    });
});

Replace default search

You can also replace default search with custom filter, and filter by multiple fields at once, for example search through Title and Category fields at once:

Filter lookup field

fd.spRendered(() => {
    fd.field('FilteredLookup').ready(field => {
        field.filter = filter => {
            let search = encodeURIComponent(filter);
            return filter
                ? "substringof('" + search + "', Title) or substringof('" + search + "', Category)"
                : '';
        };
        field.useCustomFilterOnly = true;
        field.refresh();
    });
});

Filter lookup control

fd.spRendered(() => {
    fd.control('FilteredLookup').ready(control => {
        control.filter = filter => {
            let search = encodeURIComponent(filter);
            return filter
                ? "substringof('" + search + "', Title) or substringof('" + search + "', Category)"
                : '';
        };
        control.useCustomFilterOnly = true;
        control.refresh();
    });
});