Cascading Lookups (Filtering)

Description

With a little bit of JavaScript code, you can now easily configure Cascading Lookup fields.

What does that mean? Just to be clear, I am talking about Lookup fields where choosing value in one field, limits available choice in the other field. Lookup filtering can be applied with any data, including values of other fields on the form, not limited to other lookups.

In this example, however, I will show you two Lookup fields on one form - Category and Product, so that the user can only see and choose Products belonging to the selected Category.

Setting up Lists

There is nothing difficult in setting up the Lists. Our first List - Categories, only contains Title column:

Categories List

Second list needs to be connected to the first List, in order for filtering to work correctly. Easiest way is via Lookup. In our case, Products List has a Lookup to Categories List:

Products List

Finally, you need a third List to create form for. It should include 2 Lookup fields: 1 to Categories List and 1 to Products List.

Orders List

Form Configuration

I've created a simple Product Order form:

Product Order form

In order for Lookups to be connected, I only need to change two things. First, I need to select Products Lookup and alter its Extra Fields property:

Extra Fields

It should retrieve ID of the Category Lookup in the Products List, so we can compare it to this form's selected Category:

Category/Id

I will also need to use Expand property to get access to the Category field in the code:

Expand

Here, I just need to add Internal Name of the Lookup field, simple enough:

Category

JavaScript

Last but not least, we use JavaScript in order to apply filtering:

fd.spRendered(function() {
    function filterLookup(v){
        // getting the selected Category (0 if nothing is selected).
        var categoryId = 0;
        if (v) {
        categoryId = isNaN(v) ? v.LookupId : v;
        }

        if (categoryId) {
            // setting filtration
            fd.field('Product').filter = 'Category/Id eq ' + categoryId;
        } else {
            // resetting the filtration
            fd.field('Product').filter = null;
        }

        fd.field('Product').widget.dataSource.read();
    }

    //filter Products when form opens
    filterLookup(fd.field('Category').value);

    //filter Products when Category changes
    fd.field('Category').$on('change', function(value){
        filterLookup(value);
        fd.field('Product').value = null;
    });
});

fd.field('FieldName').filter property is an OData $filter query. You can include all kinds of conditions in this query and combine them with and/or operators.

Read more about OData query here.

Result

By default, all Products are available in the Products field, but if I choose Category - only products of this particular Category are available.

For example, here is Cell Phone Category:

Phones selected

And here I've selected Camera Category:

Cameras selected