Cascading Dropdown + multiple Field filter

Forms SharePoint Online
1

I have a field (Product) that is a Lookup (using Plumsail CrossSite Lookup for this) that I have as the target of a Cascading Dropdown.

To make the field more usable, I have used code suggested in: https://plumsail.com/docs/forms-sp/how-to/lookup-view.html to combine the Title field (which is the Product Sku) and and Name (name of the product).

The Cascading Dropdown Works great. But what I can not figure out is how to get the filter (as the user types in the field) to work as a Contains on both fields. I tried to use the filter sample located at https://plumsail.com/docs/forms-sp/javascript/fields-sp.html#lookup-lookupmulti which works if I comment out the Cascading filters, but breaks (no filters work) if I try to apply it with the cascading code.

fd.spBeforeRender(function() {
   var template = "";
   template += '<span class="lookup-col">#: data.LookupValue # : #: data.Name #</span>';
    fd.field('Product').widgetOptions = {
        template: template,
        height: 300,
        virtual: {
            itemHeight: 25
        },
        dataSource: {
            pageSize: 16
        }
    };
    
});

fd.spRendered(function() {
    SetProductFields();
});
function filterTypes(category){
    catID = category && category.LookupId || category || null;
    if(catID == null)
    { fd.field('Product_x0020_Type').filter ='';}
    else { fd.field('Product_x0020_Type').filter = 'Product_x0020_Category/Id eq ' + catID; }
    try { fd.field('Product_x0020_Type').widget.dataSource.read();} catch (e) { }
    filterProduct();
}
function filterProduct(){
   var strProdFilter = "";
    if(lobID != null)
    { strProdFilter +=  'Product_x0020_Line/Id eq ' + lobID };
    if(catID != null){ 
      if (strProdFilter.length > 0) {strProdFilter += ' and ';}
      strProdFilter +=  'Product_x0020_Category/Id eq ' + catID;
    }
    if(typeID != null){ 
      if (strProdFilter.length > 0) {strProdFilter += ' and ';}
      strProdFilter +=  'Product_x0020_Type/Id eq ' + typeID;
    }
    
        
    fd.field("Product").filter = strProdFilter;
    
    fd.field('Product').filter = function(filter) {
        console.log(filter);
    var search = encodeURIComponent(filter);
    return filter
        ? "substringof('" + search + "', Title) or substringof('" + search + "', Name)"
        : '';
    }
    //fd.field('Product').useCustomFilterOnly = true;
    try { fd.field('Product').widget.dataSource.read(); } catch (e) {}
}

function SetProductFields(){
    console.log("Set Products Started");
    
    fd.field('Product_x0020_Category').ready().then(function() {
        filterCategories();
     });
    
    fd.field('Product_x0020_Type').ready().then(function() {
        fd.field('Product_x0020_Category').$on('change', function(value){
            filterTypes(value);
            fd.field('Product_x0020_Type').value = null;
            fd.field('Product').value = null;
        });

        //filter Products when form opens
        fd.field('Product_x0020_Category').ready().then(function(field) {
            filterTypes(field.value);
        });
    });
    
    fd.field('Product_x0020_Type').$on('change', function(value){
        typeID = value && value.LookupId || value || null;
            fd.field('Product').value = null;
        filterProduct();
    });
    
    fd.field('Product_x0020_Type').ready().then(function(field) {
        typeId = field.value && field.value.LookupId || field.value || null;
        filterProduct();
    });
    
    fd.field('Product').$on('change', function(value){
        change_Product(value);
    });
    
}
2

Hello @sphilson,

You need to combine filtering by another field and users input like below. This is just a part of the code:

function filterTypes(category){
    catID = category && category.LookupId || category || null;
    if(catID == null)
    { fd.field('Product_x0020_Type').filter ='';}
    else { fd.field('Product_x0020_Type').filter = 'Product_x0020_Category/Id eq ' + catID; }
    try { fd.field('Product_x0020_Type').widget.dataSource.read();} catch (e) { }
    filterProduct();
}

fd.field('Product').filter = function(filter) {
var search = encodeURIComponent(filter);
return filter
//I've added part that filters lookup values by field value
  ? "Product_x0020_Category/Id eq " + catId + " and (substringof('" + search + "', Title) or substringof('" + search + "', Name))"
  : '';
}
//this line is required when using custom filtering
fd.field('Product').useCustomFilterOnly = true;
3

I am really REALLY close here, but still missing something.......

I am working on a similar scenario on a different form, so some of the field names will be different:

I am setting things up in the spRendered

    var PSearch = 'Product_x0020_Line/Id eq 4'; //Only show products from the Line of Business (LOB) of 4
        fd.spRendered(function () {
                $(fd.field("Title").$parent.$el).hide();
                configDisabledFields();
                
                fd.field("Product_x0020_Category").filter = "Line_x0020_of_x0020_Business/Id eq 4";
                fd.field("Product_x0020_Category").$on('change', function (value) { changeCategory(value); });
                fd.field("Component").filter = function (filter) { return filterComponent(filter); }; //capture the typed user filter
                fd.field('Component').useCustomFilterOnly = true;
            });
            function filterComponent(filter)
        {
            var search = encodeURIComponent(filter);
            return filter ? `${PSearch} and (substringof('${search}', Name) or substringof('${search}', Title))` : PSearch;
        }
function changeCategory(cat) {
    fd.field("Component").value = null; //Remove something if already selected
    if (cat != null) {
        PSearch = `Product_x0020_Category/Id eq ${cat.LookupId}`;
    }
    else {
        PSearch = 'Product_x0020_Line/Id eq 4'; //Reset PSearch to LOB
    }
}

This works great when I go to the Component field and start typing...

But when I change the Category, how do I send the PSearch to the Component Field without undoing the capture the typed user filter code?

Like I said I a super close just missing one small thing I am sure.

4

Hello @sphilson,

Could you please clarify this. Do you want to keep the value that a user enters in the search box of the lookup field?

5

I literally was just coming up here to post that I figured it out :).

When setting Product Category, I set the Filter to the Category, refresh the list data, and then set the Filter to use the function call, this now applies the Category and typed in user input: VERY COOL!!!

function applyComponentfilter() {
    fd.field("Component").filter = PSearch; 
    try { fd.field('Component').widget.dataSource.read(); } catch (e) { }
    fd.field("Component").filter = function (filter) { return filterComponent(filter); };
}
function filterComponent(filter) {
    var search = encodeURIComponent(filter);
    return filter ? `${PSearch} and (substringof('${search}', Name) or substringof('${search}', Title))` : PSearch;
}
function changeCategory(cat) {
    fd.field("Component").value = null;
    if (cat != null) {
        PSearch = `Product_x0020_Category/Id eq ${cat.LookupId}`;
        applyComponentfilter();
    }
    else {
        PSearch = 'Product_x0020_Line/Id eq 4';
        applyComponentfilter();
    }
}
1 Like